Gaps and Islands, and Comparing Adjacent Rows

Concept

Gaps are missing values in a sequence. Islands are runs of consecutive values. The pattern appears constantly once you recognise it: consecutive login days, uninterrupted attendance, unbroken order streaks, session boundaries in event logs.

Finding gaps

-- Where does the id sequence jump?
SELECT id AS gap_starts_after,
       LEAD(id) OVER (ORDER BY id) AS next_id,
       LEAD(id) OVER (ORDER BY id) - id - 1 AS missing_count
FROM   orders
QUALIFY missing_count > 0;    -- Snowflake / DuckDB only

-- Portable version
WITH seq AS (
    SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
    FROM   orders
)
SELECT id AS gap_after, next_id AS gap_before, next_id - id - 1 AS missing
FROM   seq
WHERE  next_id - id > 1;

-- Missing dates: generate the full range, then anti join
WITH RECURSIVE days AS (
    SELECT DATE('2024-01-01') AS d
    UNION ALL
    SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2024-12-31'
)
SELECT d AS day_with_no_orders
FROM   days
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_date = days.d)
ORDER BY d;

Finding islands

The standard trick: subtract a row number from the value. Within a consecutive run the difference is constant, so it becomes a group key.

-- Consecutive order dates per customer, grouped into runs
WITH numbered AS (
    SELECT customer_id,
           order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
    FROM   (SELECT DISTINCT customer_id, order_date FROM orders) d
),
grouped AS (
    SELECT customer_id,
           order_date,
           DATE_SUB(order_date, INTERVAL rn DAY) AS island_key
    FROM   numbered
)
SELECT customer_id,
       MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*)        AS streak_length
FROM   grouped
GROUP BY customer_id, island_key
HAVING COUNT(*) > 1
ORDER BY customer_id, streak_start;

Explanation

Work the arithmetic through on three consecutive dates:

The first three share an island_key, so they group together as one streak. The gap on the 7th produces a new key and starts a new island. Grouping by the key gives the runs, their start, end and length.

Comparing adjacent rows

-- Detect a change of value, then number the resulting blocks
WITH marked AS (
    SELECT customer_id, order_date, status,
           CASE WHEN status = LAG(status) OVER (PARTITION BY customer_id ORDER BY order_date)
                THEN 0 ELSE 1 END AS is_new_block
    FROM   orders
),
blocked AS (
    SELECT customer_id, order_date, status,
           SUM(is_new_block) OVER (PARTITION BY customer_id ORDER BY order_date
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS block_id
    FROM   marked
)
SELECT customer_id, status, MIN(order_date) AS from_date, MAX(order_date) AS to_date, COUNT(*) AS rows_in_block
FROM   blocked
GROUP BY customer_id, status, block_id
ORDER BY customer_id, from_date;

Flag each change with 1, then take a running SUM of the flags. The running total is constant within a block and increments at each boundary - the same idea as the island key, expressed for value changes rather than sequence gaps.

Sessionisation

-- A new session starts when more than 30 minutes have passed since the last event
WITH gaps AS (
    SELECT user_id, event_time,
           CASE WHEN TIMESTAMPDIFF(MINUTE,
                        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
                        event_time) > 30
                  OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
                THEN 1 ELSE 0 END AS new_session
    FROM   events
)
SELECT user_id, event_time,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM   gaps;

Important rules

• The island trick needs a dense row number over the distinct values, so de duplicate first.
• For date islands, subtract row number as an interval; for integer islands, subtract it directly.
• The first row of each partition has no LAG, so it must be treated as the start of a block explicitly.
• Gap detection over a date range needs a generated calendar - a recursive CTE or a numbers table.
• QUALIFY filters window results without a subquery, but exists only in Snowflake, DuckDB, BigQuery and Teradata.

Common mistakes

• Forgetting DISTINCT, so duplicate dates break the row numbering and split islands.
• Missing the IS NULL case for the first row and losing the first session or block.
• Assuming missing values can be found without generating the complete expected sequence.
• Using RANK instead of ROW_NUMBER - ties leave holes and the arithmetic stops working.

Best practices

• Name the CTEs after the steps: numbered, grouped, marked, blocked.
• Keep a permanent calendar table if you do date gap analysis regularly - it is faster and simpler than generating one each time.
• Test on a case with a single row, and on one with all rows consecutive.
• Always use ROW_NUMBER for the island key.

Practice

1. Find every day in 2024 with no orders.
2. Find each customer's longest streak of consecutive order days.
3. Group each customer's orders into blocks of unchanging status, showing the date range of each block.