Gaps and islands in SQL: the longest streak is one subtraction

The streak question is one subtraction. "Find the longest run of consecutive login days per user" sounds like it needs a loop or a pile of self-joins, and it needs neither: number the rows, subtract the number from the date, group by the result. One page on the trick, the query, the LAG variant, and the three ways it silently breaks. The print-ready A4 PDF is at the bottom.

The problem

• Islands are runs of consecutive days.
• Gaps are the holes between them.
• The ask: the longest streak per user.

Dedupe first

• One row per user per day, or the trick breaks.
• SELECT DISTINCT user_id, login_at::date does it.
• Two logins on the same day split the streak in two.

The trick

• day - ROW_NUMBER() gives the same value on every row of an island.
• Number the rows per user: PARTITION BY user_id.
• One subtraction: that is the whole trick.

Group it

• GROUP BY the key: one row per island.
• MIN(day) and MAX(day) are the start and the end.
• COUNT(*) is the streak length.

Every streak, in one query

-- every login streak, per user
WITH d AS (
  SELECT DISTINCT user_id, login_at::date AS day
  FROM logins
), k AS (
  SELECT *, day - ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY day)::int AS grp
  FROM d
)
SELECT user_id, MIN(day), MAX(day), COUNT(*) AS streak
FROM k GROUP BY user_id, grp;

Oct 1, 2, 3 minus row numbers 1, 2, 3 all give Sep 30: one island. After a gap the date jumps and the row number does not, so the key changes. For the longest streak, take MAX(streak) per user on top of this result.

The ::int cast matters in PostgreSQL: ROW_NUMBER() returns a bigint, and a date minus a bigint is not defined, while a date minus an integer is a date.

The LAG variant

• day - LAG(day) > 1 flags the first row of a new island.
• A running SUM of the flags is the island id.
• Use it when the gap rule is custom: a threshold, a tolerance, a timeout.

WITH d AS (
  SELECT DISTINCT user_id, login_at::date AS day
  FROM logins
), f AS (
  SELECT *, CASE WHEN day - LAG(day) OVER (
    PARTITION BY user_id ORDER BY day) > 1
    THEN 1 ELSE 0 END AS new_island
  FROM d
), k AS (
  SELECT *, SUM(new_island) OVER (
    PARTITION BY user_id ORDER BY day) AS grp
  FROM f
)
SELECT user_id, MIN(day), MAX(day), COUNT(*) AS streak
FROM k GROUP BY user_id, grp;

Same result as the subtraction, in one more step. Change > 1 to > 2 and the streak survives one missed day.

At work

• Streaks: logins, habits, orders.
• Sessions: 30 minutes of silence starts a new one.
• Uptime and subscriptions: periods with no hole.

The trap: three ways the streak breaks

The quiz

Which key finds the streaks?

A) day - ROW_NUMBER() OVER (
   PARTITION BY user_id
   ORDER BY day)::int

B) day - LAG(day) OVER (
   PARTITION BY user_id
   ORDER BY day)

A. Inside a streak the date and the row number both grow by 1, so their difference is the same on every row and changes after each gap: one key per streak. B is the gap to the previous day: 1 on every consecutive day, whatever the streak, so separate streaks land in the same group.

Interview phrasing worth memorizing: consecutive dates minus a row number give a constant, so I group by that difference to get one row per streak.