How do I count consecutive-day streaks and the gaps between them?

SQL

SELECT
  member,
  COUNT(*) : : INTEGER AS sessions,
  COUNT(DISTINCT session_date) : : INTEGER AS active_days
FROM input
GROUP BY member
ORDER BY active_days DESC, member

Rows

SQL

WITH d AS (
  SELECT DISTINCT member, session_date
  FROM input
)
SELECT
  member,
  session_date,
  (date_diff('day', LAG(session_date) OVER (PARTITION BY member ORDER BY session_date), session_date) - 1) : : INTEGER AS days_missed_before
FROM d
ORDER BY member, session_date

Rows

SQL

WITH d AS (
  SELECT DISTINCT member, session_date
  FROM input
), g AS (
  SELECT
    member,
    session_date,
    date_diff('day', LAG(session_date) OVER (PARTITION BY member ORDER BY session_date), session_date)
      - 1 AS days_missed_before
  FROM d
), s AS (
  SELECT
    *,
    SUM(CASE WHEN days_missed_before <= 0 THEN 0 ELSE 1 END) OVER (PARTITION BY member ORDER BY session_date ROWS UNBOUNDED PRECEDING) AS streak_no
  FROM g
)
SELECT
  member,
  session_date,
  days_missed_before : : INTEGER AS days_missed_before,
  streak_no : : INTEGER AS streak_no,
  ROW_NUMBER() OVER (PARTITION BY member, streak_no ORDER BY session_date) : : INTEGER AS day_of_streak
FROM s
ORDER BY member, session_date

Rows

SQL

WITH d AS (
  SELECT DISTINCT member, session_date
  FROM input
), g AS (
  SELECT
    member,
    session_date,
    date_diff('day', LAG(session_date) OVER (PARTITION BY member ORDER BY session_date), session_date)
      - 1 AS days_missed_before
  FROM d
), s AS (
  SELECT
    *,
    SUM(CASE WHEN days_missed_before <= 0 THEN 0 ELSE 1 END) OVER (PARTITION BY member ORDER BY session_date ROWS UNBOUNDED PRECEDING) AS streak_no
  FROM g
)
SELECT
  member,
  MIN(session_date) AS first_day,
  MAX(session_date) AS last_day,
  COUNT(*) : : INTEGER AS streak_days
FROM s
GROUP BY member, streak_no
ORDER BY streak_days DESC, member, first_day

Rows

SQL

WITH d AS (
  SELECT DISTINCT member, session_date
  FROM input
), g AS (
  SELECT
    member,
    session_date,
    date_diff('day', LAG(session_date) OVER (PARTITION BY member ORDER BY session_date), session_date)
      - 1 AS days_missed_before
  FROM d
), s AS (
  SELECT
    *,
    SUM(CASE WHEN days_missed_before <= 0 THEN 0 ELSE 1 END) OVER (PARTITION BY member ORDER BY session_date ROWS UNBOUNDED PRECEDING) AS streak_no
  FROM g
), k AS (
  SELECT
    member,
    MAX(session_date) AS last_day,
    COUNT(*) AS streak_days
  FROM s
  GROUP BY member, streak_no
)
SELECT
  member,
  SUM(streak_days) : : INTEGER AS active_days,
  MAX(streak_days) : : INTEGER AS longest_streak,
  COALESCE(MAX(streak_days) FILTER (WHERE last_day >= (
    SELECT MAX(session_date)
    FROM input
  ) - 0), 0) : : INTEGER AS current_streak
FROM k
GROUP BY member
ORDER BY longest_streak DESC, member

Rows