Gaps, Islands and Streaks

"Longest login streak", "consecutive days with sales", "missing invoice numbers", "find overlapping bookings" — these all look like different questions and are all one pattern. It is the hardest thing on a SQL sheet and it rests on a single trick.

The trick

For a consecutive run, the value minus its row number is constant.

Consecutive values increase in lockstep with the row number, so the difference stays put. A gap makes the value jump ahead of the counter, and the difference changes. That difference is therefore a group key — and once you have a group key, it is an ordinary `GROUP BY`.

WITH numbered AS (
    SELECT day, day - ROW_NUMBER() OVER (ORDER BY day) AS island
    FROM activity
)
SELECT MIN(day) AS run_start, MAX(day) AS run_end, COUNT(*) AS run_length
FROM numbered
GROUP BY island
ORDER BY run_start;

With dates

Subtracting a row number from a date needs the number converted to an interval — or, simpler, subtract the row number as days:

WITH numbered AS (
    SELECT
        user_id,
        login_date,
        login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int
            AS streak_group
    FROM (SELECT DISTINCT user_id, login_date FROM logins) d
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_days
FROM numbered
GROUP BY user_id, streak_group
ORDER BY streak_days DESC;

Two details that decide correctness:

• `SELECT DISTINCT` first. Two logins on the same day would each get a row number, and

the run would break where it should not. Deduplicate to one row per user per day.

• `PARTITION BY user_id` so each user's streak is numbered independently.

The longest streak per user is then MAX(streak_days) over that result — or wrap it in one more ranking step.

Finding gaps with LAG

The complementary question — where are the breaks — is `LAG`:

WITH with_prev AS (
    SELECT invoice_no,
           LAG(invoice_no) OVER (ORDER BY invoice_no) AS prev_no
    FROM invoices
)
SELECT prev_no + 1 AS gap_start, invoice_no - 1 AS gap_end
FROM with_prev
WHERE invoice_no - prev_no > 1;

The first row has prev_no NULL, so the arithmetic is NULL and the row is filtered out — which is the desired behaviour, since there is no gap before the beginning.

Missing dates need a calendar

There is an important limit here: **a query over the data can only see days the data contains.** If a date is missing entirely, no window function can conjure it. You need a generated series and a `LEFT JOIN`:

WITH RECURSIVE calendar AS (
    SELECT DATE '2024-01-01' AS day
    UNION ALL
    SELECT day + 1 FROM calendar WHERE day < DATE '2024-12-31'
)
SELECT c.day
FROM calendar c
LEFT JOIN orders o ON o.order_date = c.day
WHERE o.id IS NULL;                        -- days with no orders at all

The same calendar join is what makes a daily revenue report show zeroes instead of skipping days. See CTEs.

Consecutive rows by condition

A variant worth knowing: runs defined by a condition rather than a sequence — "three or more consecutive days above target", "the same status repeated".

-- Group changes each time the status differs from the previous row
WITH marked AS (
    SELECT day, status,
           CASE WHEN status = LAG(status) OVER (ORDER BY day) THEN 0 ELSE 1 END AS is_new
    FROM daily_status
),
grouped AS (
    SELECT day, status, SUM(is_new) OVER (ORDER BY day) AS run_id
    FROM marked
)
SELECT status, MIN(day) AS start_day, COUNT(*) AS run_length
FROM grouped
GROUP BY status, run_id
HAVING COUNT(*) >= 3;

The idiom is worth naming: **a running SUM over a 0/1 "did it change" flag produces a group id.** It generalises to sessionisation — mark a new session when the gap since the previous event exceeds 30 minutes, then running-sum the flag.

Overlapping ranges

-- Two ranges overlap when each starts before the other ends
SELECT a.id, b.id
FROM bookings a
JOIN bookings b
  ON a.room_id = b.room_id
 AND a.id < b.id                    -- each pair once
 AND a.start_time < b.end_time
 AND b.start_time < a.end_time;

The condition a.start < b.end AND b.start < a.end is the whole overlap test, and it is worth memorising — writing it out case by case takes four branches and usually gets one wrong.

Mistakes that cost the interview

• Not deduplicating before numbering, so repeats break a run.
• Missing `PARTITION BY`, computing one global streak instead of one per user.
• Expecting missing dates to appear without a calendar table.
• `ROW_NUMBER` vs `RANK`. The trick needs consecutive numbering, so ties under RANK

break it — deduplicate instead.

• Off-by-one in gap boundaries. The gap runs from prev + 1 to current - 1.

Practise these on the SQL sheet. Read window functions first if LAG and ROW_NUMBER are new.