Gaps and islands, streaks and sessions

Gaps and islands is the name of a family of SQL problems about runs of consecutive rows. An island is such a run: the days in a row on which a user was active, invoice numbers with none missing, health checks that all said “up”. Gaps are the holes between islands. Login streaks are islands of consecutive days, and sessions are islands of events that come close together in time. One idea solves most of them: number the rows in order with row_number() and subtract the number from the value. Inside an island both rise by one per row, so the difference stays the same, and grouping by it returns one row per island with its first value, its last value and its length.

Why the value minus its row number finds the islands

Take one user who was active on days 1, 2, 3, 6, 7 and 10 of a month. The query runs on SQLite 3.49.1 in your browser:

-- The days of one month on which a user was active, as day numbers.
CREATE TABLE active (day INTEGER PRIMARY KEY);
INSERT INTO active VALUES (1), (2), (3), (6), (7), (10);

-- Number the days in order and subtract the number: within a run of consecutive days
-- both go up by 1, so the difference stays the same.
SELECT day,
       row_number() OVER (ORDER BY day)       AS rn,
       day - row_number() OVER (ORDER BY day) AS island
FROM active
ORDER BY day;

-- One row per island: group by the difference.
WITH numbered AS (
  SELECT day, day - row_number() OVER (ORDER BY day) AS island
  FROM active
)
SELECT min(day) AS first_day, max(day) AS last_day, count(*) AS days
FROM numbered
GROUP BY island
ORDER BY first_day;

┌─────┬────┬────────┐
│ day │ rn │ island │
├─────┼────┼────────┤
│ 1   │ 1  │ 0      │
│ 2   │ 2  │ 0      │
│ 3   │ 3  │ 0      │
│ 6   │ 4  │ 2      │
│ 7   │ 5  │ 2      │
│ 10  │ 6  │ 4      │
└─────┴────┴────────┘
┌───────────┬──────────┬──────┐
│ first_day │ last_day │ days │
├───────────┼──────────┼──────┤
│ 1         │ 3        │ 3    │
│ 6         │ 7        │ 2    │
│ 10        │ 10       │ 1    │
└───────────┴──────────┴──────┘

In the first result, row_number() counts the days from 1 to 6, and the difference is 0 for days 1 to 3, 2 for days 6 and 7, and 4 for day 10. It changes only where days are missing, and it grows by the number of days missed, so it works as a label for each island. The second result groups by that label and finds three islands, of 3, 2 and 1 days. The gaps are what lies between them: days 4 and 5, and days 8 and 9. Nothing here is specific to one engine: the pattern needs only row_number() and GROUP BY, so it runs in PostgreSQL, in MySQL 8.0 and later, in SQLite 3.25 and later, and in SQL Server and Oracle.

The same subtraction, or a close relative of it, answers each problem of the family:

• Streaks of dates subtract the row number in days, with date() in SQLite and day - n in PostgreSQL.
• Duplicate days break the subtraction, so streaks must count each day once, with DISTINCT or dense_rank().
• Gaps in a numbered sequence show up where lead() finds a next value more than 1 higher.
• Sessions start after a pause longer than a limit, such as 30 minutes, and a running sum of those starts numbers them.
• Runs of the same status subtract two row numbers: one over all rows, and one within the status.
• Time zones decide which calendar day an event falls on, so streaks have to use the user’s own calendar.

Login streaks from dates, with the longest one per user Pro: opens with a Pro, Premium or Ultimate pass