How do you compute each user's longest streak of consecutive daily logins in SQL?
answer
- one row per user per day first
- number the days in date order
- shift the date back by that many days
- a consecutive run lands on one anchor date
- group, count, then take the maximum
basics
~20 sReduce logins to one row per user per day, number those days per user with ROW_NUMBER() ordered by date, subtract that many days from the date, group by user plus that shifted date to get each streak, then take the maximum streak length per user.
solid answer
~50 sFour steps. First deduplicate to one row per user per calendar day — several logins on the same day would otherwise shift the row numbers and shatter the streak. Second, number each user's days with `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)` and subtract that many *days* from the date; inside a consecutive run the shifted date is constant, because both the date and the counter advance by one per row. Third, `GROUP BY user_id, shifted_date` with `MIN(login_date)`, `MAX(login_date)` and `COUNT(*)` to get every streak with its bounds. Fourth, `GROUP BY user_id` over that result taking `MAX(streak_days)` for the longest. Two practical notes: date arithmetic is the least portable part of this query, so compute the row number in an inner query and do the subtraction outside; and "current streak" is just the streak whose `MAX(login_date)` is today.
code
sql · 20 linesWITH login_days AS (
SELECT DISTINCT user_id, CAST(login_ts AS DATE) AS login_date
FROM logins
),
numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM login_days
),
streaks AS (
SELECT user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS streak_days
FROM numbered
GROUP BY user_id, login_date - rn * INTERVAL '1' DAY
)
SELECT user_id, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY user_id;go deeper
Recall the recipe: one row per user per day, number the days, subtract that many days from the date, group, count. Being able to write it slowly and correctly is enough at this level.
Explain why subtracting the row number as days cancels inside a run, and be quick to name the deduplication step — interviewers usually seed the sample data with two logins on one day to see whether you catch it.
Show production judgment: time zones on the timestamp, users with no logins, an explicit tiebreaker when two streaks tie, and a sanity check that streak lengths sum to the distinct day count.
Decide where the streak lives. If it drives product behaviour it should be one agreed definition — materialised or wrapped in a view — rather than recomputed with slightly different edge-case handling in every dashboard.
## The full query ```sql WITH login_days AS ( -- one row per user per calendar day SELECT DISTINCT user_id, CAST(login_ts AS DATE) AS login_date FROM logins ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM login_days ), marked AS ( SELECT user_id, login_date, login_date - rn * INTERVAL '1' DAY AS streak_key FROM numbered ), streaks AS ( SELECT user_id, streak_key, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_days FROM marked GROUP BY user_id, streak_key ) SELECT user_id, MAX(streak_days) AS longest_streak FROM streaks GROUP BY user_id; ``` ## Why the shifted date is constant Inside a run of consecutive days, moving to the next row advances the date by one day and the row number by one. Subtracting one day per row therefore cancels the advance exactly, and every row of the run lands on the same anchor date — one day before the run started, in fact. When a day is skipped the date jumps further than the counter, the anchor moves forward, and a new group begins. The anchor value is not meant to be shown to anyone; it exists so `GROUP BY` has something to hold on to. ## Deduplication is not optional This is the single most common failure in interviews. If a user logs in three times on 2026-03-02, that day contributes three rows, the row numbers run ahead of the calendar, and the anchor date moves *within* what should be one streak — the run shatters into fragments and the reported longest streak is too short. Collapsing to one row per user per day with `SELECT DISTINCT` (or a `GROUP BY user_id, login_date`) fixes it. The equivalent defence is `DENSE_RANK()` instead of `ROW_NUMBER()`, which gives equal dates the same number, but deduplicating first is clearer and makes the `COUNT(*)` per streak mean "days", which is what the question asks for. Related trap: if `login_ts` is a timestamp, you must cast to a date before deduplicating, and you must know which time zone the "day" is defined in. Two logins at 23:50 and 00:10 are the same day in one zone and two days in another, which changes the streak. ## Portability of the date arithmetic The grouping logic is fully portable; the subtraction is not. `login_date - rn * INTERVAL '1' DAY` is the SQL-standard spelling. PostgreSQL also accepts plain integer subtraction from a `date` (`login_date - CAST(rn AS INT)`), which returns a `date`. MySQL spells it `DATE_SUB(login_date, INTERVAL rn DAY)`. Engines differ here, so check your engine's documentation rather than assuming — and always compute the row number in an inner query first, so the date function receives a plain integer column instead of a window expression, which sidesteps most dialect quirks. ## Variations interviewers ask next **Current streak.** Filter the `streaks` result to the row whose `streak_end` is today: `WHERE streak_end = CURRENT_DATE`. Whether "today" or "yesterday" should count as still alive is a product decision worth asking about — many apps keep the streak alive until the end of the following day. **Streak as of any date.** The same query with the source filtered to `login_date <= :as_of`, because the row numbering happens over whatever rows survive `WHERE`. **Ties.** `MAX(streak_days)` returns the length but not which streak it was. If the dates are wanted too, rank the streaks with `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY streak_days DESC, streak_start)` and keep the first, adding an explicit tiebreaker so the result is deterministic. **Business days only.** If weekends are not supposed to break a streak, calendar arithmetic alone will not do it; you need a calendar table that numbers only working days, and you subtract the row number from that working-day number instead of from the date. Say this out loud rather than pretending `INTERVAL '1' DAY` handles it. **Users with no logins.** They simply do not appear. If the report must list every user with zero, left-join the result back onto the users table and coalesce the count to 0. ## Sanity checks Before trusting the output, check that the sum of all streak lengths per user equals that user's distinct login-day count — every day must belong to exactly one streak. A shortfall means duplicates or a lost partition key; a surplus means the deduplication did not run.
- What breaks if a user logs in five times on the same day?The row numbers advance five times while the calendar advances once, so the shifted date moves inside what should be a single streak and the run splits into fragments — the reported streak comes out shorter than reality. Deduplicate to one row per user per day first, or use `DENSE_RANK()` so equal dates share a number.
- How would you return the dates of the longest streak, not just its length?Rank the per-streak rows instead of aggregating: `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY streak_days DESC, streak_start)` over the streaks result, then keep rank 1. The explicit second sort key matters — without a tiebreaker, two equally long streaks make the choice arbitrary and the query non-deterministic across runs.
- The product says weekends must not break a streak. Does the query still work?No — calendar subtraction treats Saturday and Sunday as ordinary missing days. You need a calendar table that assigns a sequential working-day number to each business day, then subtract the row number from that working-day number instead of from the date. The grouping logic is unchanged; only the definition of 'next' moves.
saying these in an interview costs you the question
- Forgets to deduplicate multiple logins on the same day
- Leaves out PARTITION BY user_id so all users share numbering
- Groups streaks without user_id in the GROUP BY
- Assumes one dialect's date arithmetic is portable
- Casts timestamps to dates without considering the time zone