How do you solve gaps-and-islands problems in SQL?

Q: How do you solve gaps-and-islands problems in SQL?

**Target Audience:** Learners who know `ROW_NUMBER()` and `LAG()` and want to apply them to runs and holes in data, and candidates preparing for one of the classic hard SQL interview puzzles. **Difficulty:** Hard **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window functions are standard SQL; the date arithmetic is engine-specific and is shown for both. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Picture a wall calendar where you tick every day you went for a run. Stand back and two kinds of pattern jump out: blocks of ticks next to each other ("I ran four days in a row") and empty str

The problem

Question presented to candidate: "Two related problems. First, our invoice numbers should be continuous, so find every missing range. Second, from the logins table, find each user's streaks of consecutive login days, with start date, end date and length. How do you approach these gaps-and-islands problems in SQL?"

What a strong answer should cover:

The vocabulary: an island is a maximal run of consecutive values (or consecutive rows with the same state), a gap is the space between islands
Gaps: LEAD(x) OVER (ORDER BY x) puts the next value on each row; where next - x > 1 there is a gap from x + 1 to next - 1
Islands, method 1 (difference of sequences): value - ROW_NUMBER() is constant inside a run of consecutive values, so it works as a group key for GROUP BY
Islands, method 2 (change flag + running sum): LAG marks rows where the state changes, a running SUM of the flags numbers the runs; works for "same status" runs where values are not numbers
Pitfalls: duplicates break the ROW_NUMBER trick (de-duplicate first or use DENSE_RANK), PARTITION BY per entity, and the date arithmetic differs per engine

Clarifying questions expected:

Can the same value appear twice (two logins on one day), and does it count once?
What counts as "consecutive": calendar days, business days, or a tolerance such as "within 30 minutes"?
Is the expected output one row per island, or each row tagged with its island?

Code / implementation expected: Yes — a LEAD-based gap query and at least one island-grouping query with the ROW_NUMBER difference or the running-sum technique.

Schema used by the examples:

window-functions
gaps-and-islands

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Learners who know ROW_NUMBER() and LAG() and want to apply them to runs and holes in data, and candidates preparing for one of the classic hard SQL interview puzzles. Difficulty: Hard Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window functions are standard SQL; the date arithmetic is engine-specific and is shown for both.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Picture a wall calendar where you tick every day you went for a run. Stand back and two kind

The solution is hidden so you can try first

15 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLGaps: LEAD() finds every missing range of invoice numbers

SQLIslands: login streaks with the date minus ROW_NUMBER() trick

SQLIslands of the same status: LAG() change flag plus a running SUM()

PostgreSQL: longest streak per user with date arithmetic and DISTINCT ON

WITH days AS (
  SELECT DISTINCT user_id, login_date::date AS d
  FROM logins
), grouped AS (
  SELECT user_id, d,
         d - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d))::int AS grp
  FROM days
), streaks AS (
  SELECT user_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS days
  FROM grouped
  GROUP BY user_id, grp
)
SELECT DISTINCT ON (user_id) user_id, streak_start, streak_end, days
FROM streaks
ORDER BY user_id, days DESC, streak_start;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.