Finding consecutive days in SQL (gaps and islands)

"Find each user's longest login streak" comes up in a lot of data interviews. The trick behind it is short, but it's hard to come up with under pressure if you haven't seen it. It's known as gaps and islands.

The idea

Take one user's login dates in order and number them. When dates are consecutive, the date and the row number both go up by one, so date minus row number stays constant. When there's a gap, the date jumps but the row number doesn't, and the constant changes. That constant labels each streak.

SELECT login_date,
       ROW_NUMBER() OVER (ORDER BY login_date) AS n,
       date(login_date, '-' || ROW_NUMBER() OVER (ORDER BY login_date) || ' days') AS streak_id
FROM logins
WHERE user_id = 1
ORDER BY login_date
LIMIT 12;

Watch streak_id. It holds steady across a run of consecutive days, then moves after a gap.

From label to streak length

Group by user and label, and each group is one streak:

WITH labeled AS (
  SELECT user_id, login_date,
         date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS streak_id
  FROM logins
), streaks AS (
  SELECT user_id, MIN(login_date) AS started, MAX(login_date) AS ended, COUNT(*) AS days
  FROM labeled
  GROUP BY user_id, streak_id
)
SELECT * FROM streaks ORDER BY days DESC, user_id LIMIT 5;

Duplicates break it

This table has one row per user per day. Real event tables don't. If a user has two logins on the same day, the row number rises while the date doesn't, and the streak splits in the wrong place. Remove duplicates first with SELECT DISTINCT user_id, date(event_time) in a CTE, or use DENSE_RANK in place of ROW_NUMBER, which gives equal dates equal numbers.

Other databases

The logic is the same everywhere and only the date arithmetic changes. In Postgres, login_date - (ROW_NUMBER() OVER (...))::int works directly. In SQL Server, use DATEADD(day, -ROW_NUMBER() OVER (...), login_date).

The same labeling works for any "consecutive" question, including runs of months, or a sequence of rows with the same status.