Gap and Island Problems

Gap and island problems are a family of SQL challenges that appear constantly in real-world analytics: finding consecutive sequences of events, identifying breaks in a time series, grouping rows that belong together, and detecting when something stopped or restarted.

The classic window-function solution is elegant once you see it — but it requires building the right mental model first.

What is a gap and island problem?

Islands — groups of consecutive rows that belong together (e.g., consecutive days with orders, consecutive order numbers, consecutive active sessions).

Gaps — the breaks between islands (days with no orders, missing sequence numbers, periods of inactivity).

Example: given this daily order data, identify each consecutive streak of days with orders:

Day        | Orders
-----------+--------
2025-09-01 |  3        ← island 1 starts
2025-09-02 |  5
2025-09-03 |  2        ← island 1 ends
           |           ← gap (no orders on 2025-09-04)
2025-09-05 |  1        ← island 2 starts
2025-09-06 |  4        ← island 2 ends
           |           ← gap
2025-09-09 |  2        ← island 3 starts

The classic solution: ROW_NUMBER() subtraction

The key insight: if you number the days sequentially AND number the rows within the result set, the difference between those two numbers is constant within each island and changes at each gap.

WITH order_days AS (
    SELECT DISTINCT created_at::DATE AS day
    FROM orders
    WHERE status = 'completed'
    ORDER BY day
),
numbered AS (
    SELECT
        day,
        ROW_NUMBER() OVER (ORDER BY day) AS rn
    FROM order_days
),
grouped AS (
    SELECT
        day,
        rn,
        day - rn::INT                         AS island_id
    FROM numbered
)
SELECT
    island_id,
    MIN(day)            AS island_start,
    MAX(day)            AS island_end,
    COUNT(*)            AS consecutive_days,
    MAX(day) - MIN(day) + 1 AS span_days
FROM grouped
GROUP BY island_id
ORDER BY island_start;

Why does day - rn stay constant within an island?

• If days are consecutive (no gaps), as day increases by 1, rn also increases by 1 — the difference is constant.
• When a gap occurs, day jumps by more than 1 but rn only increases by 1 — so the difference changes, creating a new island group.