What is the gaps-and-islands problem, and how do you solve it with window functions?

How to think about it

This is a classic hard SQL problem that hides inside real work — sessionization, streak counting, finding outage windows, detecting login gaps. The trick is elegant once it clicks, but it takes a moment to see why it works, and that “why” is exactly what the interviewer is checking.

The problem

Given a table of dates with some missing, group consecutive present dates into contiguous ranges (islands) and identify the breaks (gaps) between them. For dates Jan 1, 2, 3, 5, 6, 9, you want three islands: Jan 1–3, Jan 5–6, and a lone Jan 9.

Why the row-number trick works

Number the dates 1, 2, 3, 4, 5, 6 in order, then subtract that row number from the date. Along a run of consecutive dates, both values increase by 1 each step — so their difference stays frozen. The instant a gap appears, the date jumps by more than 1 while the row number still climbs by 1, and the difference shifts. Same difference = same island.

Date sequence:  Jan 1  Jan 2  Jan 3  Jan 5  Jan 6  Jan 9
Row number:       1      2      3      4      5      6
Date − rn:        0      0      0      1      1      3   ← group key

The three distinct group keys (0, 0, 0 / 1, 1 / 3) are precisely the three islands.

A worked example

In SQLite the date has to become a number first — julianday() does that — so the real group key is a large constant rather than 0/1/3, but the pattern is identical: it holds steady within an island and jumps at every gap.

WITH numbered AS (
  SELECT active_date,
         ROW_NUMBER() OVER (ORDER BY active_date) AS rn
  FROM user_activity            -- rows: Jan 1,2,3,5,6,9
),
islands AS (
  SELECT active_date,
         julianday(active_date) - rn AS grp   -- the constant-per-island key
  FROM numbered
)
SELECT MIN(active_date) AS island_start,
       MAX(active_date) AS island_end,
       COUNT(*)         AS days_in_island
FROM islands
GROUP BY grp
ORDER BY island_start;

island_start	island_end	days_in_island
2026-01-01	2026-01-03	3
2026-01-05	2026-01-06	2
2026-01-09	2026-01-09	1

Three islands, exactly as predicted — a 3-day run, a 2-day run, and a single day. Add 2026-01-10 to the table and the last island stretches to two days; insert a gap and a fourth island appears. The GROUP BY grp does all the work, because rows in the same island already share a key.

Variant: status-based islands

When the runs are state changes rather than dates — consecutive rows where status stays the same — use LAG to mark each boundary, then a running SUM to assign group IDs:

WITH flagged AS (
  SELECT user_id, event_time, status,
         CASE
           WHEN status <> LAG(status) OVER (PARTITION BY user_id ORDER BY event_time)
             OR LAG(status) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
           THEN 1 ELSE 0
         END AS is_new_group
  FROM events
),
grouped AS (
  SELECT *,
         SUM(is_new_group) OVER (
           PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING
         ) AS grp
  FROM flagged
)
SELECT user_id, status, MIN(event_time) AS period_start, MAX(event_time) AS period_end
FROM grouped
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

SUM(is_new_group) is a running counter that ticks up by one each time a new island begins.