Dates and streaks

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

A fitness app gives a badge for logging in 3 days in a row. Product asks you for everyone who earned it. There is no "streak" column anywhere. Product companies love this question.

You'll learn to

• Find the first and last event per user
• Solve consecutive-days and gaps-and-islands problems with ROW_NUMBER
• Fill missing dates with generate_series
• Compute month-over-month growth, retention and rolling sums

Learn

The gaps-and-islands trick

Take one user's distinct login dates in order and number them 1, 2, 3, … with ROW_NUMBER. Now subtract the number from the date.

• On consecutive days, the date goes up by 1 and so does the number, so date − number stays the same.
• After a gap, the date jumps more than the number, so the difference changes.

That constant is a group key: every run of consecutive days (an island) shares one value. GROUP BY it to get each streak's start, end and length.

See the islands appear

WITH days AS (
  SELECT DISTINCT user_id, login_date
  FROM logins
)
SELECT user_id, login_date,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
       login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::INT AS island
FROM days
ORDER BY user_id, login_date;

User 1 logs in on 1, 2, 3, 5 and 6 July. The island column is 30 June for the first three days and 1 July for the next two: two streaks. DATE − INT gives a date, so cast ROW_NUMBER (a bigint) to INT.

Days with no rows: generate_series

SELECT d::DATE AS day, COALESCE(s.amount, 0) AS amount
FROM generate_series(DATE '2025-07-01', DATE '2025-07-10', INTERVAL '1 day') AS d
LEFT JOIN daily_sales AS s ON s.sale_date = d::DATE
ORDER BY day;

GROUP BY can only show days that have rows. To show the zeros too, generate every day and LEFT JOIN the data to it.

Two logins on the same day break the trick: ROW_NUMBER goes up by 2 while the date goes up by 1, which splits one streak in two. Always take DISTINCT dates first. Interviewers add a duplicate on purpose.

MySQL has no date − integer: use DATE_SUB(login_date, INTERVAL rn DAY), and DATEDIFF(a, b) for days between. There is no generate_series either; use a recursive CTE to make the dates.

In one line

Streaks: DISTINCT days, then date − ROW_NUMBER() groups each island; missing days come from generate_series; growth = LAG over per-month rows.

Interview corner

How do you find users who logged in on 3 consecutive days?

Take distinct (user, date) pairs, compute login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), which is constant within a run of consecutive days, then GROUP BY user_id, that_key HAVING COUNT(*) >= 3. An alternative is LAG/LEAD: a day whose previous day is date − 1 and whose day before that is date − 2.

How do you show days with zero sales in a daily report?

Generate the full calendar with generate_series(start, end, INTERVAL '1 day') (or a calendar table, or a recursive CTE in MySQL), LEFT JOIN the sales to it, and use COALESCE(amount, 0).

How would you compute month-over-month growth?

Aggregate to one row per month in a CTE, then 100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month). Mention that months with no data are missing unless you generate them, and guard against dividing by zero with NULLIF.