Gaps and islands in SQL: streaks, sessions and duplicates
Gaps and islands in SQL come down to one label per run. The row number difference finds it, but without a tiebreaker one test reported 334 islands for 40.
The count says 6 and the longest streak is 3. Meter 7 reported on six days in March, went dark on March 5 and 6, and went dark again on March 9 and 10. This query cannot see that:
SELECT meter_id, count(DISTINCT report_on) AS streak
FROM meter_reports
GROUP BY meter_id; meter_id | streak
----------+--------
7 | 6That counts days with a report, and for a meter that never missed a day, that number equals the streak.
Every fixture with a healthy meter passes. Finding the longest unbroken run takes more than one
aggregate, because GROUP BY needs a value to group on and nothing in the table says which run a row
belongs to.
This is the gaps and islands problem. An island is a maximal run of adjacent rows, and a gap is the space between islands. Streaks, uptime windows, occupied date ranges and user sessions are all the same shape. The job in every case is to compute a label for each row that says which island it is in.
Here is the data:
CREATE TABLE meter_reports (
meter_id integer NOT NULL,
report_on date NOT NULL
);
INSERT INTO meter_reports VALUES
(7, '2026-03-02'), (7, '2026-03-03'), (7, '2026-03-04'),
(7, '2026-03-07'), (7, '2026-03-08'),
(7, '2026-03-11');Number each meter's days in order, then subtract the number from the date. Inside a run, the date and the row number each go up by one, so their difference does not change. After a gap, the date jumps further than the row number, and the difference moves to a new value:
SELECT report_on,
row_number() OVER w AS rn,
report_on - (row_number() OVER w)::integer AS island
FROM meter_reports
WINDOW w AS (PARTITION BY meter_id ORDER BY report_on)
ORDER BY report_on; report_on | rn | island
------------+----+------------
2026-03-02 | 1 | 2026-03-01
2026-03-03 | 2 | 2026-03-01
2026-03-04 | 3 | 2026-03-01
2026-03-07 | 4 | 2026-03-03
2026-03-08 | 5 | 2026-03-03
2026-03-11 | 6 | 2026-03-05The resulting date means nothing on its own. It is only useful because it is the same across a run and
different between runs. The ::integer cast is required in PostgreSQL: row_number() returns bigint,
and without the cast you get operator does not exist: date - bigint.
Date minus row number stays constant inside a run
Island key 2026-03-01
- Mar 2 row 1
- Mar 3 row 2
- Mar 4 row 3
3 days
Island key 2026-03-03
- Mar 7 row 4
- Mar 8 row 5
2 days
Island key 2026-03-05
- Mar 11 row 6
1 day
A duplicate breaks the numbering
A second Mar 3 row takes number 3 while the date stands still. The key drops to 2026-02-28 and one 3-day run is reported as two runs of 2.
Now there is something to group on:
WITH numbered AS (
SELECT meter_id, report_on,
report_on - (row_number() OVER (
PARTITION BY meter_id ORDER BY report_on))::integer AS island
FROM meter_reports
)
SELECT meter_id,
min(report_on) AS first_day,
max(report_on) AS last_day,
count(*) AS days
FROM numbered
GROUP BY meter_id, island
ORDER BY meter_id, first_day; meter_id | first_day | last_day | days
----------+------------+------------+------
7 | 2026-03-02 | 2026-03-04 | 3
7 | 2026-03-07 | 2026-03-08 | 2
7 | 2026-03-11 | 2026-03-11 | 1Wrap that with max(days) per meter and the streak becomes 3. If you need the gaps
themselves, skip the labels and compare neighbours. lead(report_on) gives each row its successor, and
any pair more than one day apart is a gap: here March 5 to 6 and March 9 to 10, 2 days each. The
window functions overview covers OVER, partitions and lead if those
are new.
Add a second report for March 3, which a retrying meter would send sooner or later. The date stands still while the row number advances, so the key shifts in the middle of a real run:
report_on | rn | island_rn | dr | island_dr
------------+----+------------+----+------------
2026-03-02 | 1 | 2026-03-01 | 1 | 2026-03-01
2026-03-03 | 2 | 2026-03-01 | 2 | 2026-03-01
2026-03-03 | 3 | 2026-02-28 | 2 | 2026-03-01
2026-03-04 | 4 | 2026-02-28 | 3 | 2026-03-01The 3-day run becomes two 2-day islands, March 2 to 3 and March 3 to 4. Two repairs work. Reducing to
one row per day first, with SELECT DISTINCT meter_id, report_on, returns the original three islands.
The other is dense_rank(), shown in the dr columns, which the
PostgreSQL window function reference
describes as effectively counting peer groups, so both March 3 rows get the same number. Use DISTINCT
if you want count(*) to mean days. With dense_rank() the rows stay, so count days with
count(DISTINCT report_on).
One duplicate row turned a 3-day run into two 2-day islands, with no error from the query.
A doubled day is among the traps in "Longest Login Streak", an exercise in the database set that runs against a real PostgreSQL database, with its evidence laid out in the database track guide.
Sometimes the sequence has no regular step, and what defines the island is a value. Consider link checks
at irregular times, with runs of up and down. Date arithmetic has nothing to work with, so use two
row numbers: one across all rows and one within each status. Inside a run of one status both counters
advance together, and when the other status interrupts, only the overall counter moves:
WITH labelled AS (
SELECT checked_at, status,
row_number() OVER (ORDER BY checked_at, id)
- row_number() OVER (PARTITION BY status ORDER BY checked_at, id) AS island
FROM link_checks
)
SELECT status,
min(checked_at) AS first_check,
max(checked_at) AS last_check,
count(*) AS checks
FROM labelled
GROUP BY status, island
ORDER BY first_check;Over seven sample checks, with the timestamps cut to the time for width:
status | first_check | last_check | checks
--------+-------------+------------+--------
up | 10:00:00 | 10:05:00 | 2
down | 10:11:00 | 10:15:00 | 2
up | 10:21:00 | 10:21:00 | 1
down | 10:25:00 | 10:25:00 | 1
up | 10:32:00 | 10:32:00 | 1Group on the status as well as the difference. In this sample, down and up runs both produced the
difference 2 and both produced 3, so the difference alone does not identify an island.
The id in both ORDER BY clauses is doing real work. Two window definitions with different partitions
can each sort tied rows differently, and nothing in the SQL fixes that order. Take 2,000 checks where
five rows share each timestamp and the status flips every 50 rows, so the true answer is 40 islands:
ORDER BY checked_at 334 islands
ORDER BY checked_at, id 40 islandsThose numbers come from one run on postgres:17, and the 334 could differ with another data order or
plan. Without a unique ordering, the numbers on tied rows are arbitrary, and so is the island count.
Timestamps have no "next value", so the row number difference does not apply directly. A session, or here a vehicle trip built from GPS pings, is defined by a threshold: a new trip starts after more than 10 minutes of silence. The label comes from two steps, flagging where an island starts and then running a sum over the flags:
WITH flagged AS (
SELECT id, vehicle_id, pinged_at,
CASE
WHEN pinged_at - lag(pinged_at) OVER w <= interval '10 minutes' THEN 0
ELSE 1
END AS starts_trip
FROM gps_pings
WINDOW w AS (PARTITION BY vehicle_id ORDER BY pinged_at, id)
),
numbered AS (
SELECT *,
sum(starts_trip) OVER (
PARTITION BY vehicle_id ORDER BY pinged_at, id) AS trip
FROM flagged
)
SELECT vehicle_id, trip,
min(pinged_at)::time AS started,
max(pinged_at)::time AS ended,
count(*) AS pings
FROM numbered
GROUP BY vehicle_id, trip
ORDER BY vehicle_id, trip;With pings at 08:00, 08:04, 08:14, 08:40, two at 08:43, and one at 09:30:
vehicle_id | trip | started | ended | pings
------------+------+----------+----------+-------
42 | 1 | 08:00:00 | 08:14:00 | 3
42 | 2 | 08:40:00 | 08:43:00 | 3
42 | 3 | 09:30:00 | 09:30:00 | 1The first ping has a NULL from lag, so the comparison is unknown and the CASE falls through to 1,
which correctly starts a trip. The gap from 08:04 to 08:14 is exactly 10 minutes and stays in the trip,
because the rule says more than. Write down which side of the boundary you want and test that exact value.
The tiebreaker matters here too. Per the
window call documentation,
the default frame runs through the current row's last ORDER BY peer. On two tied rows, a running
sum of ones with ORDER BY pinged_at returned 3 and 3, while adding id returned 2 and 3. The trip
query above returns the same three trips either way, but other running totals will not be so forgiving,
so keep the id.
Date-based streaks have one more input that never appears in the query: the day boundary. Casting a
timestamptz to date uses the session's time zone. These two events are one hour apart:
at | utc_day | ny_day | tokyo_day
------------------------+------------+------------+------------
2026-03-03 23:30:00+00 | 2026-03-03 | 2026-03-03 | 2026-03-04
2026-03-04 00:30:00+00 | 2026-03-04 | 2026-03-03 | 2026-03-04In UTC they are two consecutive days. In New York and Tokyo they are one day, computed with
(at AT TIME ZONE 'America/New_York')::date. A person's streak should use that person's zone, and a
fleet report should use whichever zone its contract names. Pick one explicitly, because the alternative
is a streak that changes with a server setting. When your runs follow links between rows instead of
adjacent values, recursive CTEs take over.
Practice Databases & EF Core
- Open in the editor: Filter Before You Fetch
EF Core and SQL Easy Free, no account needed
Filter Before You Fetch
Filter measurements on the database, not after loading the whole table.
- Open the Pro kata: Longest Login Streak
EF Core and SQL Hard Katabench Pro
Longest Login Streak
Gaps and islands: group consecutive days with the date minus ROW_NUMBER trick to find each player's longest streak.
More like this: EF Core practice →