SQL Interview Prep · Lesson

Detecting Consecutive Calendar Days

Using date arithmetic and row numbers to find unbroken day runs.

The Interview Setup

Interviewers love streak questions because they reveal whether you truly understand window functions and date arithmetic. A typical prompt: "Given a table of user login dates, find each unbroken run of consecutive calendar days."

The naive instinct is a self-join comparing every row to the next, but that explodes on large tables and is awkward to express. The professional answer uses the gaps-and-islands technique. In this lesson you will learn to detect consecutive days cleanly with row numbers and date subtraction.

The Sample Data

Throughout this lesson we use a logins table with one row per user per day they were active. Duplicates are assumed already removed (one login per calendar day).

user_id — who logged in
login_date — a DATE value

For user 1 the dates are Jan 1, 2, 3, then a gap, then Jan 6, 7. We expect two runs: a 3-day run and a 2-day run.

SELECT * FROM logins ORDER BY user_id, login_date;
-- user_id | login_date
--    1    | 2024-01-01
--    1    | 2024-01-02
--    1    | 2024-01-03
--    1    | 2024-01-06
--    1    | 2024-01-07

All lessons in this course

← Back to SQL Interview Prep