Identify Gaps and Islands in SQL (The Classic Technique)

What "gaps" and "islands" mean

Given a sequence of numbers or dates that's supposed to be continuous but isn't, there are two questions you might ask: "where are the breaks?" (gaps) and "what are the unbroken runs between the breaks?" (islands). Take this sequence of order IDs:

The islands are {101-103}, {107-108}, and {110}. The gaps are 104-106 and 109. Same data, two different questions, two different queries.

Islands: grouping consecutive runs

The key insight: in a perfectly consecutive run, a value and its position in that run (its row number) both increase by exactly 1 each step — so their difference never changes. The moment there's a break, the value jumps ahead but the row number doesn't, so the difference shifts to a new constant.

WITH numbered AS (
  SELECT
    order_id,
    ROW_NUMBER() OVER (ORDER BY order_id) AS rn
  FROM orders
)
SELECT
  order_id,
  rn,
  order_id - rn AS island_group
FROM numbered
ORDER BY order_id;

Every row sharing the same island_group value belongs to the same consecutive run. Wrap it in GROUP BY to collapse each island into a single summary row:

SELECT
  MIN(order_id) AS island_start,
  MAX(order_id) AS island_end,
  COUNT(*) AS island_size
FROM (
  SELECT order_id, order_id - ROW_NUMBER() OVER (ORDER BY order_id) AS island_group
  FROM orders
) t
GROUP BY island_group
ORDER BY island_start;

Islands on dates: consecutive login streaks

The exact same idea works on calendar dates — subtract a day-scaled row number from the date instead of an integer. This is the standard way to compute "longest login streak" or "consecutive active days":

WITH daily_logins AS (
  SELECT DISTINCT user_id, login_date
  FROM logins
),
numbered AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day') AS streak_group
  FROM daily_logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM numbered
GROUP BY user_id, streak_group
ORDER BY user_id, streak_start;

DISTINCT on (user_id, login_date) matters here — multiple logins on the same day must collapse to one row, or the row numbers would drift ahead of the actual day count and break the trick entirely.

Gaps: finding missing values

For gaps, LAG() is more direct than the row-number trick: compare each value to the one before it, and flag anywhere the jump is bigger than expected.

WITH ordered AS (
  SELECT
    order_id,
    LAG(order_id) OVER (ORDER BY order_id) AS prev_order_id
  FROM orders
)
SELECT
  prev_order_id + 1 AS gap_start,
  order_id - 1      AS gap_end
FROM ordered
WHERE order_id - prev_order_id > 1;

Exactly the two missing ranges from the original sequence. For dates, swap the + 1 / - 1 integer arithmetic for + INTERVAL '1 day' and compare against a day difference instead of 1.

Alternative: LAG-based island detection

If ROW_NUMBER subtraction feels less intuitive, the same flag-and-cumulative-sum pattern from sessionization works here too — flag a row whenever it breaks the sequence, then run a cumulative sum of the flag:

WITH flagged AS (
  SELECT
    order_id,
    CASE WHEN order_id - LAG(order_id) OVER (ORDER BY order_id) = 1
         THEN 0 ELSE 1 END AS is_new_island
  FROM orders
)
SELECT
  order_id,
  SUM(is_new_island) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island_number
FROM flagged;

Both approaches produce identical groupings — the row-number trick is usually less code, but the LAG version reads more explicitly as "start a new group when the sequence breaks," which some people find easier to reason about at first.

Real-world use cases

Common mistakes

• Not deduplicating before applying ROW_NUMBER on dates. Multiple events on the same day inflate the row count and desynchronize it from the date, breaking the subtraction trick.
• Forgetting PARTITION BY when islands are needed per group (per user, per device) instead of across the whole table.
• Off-by-one errors in gap boundaries. The gap is between two present values — prev + 1 to current - 1, not prev to current.
• Mixing data types in date arithmetic — subtracting a plain integer row number from a DATE column without casting it to an interval first, which several databases reject or misinterpret.