Skip to content
Katabench
Try free
9 min read The Katabench team

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:

SQL
SELECT meter_id, count(DISTINCT report_on) AS streak
FROM meter_reports
GROUP BY meter_id;
Text
 meter_id | streak
----------+--------
        7 |      6

That 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.

The row number difference

Here is the data:

SQL
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:

SQL
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;
Text
 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-05

The 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.

A missing day moves the date without moving the row number, so the key jumps by exactly the size of the gap. Here each gap is 2 days.

Now there is something to group on:

SQL
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;
Text
 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 |    1

Wrap 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.

Duplicates split an island

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:

Text
 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-01

The 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.

Islands of a status, and the tie that creates 334 of them

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:

SQL
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:

Text
 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   |      1

Group 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:

Text
ORDER BY checked_at             334 islands
ORDER BY checked_at, id          40 islands

Those 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.

Sessionizing: when a gap is a duration

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:

SQL
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:

Text
 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 |     1

The 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.

Time zones decide where the islands break

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:

Text
           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-04

In 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

More like this: EF Core practice →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.