SQL · Guides · October 29, 2026 · 11 min read

Gaps and islands in SQL: streaks, consecutive days and pauses

“Gaps and islands” is the name for a family of SQL problems about sequences. An island is a run of consecutive values: days in a row a user was active, hours in a row an error rate stayed high, consecutive ticket numbers. A gap is the hole between two islands. Typical questions: what is each user’s longest streak, when did a failure start and end, how long did customers stay away before coming back.

The standard solution is short. For consecutive days it is: number the days with ROW_NUMBER(), subtract that number from the date, and group by the result. Every day in the same streak gets the same value. Gaps are even simpler: compare each row with the previous one using LAG().

Below, both techniques are worked through on a real dataset: the app event log of a training airline (app_events, about 1.5 million events from 1 July to 11 August 2026). Every query was run on that data and the tables show its actual output, so you can repeat each step in the Nomto SQL room.

Step 1: reduce events to one row per member per day

Streaks are about days, not events. A member who opened the app ten times on Tuesday was active on one day. So the first step is always SELECT DISTINCT on the entity and the date:

SELECT DISTINCT member_id, CAST(event_time AS DATE) AS day
FROM app_events
WHERE member_id IS NOT NULL;

This gives 130,413 member-days for 33,624 logged-in members. Without DISTINCT the row-number trick breaks, because two rows for the same date would get different numbers and fall into different islands.

Step 2: the row_number difference trick

Take member 16292. Number their active days in date order and subtract the number from the date:

WITH days AS (
  SELECT DISTINCT member_id, CAST(event_time AS DATE) AS day
  FROM app_events
  WHERE member_id = 16292
)
SELECT day,
  ROW_NUMBER() OVER (ORDER BY day) AS rn,
  day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island
FROM days
ORDER BY day;

Why it works: inside a streak, the date and the row number both go up by exactly one per row, so their difference stays constant. When a day is skipped, the date jumps by two or more while the row number still goes up by one, so the difference changes. The value in island has no meaning of its own (2026-07-22 is not a day the member was active); it is just a label shared by all days of one streak.

The CAST(... AS INTEGER) is there because ROW_NUMBER() returns a bigint, and in PostgreSQL date - integer gives a date while date - bigint has no operator. The cast keeps the query portable.

Step 3: group by the island key

Now each streak becomes one row with its start, end and length:

WITH days AS (
  SELECT DISTINCT CAST(event_time AS DATE) AS day
  FROM app_events
  WHERE member_id = 16292
),
grp AS (
  SELECT day,
    day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island
  FROM days
)
SELECT MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM grp
GROUP BY island
ORDER BY streak_start;

For all members at once, add PARTITION BY member_id to the window and member_id to the GROUP BY. The row numbers then restart for each member, and two members can share an island label without being mixed up.

Longest streak per member

With the streaks in hand, the longest one per member is a MAX. Here it is turned into a distribution, which is the usual way to report it:

WITH days AS (
  SELECT DISTINCT member_id, CAST(event_time AS DATE) AS day
  FROM app_events
  WHERE member_id IS NOT NULL
),
grp AS (
  SELECT member_id,
    day - CAST(ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY day) AS INTEGER) AS island
  FROM days
),
streaks AS (
  SELECT member_id, COUNT(*) AS n
  FROM grp
  GROUP BY member_id, island
),
best AS (
  SELECT member_id, MAX(n) AS longest
  FROM streaks
  GROUP BY member_id
)
SELECT
  CASE WHEN longest = 1 THEN '1'
       WHEN longest <= 3 THEN '2-3'
       WHEN longest <= 6 THEN '4-6'
       WHEN longest <= 13 THEN '7-13'
       ELSE '14+' END AS longest_streak,
  COUNT(*) AS members
FROM best
GROUP BY 1
ORDER BY MIN(longest);

Most logged-in members never came back on the next day; 1,897 had a streak of a week or longer. Note the odd shape: fewer members in 4–6 than in 7–13. That is the kind of thing worth checking in the raw data before presenting it; a distribution from a real log is rarely smooth.

Two caveats apply to any streak metric. The log starts on 1 July, so a streak that touches 1 July may have begun earlier. And the last day, 11 August, is cut at 18:00, so streaks running into it are not finished. Both ends of the observation window censor the data.

Finding the gaps with LAG

A gap is the space between the end of one island and the start of the next. You do not need the island key for it: LAG() brings the previous active day onto the current row, and any difference greater than one day is a gap.

WITH days AS (
  SELECT DISTINCT CAST(event_time AS DATE) AS day
  FROM app_events
  WHERE member_id = 16292
),
g AS (
  SELECT LAG(day) OVER (ORDER BY day) AS prev_day, day
  FROM days
)
SELECT prev_day + 1 AS gap_start,
  day - 1 AS gap_end,
  day - prev_day - 1 AS days_missing
FROM g
WHERE day - prev_day > 1
ORDER BY gap_start;

This answers “comeback” questions directly: member 16292 was away for 12 days and then returned. Filter on days_missing >= 7 and you have every return after a week or more of inactivity. The first row of each member has prev_day NULL; the WHERE drops it automatically because a comparison with NULL is not true.

In PostgreSQL, date - date returns an integer number of days, so day - prev_day can be compared with numbers directly. For timestamps it returns an interval, and you compare with INTERVAL '30 minutes' instead.

Islands with a tolerance: allowing short breaks

The row-number trick only accepts strictly consecutive days. Real definitions are often looser: “a streak continues if the member comes back within two days”. For that, switch to the second general technique: flag where a new island starts, then take a running sum of the flags.

WITH days AS (
  SELECT DISTINCT CAST(event_time AS DATE) AS day
  FROM app_events
  WHERE member_id = 16292
),
flagged AS (
  SELECT day,
    CASE WHEN day - LAG(day) OVER (ORDER BY day) <= 2 THEN 0 ELSE 1 END AS new_island
  FROM days
),
numbered AS (
  SELECT day, SUM(new_island) OVER (ORDER BY day) AS island_id
  FROM flagged
)
SELECT island_id, MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS active_days
FROM numbered
GROUP BY island_id
ORDER BY island_id;

The one-day break on 8 August no longer splits the August streak. The first row gets new_island = 1 because LAG returns NULL there and the CASE falls through to ELSE. The running sum (SUM(...) OVER (ORDER BY day), see our guide to running totals in SQL) turns the flags into island numbers 1, 2, 3…

This flag-and-sum pattern is the most flexible of the two: it works for any rule you can express by comparing a row with the previous one, including timestamps (sessions split by 30 minutes of inactivity) and values that are not dates at all.

Islands by condition: when a metric stayed bad

An island does not have to be activity. It can be any run of time buckets where a condition holds. Here, days when the iOS app logged 200 or more errors:

WITH ev AS (
  SELECT DISTINCT client_id, event_time, event_name
  FROM app_events
  WHERE platform = 'ios'
),
daily AS (
  SELECT CAST(event_time AS DATE) AS day,
    COUNT(*) FILTER (WHERE event_name = 'error') AS errors
  FROM ev
  GROUP BY CAST(event_time AS DATE)
),
bad AS (
  SELECT day, errors,
    day - CAST(ROW_NUMBER() OVER (ORDER BY day) AS INTEGER) AS island
  FROM daily
  WHERE errors >= 200
)
SELECT MIN(day) AS first_day, MAX(day) AS last_day,
  COUNT(*) AS days, SUM(errors) AS errors
FROM bad
GROUP BY island
ORDER BY first_day;

The order of operations is the whole method: build the series by bucket, apply the condition in WHERE, then compute the island key among the rows that remain. The first CTE removes repeated deliveries of the same event (about 0.7% of the log arrives twice with a different event_id), so they do not inflate the counts.

If you need both the bad periods and the normal periods between them, use flag-and-sum on the condition instead:

WITH ev AS (
  SELECT DISTINCT client_id, event_time, event_name
  FROM app_events
  WHERE platform = 'ios'
),
daily AS (
  SELECT CAST(event_time AS DATE) AS day,
    COUNT(*) FILTER (WHERE event_name = 'error') AS errors
  FROM ev
  GROUP BY CAST(event_time AS DATE)
),
flagged AS (
  SELECT day, errors, errors >= 200 AS is_bad,
    CASE WHEN (errors >= 200) = LAG(errors >= 200) OVER (ORDER BY day)
         THEN 0 ELSE 1 END AS new_run
  FROM daily
),
runs AS (
  SELECT day, is_bad, SUM(new_run) OVER (ORDER BY day) AS run_id
  FROM flagged
)
SELECT run_id, is_bad, MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days
FROM runs
GROUP BY run_id, is_bad
ORDER BY run_id;

The whole period is now split into alternating runs, which is exactly what you need for “conversion during the incident versus outside it”.

One warning for condition-based islands: the series must not skip buckets. If a day has no events at all, it is missing from daily, and an island can silently jump across it. In this log every day has iOS events, but in general build a full calendar first with generate_series and decide whether an empty day counts as good or bad. Our guide on filling missing dates in SQL shows how.

Which technique to use

For hourly buckets the row-number trick still works; multiply the number by the step instead of casting: hour_start - ROW_NUMBER() OVER (ORDER BY hour_start) * INTERVAL '1 hour'.

All the queries here run on the course dataset, so in the Nomto SQL room you can swap member 16292 for any other ID, change the tolerance from 2 days to 7, or move the error threshold and see how the islands change.

FAQ

What is the gaps and islands problem in SQL?

It is the task of finding runs of consecutive values (islands) and the holes between them (gaps) in an ordered sequence: streaks of active days, uninterrupted periods of an outage, missing numbers in a series.

How do I find consecutive days in SQL?

Reduce the data to one row per entity per day, then compute day - ROW_NUMBER() OVER (PARTITION BY entity ORDER BY day). Consecutive days share the same result; group by it to get each streak’s start, end and length.

How do I find the longest streak for each user?

Build the streaks with the row-number trick, count days per streak, then take MAX of that count per user. Remember that streaks touching the first or last day of your data may be incomplete.

Why do I need DISTINCT before ROW_NUMBER?

If a day appears twice, the two rows get different row numbers but the same date, so the difference changes in the middle of a streak and splits it. One row per entity per day keeps the arithmetic right.

Can I solve gaps and islands without window functions?

Yes, with self-joins or correlated subqueries (“is there a row for the previous day?”), but these are slower and harder to read. Every mainstream database today supports ROW_NUMBER() and LAG(), so window functions are the standard answer.