Gaps and islands

The idea

Walk a calendar and number every day: 1, 2, 3, 4… Now number the days a second time, but keep a separate count for the active days and for the missing ones. Inside an unbroken run both counters step forward together, so the difference between them stands perfectly still.

The moment the run breaks, one counter carries on while the other pauses, and the difference jumps. That frozen difference is a group key. Group by it (together with which kind of run you are in) and each group is one island; min, max and count hand you its start, end and length. Whatever falls between two islands is a gap, and a "streak" is just the longest island.

Watch the difference freeze

How it works

Two window functions do all the work. ROW_NUMBER() OVER (ORDER BY day) gives a row its position in the whole ordered set. ROW_NUMBER() OVER (PARTITION BY key ORDER BY day) gives its position inside its own group. Both advance by one per row while a run is unbroken, so their difference is constant; when a run is interrupted, only the first counter advances during the interruption, so the difference rises by exactly the size of the interruption.

The difference is meaningless on its own: it is only a label. Group by the partition key plus the difference and every group is a maximal run.

-- one row per calendar day, is_active tells you which kind of run you are in
WITH stamped AS (
  SELECT
    day,
    is_active,
    ROW_NUMBER() OVER (ORDER BY day)                            AS rn_all,
    ROW_NUMBER() OVER (PARTITION BY is_active ORDER BY day)     AS rn_key
  FROM calendar
)
SELECT
  is_active,
  MIN(day)  AS start_day,
  MAX(day)  AS end_day,
  COUNT(*)  AS length
FROM stamped
GROUP BY is_active, rn_all - rn_key   -- the key must include is_active
ORDER BY start_day;

When you only have the rows that exist (logins, say) and "consecutive" means consecutive calendar dates, the date itself plays the part of rn_all:

SELECT user_id,
       MIN(d) AS streak_start,
       MAX(d) AS streak_end,
       COUNT(*) AS streak_len
FROM (
  SELECT user_id, d,
         d - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) * INTERVAL '1 day' AS grp
  FROM (SELECT DISTINCT user_id, login_at::date AS d FROM logins) x
) y
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Cost

The sort dominates, and it disappears to O(n) if an index already supplies (key, day) order. The trade-off: you touch every row and materialise the whole sorted set, but you get every island in a single declarative pass. No cursor, no self-join, no recursive CTE, and the planner can parallelise it.

Watch out for

• Duplicates silently break it. Two rows for the same date each get their own row number, so the difference drops by one mid-run and splits a perfectly good island. De-duplicate first: SELECT DISTINCT user_id, login_at::date, or aggregate to one row per day.
• Grouping by the difference alone. The difference is only unique within a partition. In the strip above the missing run 4 to 5 and an active island can both carry diff 3. Always GROUP BY user_id, is_active, …, diff: every column you partitioned by belongs in the group key.
• The sequence must be dense in the unit you care about. date - row_number * 1 day assumes consecutive means "one calendar day apart". If weekends or holidays don't count, subtract from a position in a business-day calendar table instead, not from the raw date.
• Ambiguous ordering. If the ORDER BY column has ties, row numbers are assigned arbitrarily and the islands wobble between runs. Order by something total, or break the tie explicitly.
• Gaps need their own care at the edges. Deriving gaps from island endpoints with LEAD(start) OVER (…) gives you the interior gaps only: the stretch before the first island and after the last one has no neighbour to compare with, so handle those two boundaries deliberately.

Worked example

"What is each user's longest login streak?" User 7 logged in on 10, 11, 12, 15, 16, 17, 18, 20, 21 and 22 March. Number those ten dates 1 through 10 and subtract the row number as days: 10−1, 11−2, 12−3 all land on Mar 09; 15−4 … 18−7 all land on Mar 11; 20−8 … 22−10 all land on Mar 12.

Three anchors, three streaks. GROUP BY user_id, grp with MIN, MAX, COUNT returns 10 to 12 (3 days), 15 to 18 (4 days) and 20 to 22 (3 days); one more MAX(streak_len) per user answers the question. The gaps fall out of the same rows: 13 to 14 and 19. Notice that if the 16th appeared twice in the log, the second copy would shift every later row number and report the four-day streak as two shorter ones, which is why the DISTINCT is not optional.

Check yourself