sql gaps and islands pattern
Explains the SQL gaps-and-islands pattern for grouping consecutive sequences. Use it when finding streaks, sessionizing events, or collapsing consecutive date ranges. Not for simple GROUP BY aggregations.
TL;DR
Gaps-and-islands turns a sequence with breaks into groups: subtract row_number() from the value (or use a dense-rank trick) so consecutive rows share the same group key, then GROUP BY that key. It works in any dialect with window functions and handles streaks, sessions, and consecutive date ranges in one pass. The key insight is that the difference between a sequence and its row number is constant within each island.
The query
sql gaps and islands patternUse this when
- you need to find streaks of consecutive days, ids, or events
- you are sessionizing clickstreams or collapsing date ranges
- a GROUP BY alone cannot express the consecutive-runs logic
Not for
- plain per-group aggregates with no notion of consecutiveness
- databases without window functions
Steps
Order the rows and compute row_number() over that order alongside the sequence value. Expected output: Each row has its sequence value and its row number.
Compute the group key as value minus row_number() (for integers) or a date minus an interval multiple (for dates). Consecutive rows share the key. Expected output: Rows in the same island carry the same group key.
Group by the key and aggregate: min and max for the range, count for the streak length. Expected output: Each island collapses to one row with its start, end, and size.
Handle gaps explicitly: decide whether a gap of one missing value still counts as consecutive for your use case. Expected output: Your gap tolerance is encoded in the group key logic.
Test on a small hand-built dataset with known islands before running on the full table. Expected output: The small test returns exactly the islands you expect.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_LdC7lcZQ03aMCLj9CZSIMQ