Longest streak of consecutive active days per user
Table activity(user_id, activity_date) has at most one row per user-day. For each user, return the length of their longest run of consecutive calendar days with activity (a single active day counts as a streak of 1).
-- per user: length of the longest run of consecutive active daysWrite the query.
Gaps-and-islands: per user, subtract a date-ordered ROW_NUMBER() from the date — consecutive days share one offset, a constant island key. Group by user and that key, COUNT(*) the days per run, then take the MAX run length per user.
- ✗Counting all day-after-day rows instead of the longest single run
- ✗Using the first-to-last span as if activity were continuous
- ✗Treating total distinct active days as the longest streak
- →Why does
activity_date - ROW_NUMBER()stay constant within a run? - →How would you also return the start and end date of that streak?
The gaps-and-islands trick: within a run of consecutive dates, the date increases by one each row and so does an ordered ROW_NUMBER(), so date - row_number is constant — a stable id for that run. Each break in the dates shifts the offset, starting a new island:
WITH daily AS (
SELECT DISTINCT user_id, activity_date FROM activity
),
grouped AS (
SELECT user_id,
activity_date,
activity_date
- (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date))::int AS grp
FROM daily
),
runs AS (
SELECT user_id, grp, COUNT(*) AS streak
FROM grouped
GROUP BY user_id, grp
)
SELECT user_id, MAX(streak) AS longest_streak
FROM runs
GROUP BY user_id;(ROW_NUMBER() ...)::int subtracted from a date yields a date (date minus integer), and consecutive days collapse to one grp value. COUNT(*) per grp is that run's length; MAX per user is the longest. The DISTINCT guards against duplicate rows per day, which would otherwise break the one-row-per-day assumption the offset relies on.
