SQL Interview Prep · Lesson

Detecting Consecutive Calendar Days

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

Detecting Consecutive Calendar Days is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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

The Core Insight

Here is the trick that unlocks every consecutive-days problem. If you order the rows by date and assign each a sequential row number, then for any run of consecutive days the difference between the date and the row number stays constant.

Why? Both the date and the row number increase by exactly 1 on each consecutive day, so their difference does not change. When a gap appears, the date jumps but the row number does not — breaking the constant and starting a new group.

Seeing the Difference

Let us walk it by hand for user 1. ROW_NUMBER counts 1, 2, 3, 4, 5. Subtract the row number (as days) from the date and watch the result.

Jan 1 − 1 = Dec 31
Jan 2 − 2 = Dec 31
Jan 3 − 3 = Dec 31
Jan 6 − 4 = Jan 2
Jan 7 − 5 = Jan 2

The first three share Dec 31; the last two share Jan 2. That shared anchor value is our group key.

Adding ROW_NUMBER

The first concrete step is to attach a row number, partitioned per user so streaks never cross user boundaries, ordered by the date.

PARTITION BY user_id restarts the counter for each user; ORDER BY login_date guarantees the sequence follows the calendar.

SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS rn
FROM logins;

Computing the Group Anchor

Now subtract rn days from login_date. In PostgreSQL you can subtract an integer day count from a date directly. The result is the constant anchor that identifies each island.

Notice we cannot reference the alias rn in the same SELECT that defines it — so we wrap the previous query in a CTE or subquery first.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  login_date,
  login_date - rn AS grp
FROM numbered;

Grouping the Islands

With the anchor in hand, every consecutive run shares the same grp value. Group by user_id and grp, then aggregate to get the start, end, and length of each run.

MIN(login_date) — first day of the streak
MAX(login_date) — last day of the streak
COUNT(*) — number of days in the streak

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

Dialect Differences

Date arithmetic syntax varies. Mention this in interviews to show breadth.

PostgreSQL: login_date - rn (date minus integer days)
MySQL: DATE_SUB(login_date, INTERVAL rn DAY)
SQL Server: DATEADD(day, -rn, login_date)

The logic is identical; only the function names change. The portable mental model is "shift each date backward by its position so a clean run collapses to one constant."

-- SQL Server version of the anchor
DATEADD(day, -1 * rn, login_date) AS grp

Why Not a Self-Join?

An interviewer may ask why you avoided a self-join such as l1.login_date = l2.login_date + 1. Reasons to give:

A self-join only tests adjacency, not the full run — assembling complete streaks still needs grouping.
It can fan out and is O(n²) without good indexes.
The row-number method is a single ordered pass, far more scalable.

Window functions are the modern, expected answer for these problems.

Guarding Against Duplicates

The whole technique assumes one row per user per day. If the source has multiple logins per day, two rows on the same date get different row numbers, which corrupts the anchor.

Defend by deduplicating first — cast timestamps to dates and take DISTINCT, or use DENSE_RANK on the date instead of ROW_NUMBER so equal dates share a number.

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
)
SELECT * FROM days;

The Full Solution

Putting every piece together gives a clean, interview-ready answer that lists each consecutive-day run with its start, end, and length.

This same skeleton — dedupe, number, subtract, group — solves nearly any "consecutive" question you will be handed.

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
),
numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM days
)
SELECT user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

Quick Check

Test your grasp of the core trick.

Recap

You learned the foundational consecutive-days pattern:

Dedupe to one row per user per day.
ROW_NUMBER ordered by date, partitioned by user.
Subtract the row number from the date to get a constant anchor per run.
GROUP BY the anchor and aggregate for start, end, and length.

This gaps-and-islands skeleton scales in a single pass and beats self-joins. Next you will use it to compute the longest streak per user.

Frequently asked questions

Is the “Detecting Consecutive Calendar Days” lesson free?

Yes — the full text of “Detecting Consecutive Calendar Days” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Detecting Consecutive Calendar Days”?

Using date arithmetic and row numbers to find unbroken day runs. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Detecting Consecutive Calendar Days” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep