Gaps and islands

The problem

You have a sequence — dates, integers, IDs — and you need to group the consecutive stretches, or find where the sequence skips.

The trick

For a run of consecutive values, value − ROW_NUMBER() is constant. When the sequence jumps, that difference jumps too. Group by the difference and each group is one island.

Islands: contiguous stretches

monthly_revenue has months 2026-01 … 2026-04 with no gap, so it's one island. This finds the start, end, and length of every run of consecutive order_ids per customer:

Loading editor…

Gaps: what's missing

Compare each value to the previous one with LAG; a jump greater than 1 is a gap:

Loading editor…

(The seed order_ids are 101–111 with no gaps, so this returns nothing — delete a row in your head and it would.)

Dates instead of integers

Same idea, but subtract a row number of days so the units line up:

SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
       COUNT(*) AS days
FROM (
  SELECT user_id, login_date,
    date(login_date, '-' ||
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS grp
  FROM logins
) t
GROUP BY user_id, grp;

Postgres: login_date - (ROW_NUMBER() OVER (...))::int gives the same anchor date.