The SQL Pattern That Reveals Hidden Structure in Sequential Data
Sequential data is everywhere. login sessions, machine sensor readings, subscription periods, trading windows. Storing sequences in a relational table feels straightforward at first: each row has a value and a date or an ID, and the data sits neatly in order. The hard part comes when you need to answer questions like: Which periods of activity were uninterrupted? Where did sessions break? How long was the longest continuous streak?
These are gaps and islands problems. Solving them cleanly in SQL requires thinking in sets, not loops.
What Are Gaps and Islands?
The terminology comes from the visual pattern you get when sequential data is laid out in order. “Islands” are contiguous blocks that belong together consecutive dates with activity, consecutive IDs without missing values, overlapping time intervals. “Gaps” are the breaks between those islands: missing dates, skipped IDs, inactive periods.
Consider a table of daily website activity records. A user might be active on January 1, 2, and 3, then inactive on January 4 and 5, then active again on January 6 and 7. The islands are [Jan 1–3] and [Jan 6–7]; the gap is [Jan 4–5]. The business questions that follow are natural: “What was each user’s longest unbroken streak?” or “How many distinct activity periods did each user have this month?”
The naive approach looping row by row, comparing the current date to the previous one works procedurally but abandons the power of the relational model. SQL’s real strength here is in assigning group identifiers to entire islands at once, using arithmetic on row numbers.
Subtraction Eliminates the Offset
If you subtract a row’s sequential rank from the actual date (or ID value), equal differences mean consecutive rows belong to the same island. That’s the whole trick, and it’s worth sitting with for a moment before moving to code.
Imagine dates: Jan 1, Jan 2, Jan 3 (gap), Jan 6, Jan 7. Assign row numbers with ROW_NUMBER():
date | row_num | date - row_num
Jan 1 | 1 | Dec 31
Jan 2 | 2 | Dec 31
Jan 3 | 3 | Dec 31
Jan 6 | 4 | Jan 2
Jan 7 | 5 | Jan 2
The subtracted value stays constant within each island and changes at every gap. That constant, the “island key” is what you group on.
Here’s the full query, using a user_activity table with columns user_id and activity_date:
WITH ranked AS (
SELECT
user_id,
activity_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY activity_date
) AS rn
FROM user_activity
),
island_keys AS (
SELECT
user_id,
activity_date,
activity_date - rn * INTERVAL '1 day' AS island_key
FROM ranked
)
SELECT
user_id,
island_key,
MIN(activity_date) AS island_start,
MAX(activity_date) AS island_end,
COUNT(*) AS streak_length
FROM island_keys
GROUP BY user_id, island_key
ORDER BY user_id, island_start;
Expected output:
user_id | island_key | island_start | island_end | streak_length
42 | 2024-12-31 | 2025-01-01 | 2025-01-03 | 3
42 | 2025-01-02 | 2025-01-06 | 2025-01-07 | 2
The island_key column itself has no meaningful business interpretation, it’s an artifact of the subtraction. What matters is that rows sharing the same key belong to the same island, and grouping on it gives you each island’s start, end, and length in a single pass.
Handling Non-Date Sequences
The same pattern works when your sequence is integer-based rather than date-based. A manufacturing line records inspection readings for consecutive part IDs. Quality engineers need to know which stretches of part IDs passed inspection, and where the transitions happen.
Given a table inspections(part_id INT, result VARCHAR), filter to passing parts and apply the islands logic:
WITH passing AS (
SELECT
part_id,
ROW_NUMBER() OVER (ORDER BY part_id) AS rn
FROM inspections
WHERE result = 'PASS'
),
island_keys AS (
SELECT
part_id,
part_id - rn AS island_key
FROM passing
)
SELECT
MIN(part_id) AS range_start,
MAX(part_id) AS range_end,
MAX(part_id) - MIN(part_id) + 1 AS range_size
FROM island_keys
GROUP BY island_key
ORDER BY range_start;
Expected output:
range_start | range_end | range_size
1001 | 1005 | 5
1009 | 1012 | 4
1018 | 1018 | 1
range_size here is MAX — MIN + 1, not COUNT(*). For integer sequences where the islands are truly dense, this confirms span coverage. If your IDs aren’t guaranteed to be unique or increment by exactly one, use COUNT(*) instead and verify independently.
Finding Gaps Directly
Sometimes the goal isn’t to characterize the islands but to explicitly surface the gaps, the missing date ranges between consecutive active periods. This calls for comparing each island’s end to the next island’s start, which LEAD() handles elegantly:
WITH ranked AS (
SELECT
user_id,
activity_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY activity_date
) AS rn
FROM user_activity
),
island_keys AS (
SELECT
user_id,
activity_date,
activity_date - rn * INTERVAL '1 day' AS island_key
FROM ranked
),
islands AS (
SELECT
user_id,
MIN(activity_date) AS island_start,
MAX(activity_date) AS island_end
FROM island_keys
GROUP BY user_id, island_key
)
SELECT
user_id,
island_end + INTERVAL '1 day' AS gap_start,
LEAD(island_start) OVER (
PARTITION BY user_id ORDER BY island_start
) - INTERVAL '1 day' AS gap_end
FROM islands;
Expected output:
user_id | gap_start | gap_end
42 | 2025-01-04 | 2025-01-05
Rows where gap_end is NULL mark the period after the user’s last island — no subsequent island bounds the gap. Add WHERE gap_end IS NOT NULL to filter those out if you only care about confirmed gaps between activity periods.
This three-CTE structure (rank, subtract, summarize) shows up repeatedly in production analytics. I find it worth memorizing not as a recipe but as a reasoning pattern: consecutive things, when offset by their own rank, produce a constant that acts as a group key. Once that clicks, you’ll recognize variations of this problem in subscription analytics, network uptime monitoring, and financial drawdown detection almost immediately.
Why This Pattern Is Worth Understanding Deeply
The subtraction trick works because it converts a relative property (consecutive-ness) into an absolute one (a shared constant). That shift in perspective explains why the pattern scales instead of iterating row by row, you derive a group key mathematically and let the database handle the aggregation in a single scan. On large tables with proper indexing on the sequence column and partition key, this approach holds up well.
A few edge cases are worth knowing upfront. The pattern assumes your sequence is dense, consecutive values differ by exactly one unit. If your dates can have same-day duplicates, or if your IDs skip by design, you need to deduplicate or normalize first. A DISTINCT on the date column or a targeted WHERE clause is usually enough. And the ORDER BY inside ROW_NUMBER() must reference the sequence column itself, not insertion order or a surrogate key. Relational tables have no inherent row ordering, so that ORDER BY isn’t decorative. it’s doing real semantic work.
SQL’s set-based nature is often framed as a limitation compared to procedural languages. For gaps and islands problems, it’s actually the edge. The next time you see sequential data and someone reaches for a cursor or a loop, reach for the subtraction trick instead. Chances are you’ll get the same answer in a fraction of the code, with better performance to boot.
Gaps and Islands was originally published in Data Decoded on Medium, where people are continuing the conversation by highlighting and responding to this story.