“Gaps and islands”: given an ordered sequence with holes in it (invoice numbers, login dates, sensor readings), group the consecutive stretches into runs. The trick works because ROW_NUMBER() always climbs by exactly 1 per row — subtract it from a value that also climbs by 1 inside a run, and the difference stays flat until the next gap breaks it.

-- consecutive integer runs — identical in MySQL 8+, PostgreSQL, SQLite 3.25+, BigQuery, Snowflake, DuckDB
SELECT
  MIN(n) AS island_start,
  MAX(n) AS island_end,
  COUNT(*) AS island_size
FROM (
  SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) AS grp
  FROM numbers
) t
GROUP BY grp
ORDER BY island_start;

For n = 1,2,3, 7,8, 11, grp is 0,0,0, 3,3, 5 — three islands: 1-3, 7-8, 11-11.

The same idea groups dates into streaks (e.g. consecutive days a user logged in), but date arithmetic isn’t identical across dialects:

-- PostgreSQL, DuckDB: DATE - integer returns a DATE, so it works exactly like the integer version
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_days
FROM (
  SELECT
    user_id, login_date,
    login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM logins
) t
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Dialect notes for the grp expression (replace login_date - ROW_NUMBER() OVER (...) with):

MySQL, BigQuery: DATE_SUB(login_date, INTERVAL rn DAY)
Snowflake: DATEADD(day, -rn, login_date)
SQLite: dates are TEXT — date(login_date, '-' || rn || ' days')

(rn is ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), computed in an inner subquery since none of these let you reference a sibling SELECT-list alias inside the same expression.)

A gap’s length is the difference between two islands, not a property of one row. To list the gaps themselves (not just the islands), join each island to the next one by row order — e.g. LEAD(island_start) OVER (ORDER BY island_start) - island_end - 1 — see the lag-lead recipe for fetching a neighboring row’s value. And always add a unique tiebreaker to ORDER BY in the ROW_NUMBER(): ties make the run detection nondeterministic.

Find gaps and consecutive runs (gaps and islands)