Group each user's login days into streaks using a trick: subtract the row number from the date. Consecutive days give the same result, so that value labels the streak.
Step 1: one row per user per day
If a user logs in three times a day, you must collapse that first, or the row numbers break.
WITH days AS (
SELECT DISTINCT user_id, CAST(login_ts AS DATE) AS d
FROM logins
),
numbered AS (
SELECT user_id, d,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS rn
FROM days
)
SELECT user_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, d - rn -- dialect note below
HAVING COUNT(*) >= 3;Date subtraction syntax differs by engine. In Postgres, d - rn works with an integer. In BigQuery use DATE_SUB(d, INTERVAL rn DAY). In Snowflake use DATEADD(day, -rn, d).
Why it works: a six-row walk-through
d rn d minus rn 2025-03-01 1 2025-02-28 2025-03-02 2 2025-02-28 2025-03-03 3 2025-02-28 <- streak A: three days 2025-03-07 4 2025-03-03 2025-03-08 5 2025-03-03 <- streak B: two days 2025-03-10 6 2025-03-04
While days are consecutive, the date goes up by 1 and the row number goes up by 1, so the difference stays constant. When a day is skipped, the date jumps by more than the row number, and the difference changes. Rows with the same difference belong to the same streak. Streak A has 3 days and passes the filter.
The LAG alternative
Compare each row with the previous one, flag a new streak when the gap is more than one day, and use a running sum of the flags as the streak id. It is easier to explain to someone who has not seen the trick, though it takes two steps instead of one.
Follow-up
"Longest streak per user?" Wrap the result and take MAX(days) per user. "Streak that includes today?" Keep only streaks whose end date is today.