P14 · SQL & Querying
SQL gaps and islands — grouping consecutive runs
How the SQL gaps-and-islands trick groups consecutive rows into runs — and why a plain COUNT hides that a streak actually broke and restarted.
Start with the basics
Gaps-and-islands is how you group consecutive rows into runs — logged-in stretches, unbroken streaks, back-to-back events — and spot the gaps between them.
The classic trick: number the rows in order, then subtract that number from the value you're tracking. Rows in the same unbroken run share the same result, so you can group by it.
It looks like a magic trick the first time, but it falls out of counting. Sessionization is the same idea applied to time, with one twist about where a run really breaks.
One that works
| t |
|---|
| 1 |
| 2 |
| 4 |
SELECT t - ROW_NUMBER() OVER (ORDER BY t) AS island
FROM reads
ORDER BY t;| island |
|---|
| 0 |
| 0 |
| 1 |
t=1,2 are consecutive (island 0); t=4 skips 3, so it starts a new island.
Consecutive rows form an 'island'; subtracting a row number from the value gives each run a constant key you can group by.
When to use it
- Reach for gaps-and-islands to find streaks, sessions, or unbroken runs of events.
- Number the rows in order and subtract; equal results mark the same run.
- For time-based sessions, break a run when the gap between events exceeds a timeout.
That is exactly what a plain count misses. Counting the matching rows tells you how many there are, not whether they were consecutive — so a broken streak and an unbroken one look identical. Here is the day that bites.
See it happen
The streak that secretly broke — COUNT vs the islands key
A gate logs 'in' at times 1, 2, and 4, with an 'out' at 3. You want the length of each unbroken 'in' run. Watch how a plain count merges two runs into one.
| t | gate |
|---|---|
| 1 | in |
| 2 | in |
| 3 | out |
| 4 | in |
COUNT — one run of 3
SELECT COUNT(*) AS len
FROM reads
WHERE gate = 'in';| len |
|---|
| 3 |
islands key — two real runs
SELECT island, COUNT(*) AS len FROM (
SELECT t - ROW_NUMBER() OVER (ORDER BY t) AS island
FROM reads WHERE gate = 'in'
) GROUP BY island ORDER BY island;| island | len |
|---|---|
| 0 | 2 |
| 1 | 1 |
Counting 'in' rows says one run of 3, but the gate reopened at t=4 after an 'out' — the row-number-difference key splits it into the two real runs.
Walk the full example in the unit →The rest of the family
More ways sessionizing trips you up
Once each row carries a session id, duration and events-per-session are just grouping by that id.
The first event of a session and sessions that span midnight are the classic places a count lands one off.
Giving each row its predecessor and taking the difference reveals the gap; the first row has no predecessor, which is expected.
Mark a new session where the gap exceeds the timeout, then add up those flags into a session id — and partition per entity or sessions bleed across users.
A range starts a new island only when it begins after the running maximum of all previous ends.
Self-check
How to spot it in your own SQL
- You need the length of unbroken runs, not just a total count.
- You are grouping events into sessions by a time gap.
- A streak count looks too long because a break was ignored.
- Sessions from different users seem to run together.
What people miss
Streaks and sessions look like a counting problem until you realize a count can't see a gap. Two 'in' runs and one long run produce the same total — the break is invisible unless you group by consecutiveness.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
How do I group consecutive rows into runs in SQL?
Number the rows in order, then subtract that row number from the value you're tracking. Rows in the same unbroken run land on the same result, so you can group by it.
Each distinct result is one 'island' — a consecutive run.
from the unit →How do I split events into sessions by a time gap?
Flag a row as a new session whenever the gap since the previous event exceeds your timeout, then add up those flags to give every row a session id.
Do it per user (partition by the entity), or one user's sessions will bleed into another's.
from the unit →Why does my streak count merge two separate runs?
A plain count of matching rows can't tell whether they were consecutive, so a broken streak and an unbroken one count the same.
Group by the islands key so each unbroken run is counted separately.
from the unit →How do I find the gap between each row and the previous one?
Give each row its predecessor's value and subtract — that difference is the gap. The first row has no predecessor, so its gap is blank by design.
A gap over your threshold is where a new run or session begins.
from the unit →That’s the free taste