Gaps and Islands Problem in SQL: Complete Guide | sqlinterview

What Is the Gaps and Islands Problem?

The "gaps and islands" problem is one of the most recognizable pattern-based SQL interview questions, and it shows up constantly in real data work too — detecting missing dates in a reporting table, finding consecutive login streaks, or identifying continuous blocks of available inventory. The name describes exactly what you're looking for in a sequence of values (usually dates, IDs, or numbers):

Islands — consecutive runs of values with no break (e.g. a user logged in 5 days in a row)
Gaps — the missing values between those runs (e.g. the days the user did not log in)

Once you recognize a question as a gaps-and-islands problem, a small set of reusable techniques solves almost every variant — which is exactly why interviewers like asking it.

The Sample Data

-- login_days: dates a user was active
| user_id | login_date  |
|---------|-------------|
| 1       | 2026-01-01  |
| 1       | 2026-01-02  |
| 1       | 2026-01-03  |
| 1       | 2026-01-06  |
| 1       | 2026-01-07  |
| 1       | 2026-01-10  |

User 1 has three separate "islands" of activity: Jan 1-3, Jan 6-7, and Jan 10 (a single-day island). The gaps are Jan 4-5 and Jan 8-9.

Technique 1: The Row Number Difference Trick

This is the classic approach and the one interviewers most want to see. The idea: if you subtract a sequential row number from a sequential date, that difference stays constant for every row inside the same consecutive run, and changes as soon as there's a break.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS island_start,
  MAX(login_date) AS island_end,
  COUNT(*) AS streak_length
FROM numbered
GROUP BY user_id, grp
ORDER BY island_start;

-- Result:
-- | user_id | island_start | island_end | streak_length |
-- |---------|--------------|------------|---------------|
-- | 1       | 2026-01-01   | 2026-01-03 | 3             |
-- | 1       | 2026-01-06   | 2026-01-07 | 2             |
-- | 1       | 2026-01-10   | 2026-01-10 | 1             |

Why it works: for a perfectly consecutive run, date - row_number is a fixed "anchor" value, because both the date and the row number increase by exactly 1 each step. The moment there's a gap in the dates, the row number keeps incrementing by 1 but the date jumps by more than 1 — so the difference changes, which naturally partitions the data into groups.

Technique 2: The LAG-Based "New Island Flag" Method

A more explicit, arguably more readable approach: compare each row to the previous row using LAG(), flag where a new island starts, then use a running sum of those flags as the group ID.

WITH flagged AS (
  SELECT
    user_id,
    login_date,
    CASE
      WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) = 1
      THEN 0 ELSE 1
    END AS is_new_island
  FROM login_days
),
grouped AS (
  SELECT
    user_id,
    login_date,
    SUM(is_new_island) OVER (PARTITION BY user_id ORDER BY login_date) AS island_id
  FROM flagged
)
SELECT user_id, MIN(login_date) AS island_start, MAX(login_date) AS island_end, COUNT(*) AS streak_length
FROM grouped
GROUP BY user_id, island_id
ORDER BY island_start;

This version generalizes more easily to non-date sequences (like numeric IDs with arbitrary gaps) and to conditions other than "exactly 1 apart" — for example, grouping consecutive rows where a status value stays the same, which is a very common real-world variant (see below).

Finding the Gaps Directly

Sometimes the question asks for the missing values themselves, not the islands around them.

WITH bounds AS (
  SELECT MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM login_days WHERE user_id = 1
),
all_dates AS (
  SELECT generate_series(start_date, end_date, interval '1 day')::date AS d
  FROM bounds
)
SELECT d AS missing_date
FROM all_dates
WHERE d NOT IN (SELECT login_date FROM login_days WHERE user_id = 1)
ORDER BY d;

-- Result: 2026-01-04, 2026-01-05, 2026-01-08, 2026-01-09

generate_series is PostgreSQL-specific — see the database compatibility notes below for MySQL and SQL Server equivalents.

Variant: Grouping Consecutive Rows With the Same Status

A very common real-world version of this pattern: given a log of status changes, find each continuous block of time a record spent in the same status.

-- server_status: status readings over time
| server_id | checked_at          | status  |
|-----------|----------------------|---------|
| 1         | 2026-01-01 00:00:00  | up      |
| 1         | 2026-01-01 01:00:00  | up      |
| 1         | 2026-01-01 02:00:00  | down    |
| 1         | 2026-01-01 03:00:00  | down    |
| 1         | 2026-01-01 04:00:00  | up      |

WITH flagged AS (
  SELECT *,
    CASE WHEN status = LAG(status) OVER (PARTITION BY server_id ORDER BY checked_at)
         THEN 0 ELSE 1 END AS is_change
  FROM server_status
),
grouped AS (
  SELECT *, SUM(is_change) OVER (PARTITION BY server_id ORDER BY checked_at) AS grp
  FROM flagged
)
SELECT server_id, status, MIN(checked_at) AS period_start, MAX(checked_at) AS period_end
FROM grouped
GROUP BY server_id, status, grp
ORDER BY period_start;

This is the same "flag a change, then running-sum it into groups" logic as Technique 2 — the row-number-difference trick (Technique 1) doesn't directly apply here since there's no simple arithmetic sequence to subtract, which is a good interview signal: know both techniques so you can reach for whichever fits the data.

Database Compatibility Notes

PostgreSQL — has native generate_series(), making the "find all missing values" version straightforward, as shown above.
MySQL (8.0+) — no generate_series; generate a date spine using a recursive CTE instead: WITH RECURSIVE seq AS (SELECT MIN(login_date) d FROM login_days UNION ALL SELECT d + INTERVAL 1 DAY FROM seq WHERE d < (SELECT MAX(login_date) FROM login_days)) SELECT d FROM seq.
SQL Server — no generate_series either (prior to 2022, which added it); use a numbers table or recursive CTE with DATEADD.
The row-number-difference and LAG-based grouping techniques (Technique 1 and 2) are standard ANSI SQL and work identically across PostgreSQL, MySQL 8+, and SQL Server.

Common Mistakes

Forgetting PARTITION BY when the data has multiple entities (e.g. multiple users) — without it, streaks incorrectly bleed across different users.
Assuming dates are contiguous with no duplicates. Real data often has duplicate rows per day; dedupe with DISTINCT or a pre-aggregation step before applying either technique.
Using the row-number trick on non-arithmetic sequences (like the status-change example) — it only works when consecutive values increase by a fixed step. For anything else, use the LAG/flag/running-sum method.
Off-by-one errors when reporting streak length — always sanity-check with MAX(date) - MIN(date) + 1 against COUNT(*) for a given island; they should match if there are no internal gaps.

Common Interview Questions on This Pattern

How would you find a user's longest login streak? Apply Technique 1 or 2 to get islands per user, then take the island with the maximum streak_length per user.
How would you find users who logged in on every day of a given month? Count distinct login dates per user for that month and compare against the number of days in the month.
Why does subtracting ROW_NUMBER from a date produce groups? Because both increase by exactly 1 per consecutive row; any break in the date sequence breaks that 1-to-1 relationship, changing the difference and starting a new group.
How would you adapt this for numeric IDs instead of dates? Identical logic — id - ROW_NUMBER() OVER (ORDER BY id) groups consecutive integers exactly the same way.

Frequently Asked Questions

Does the gaps-and-islands pattern only apply to dates?

No — it applies to any ordered sequence: integer IDs, timestamps, even ordered categorical sequences (like the status-change example). The core idea is always "detect a break in an expected progression."

Which technique is faster — row-number difference or LAG/running-sum?

Both are O(n log n) due to the required sort for the window functions, and in practice perform similarly on modern query engines. Pick whichever is clearer for the specific case: row-number difference is more concise for simple arithmetic sequences; LAG/running-sum generalizes better to arbitrary "same value" or custom-condition groupings.

Practice This Pattern

Gaps and islands questions reward pattern recognition more than memorization — once you've solved a handful of variants, you'll spot the shape immediately in an interview. Try it yourself with our SQL practice questions, or work through a full business scenario in our case studies. For the window function techniques used throughout this guide, see our query optimization guide.