Gaps and islands

"Gaps and islands" describes any problem where you need to find runs of consecutive values: login streaks, periods a server was down, uninterrupted subscription months, or ranges of missing invoice numbers.

The row number difference trick

Suppose user_logins has one row per user per day they logged in. We want each streak with its start, end and length.

WITH numbered AS (
    SELECT user_id, login_day,
           login_day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day))::int AS grp
    FROM user_logins
)
SELECT user_id,
       MIN(login_day) AS streak_start,
       MAX(login_day) AS streak_end,
       COUNT(*)       AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Why it works: within a streak, the date increases by one each row and so does the row number, so their difference stays constant. When a day is skipped, the date jumps by more than the row number, and the difference changes. That constant difference, grp, labels each island.

Work through a tiny example by hand to convince yourself:

login_day   row_number   login_day - row_number
2026-03-01  1            2026-02-28
2026-03-02  2            2026-02-28
2026-03-04  3            2026-03-01
2026-03-05  4            2026-03-01

Two islands, two distinct values of grp.

The LAG flag approach

For data that is not strictly consecutive, such as status changes, compare each row with the previous one and start a new group whenever something changes:

WITH flagged AS (
    SELECT *, CASE WHEN status <> LAG(status) OVER w THEN 1 ELSE 0 END AS changed
    FROM server_status
    WINDOW w AS (PARTITION BY server_id ORDER BY checked_at)
),
grouped AS (
    SELECT *, SUM(changed) OVER (PARTITION BY server_id ORDER BY checked_at) AS grp
    FROM flagged
)
SELECT server_id, status, MIN(checked_at) AS from_ts, MAX(checked_at) AS to_ts
FROM grouped
GROUP BY server_id, status, grp;

A running sum of change flags gives each run its own number. This flag then running sum pattern is the most general tool in this course, and you will see it again in sessionization.