Lesson 12.8 · Reading time: ~7 min

Gaps and islands is the family of tasks about sequences: find runs of consecutive values (islands) and the holes between them (gaps). Typical questions are "the longest login streak of each user", "periods when a sensor sent data", "missing invoice numbers". It's a favorite advanced SQL interview question. In this lesson you'll learn the ROW_NUMBER trick that groups consecutive dates into islands, find gaps with LEAD, and calculate the longest streak of each customer.

Gaps and Islands in SQL

An island is a run of values with no hole: consecutive days, consecutive numbers. A gap is a run of missing values between two islands. The challenge is that SQL works with sets, and "consecutive" is a relationship between rows. Window functions make it a short query.

The examples run on the Sakila sample database (MySQL 8.0). Rentals in Sakila were recorded in week-long bursts, which makes the islands easy to see.

The Key Trick: Date Minus Row Number

Take the sorted list of distinct dates and number it. Inside a run of consecutive days, the date grows by one day and the row number grows by one, so date − row number stays constant. When a day is skipped, the date jumps ahead while the row number doesn't, and the difference changes. That difference is the island's key.

Let's see it on the rental days of customer 42 before July 13, 2005:

WITH customer_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
    WHERE customer_id = 42
      AND rental_date < '2005-07-13'
)
SELECT rental_day,
       ROW_NUMBER() OVER (ORDER BY rental_day) AS rn,
       DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
FROM customer_days
ORDER BY rental_day;

Result: 9 days forming 4 islands; for example, July 9 to 12 all have the key 2005-07-03.

The key itself means nothing; it only has to be equal within an island and different between islands. July 8 is missing, so July 7 forms its own island.

Islands: Periods with Rentals

Now group by the key and take the first and last day of each island:

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
    FROM rental_days
)
SELECT MIN(rental_day) AS island_start,
       MAX(rental_day) AS island_end,
       COUNT(*) AS days
FROM grouped
GROUP BY island_key
ORDER BY island_start;

Result: 6 islands: five periods of 8 consecutive days in 2005 and a single day, February 14, 2006.

DISTINCT in the first CTE is essential. If one day appeared twice, it would get two row numbers, and the difference would break the island. For integer sequences, use value - ROW_NUMBER() the same way.

Gaps: Periods without Rentals

For gaps, compare each day with the next one using LEAD(). If the next day is more than one day later, there is a hole between them:

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
with_next AS (
    SELECT rental_day,
           LEAD(rental_day) OVER (ORDER BY rental_day) AS next_day
    FROM rental_days
)
SELECT DATE_ADD(rental_day, INTERVAL 1 DAY) AS gap_start,
       DATE_SUB(next_day, INTERVAL 1 DAY) AS gap_end,
       DATEDIFF(next_day, rental_day) - 1 AS missing_days
FROM with_next
WHERE DATEDIFF(next_day, rental_day) > 1
ORDER BY gap_start;

Result: 5 gaps: four of 13 days between the 2005 periods and one of 174 days, from August 24, 2005 to February 13, 2006.

The same query with numbers finds missing ids, for example skipped invoice numbers: replace DATEDIFF(next_day, rental_day) with next_id - id.

Streaks per Customer

The interview version: "find each customer's longest streak of consecutive rental days". Add PARTITION BY customer_id so that every customer gets their own numbering, then aggregate twice: islands per customer, then the longest island:

WITH customer_days AS (
    SELECT DISTINCT customer_id, DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT customer_id, rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY rental_day) DAY) AS island_key
    FROM customer_days
),
streaks AS (
    SELECT customer_id, island_key, COUNT(*) AS streak_days
    FROM grouped
    GROUP BY customer_id, island_key
)
SELECT customer_id, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY customer_id
ORDER BY longest_streak DESC, customer_id
LIMIT 5;

Result: customers 42, 181, 290, 306 and 373 each rented on 8 consecutive days; in total, 10 customers have an 8-day streak.

The longest possible streak here is 8 days, because the store itself only had rentals in 8-day periods. With real activity data, the same query finds login streaks, consecutive working days, or periods of continuous sensor data.

Practice

• Gap & Islands problem

Key Takeaways:

• Islands are runs of consecutive values; gaps are the missing runs between them.
• In a run of consecutive dates, date - ROW_NUMBER() is constant: group by it to get the islands.
• Remove duplicates first with DISTINCT, or one day gets several row numbers and the islands break.
• LEAD() compares each value with the next one and finds the gaps.
• PARTITION BY gives each entity its own islands; a second aggregation finds the longest streak.

Frequently Asked Questions

What is the gaps and islands problem in SQL?

It is a class of tasks about sequences of values: islands are runs of consecutive values (days, numbers) without holes, gaps are the missing values between them. Examples: login streaks, continuous periods of activity, missing invoice numbers.

How do I find consecutive days in SQL?

Number the distinct dates with ROW_NUMBER() OVER (ORDER BY day) and subtract that number of days from the date. Consecutive days get the same result, so GROUP BY that value with MIN(day), MAX(day) and COUNT(*) gives each run.

How do I find missing dates or numbers in SQL?

Use LEAD(value) OVER (ORDER BY value) to get the next value for each row and keep the rows where the difference is greater than one step. The gap runs from the value plus one step to the next value minus one step. To list every missing date, join a generated calendar (Lesson 6.6).

How do I find the longest streak for each user?

Group consecutive days into islands per user with ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day), count the days in each island, and take MAX of these counts per user.