SQL Gaps and Islands: Finding Consecutive Streaks

The trick

Within a run of consecutive days, the date and the row number both go up by 1 per row, so date − row_number does not change. After a gap the date jumps further than the row number, so the difference changes. That difference is a label for each island.

To keep the examples identical in MySQL and PostgreSQL, the logins all fall in one month and use EXTRACT(DAY FROM login_date) as the day number.

With real dates

Across months, subtract the row number as days instead: DATE_SUB(login_date, INTERVAL rn DAY) in MySQL, or login_date - rn::int in PostgreSQL. The grouping step stays the same.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT user_id, login_date,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
       EXTRACT(DAY FROM login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS island
FROM logins
ORDER BY user_id, login_date;

WITH numbered AS (
  SELECT user_id, login_date,
         EXTRACT(DAY FROM login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS island
  FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, island
ORDER BY user_id, streak_start;

WITH numbered AS (
  SELECT user_id, login_date,
         EXTRACT(DAY FROM login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS island
  FROM logins
),
streaks AS (
  SELECT user_id, COUNT(*) AS days
  FROM numbered
  GROUP BY user_id, island
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id
HAVING MAX(days) >= 3
ORDER BY user_id;

Common mistakes

• Duplicate dates. Two logins on one day get different row numbers but the same date, which splits the streak. Start from SELECT DISTINCT user_id, login_date.
• Negative numbers in MySQL. ROW_NUMBER() is unsigned in MySQL, so small_number - ROW_NUMBER() can raise an "out of range" error. Subtract in an order that stays positive, or use the date form.
• Checking only neighbours. Comparing each row with LAG finds where a streak starts but not its length. Combine it with a running count, or use the ROW_NUMBER trick.

Practise Gaps and Islands

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   User Consecutive 3-Day Login Streak

   Detect users who logged into Google services on at least three consecutive calendar days.

   Hard✓ Solved
2. 2

   Longest Login Streaks

   Find users who logged in on at least three consecutive days and report their best streak.

   Hard✓ Solved
3. 3

   Shows at #1 for Three Weeks

   Find TV shows that held the weekly #1 spot for at least three consecutive weeks.

   Hard✓ Solved
4. 4

   Stadium Crowd Streaks

   Find days that belong to a run of three or more consecutive busy days.

   Hard✓ Solved
5. 5

   Job Status Periods

   Collapse daily job results into continuous succeeded and failed periods.

   Hard✓ Solved

Want a full set? Work through the Dates & Time study plan.

Questions people ask

Can I solve it with LAG instead?

Yes. Flag rows where the gap to the previous date is more than one day, take a running SUM of the flags as the streak id, then group by it. Both approaches are common in interviews.

Does this work for numbers, not dates?

Yes. The same trick finds runs of consecutive ids or seat numbers: value minus row number is constant within each run.

Related concepts

• SQL LAG and LEAD Functions Explained
• SQL ROW_NUMBER vs RANK vs DENSE_RANK