Finding Gaps in a Sequence
Detecting missing values and the start/end of each gap.
Finding Gaps in a Sequence is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Now Hunt The Gaps
So far we have grouped rows into islands. The mirror-image interview question is: which values are missing? Interviewers phrase it as "find the gaps in this ID sequence," "which invoice numbers were skipped," or "on which days was there no activity."
Gaps are the empty spaces between islands. The key realization is that you usually do not need to list every single missing value; you need to report the start and end of each gap range, which is far more compact and is what interviewers expect.
The Sample Gap Dataset
Reuse the present values 1, 2, 3, 7, 8, 10 from a table seq(n). The gaps to report are:
- From 4 to 6 (after the first island, before 7)
- From 9 to 9 (between 8 and 10)
Notice we describe a gap as a range: gap_start = last present value + 1, gap_end = next present value - 1. That compact form is the goal of the core technique below.
CREATE TABLE seq (n INT);
INSERT INTO seq VALUES (1),(2),(3),(7),(8),(10);The LEAD Approach To Gaps
The cleanest gap detector compares each row to the next row using LEAD. If the next value is more than 1 greater than the current value, there is a gap between them.
For each such row, the gap starts at n + 1 and ends at next_n - 1. Look at the raw LEAD output first:
SELECT
n,
LEAD(n) OVER (ORDER BY n) AS next_n
FROM seq
ORDER BY n;Reporting Gap Ranges
Wrap the LEAD result in a CTE and keep only rows where the jump to the next value exceeds 1. Those rows mark gaps:
This returns gap 4-6 and gap 9-9 exactly. The next_n - n - 1 expression also gives the count of missing values in each gap, a frequent follow-up.
WITH stepped AS (
SELECT n, LEAD(n) OVER (ORDER BY n) AS next_n
FROM seq
)
SELECT
n + 1 AS gap_start,
next_n - 1 AS gap_end,
next_n - n - 1 AS missing_count
FROM stepped
WHERE next_n - n > 1
ORDER BY gap_start;The Symmetric LAG Variant
You can detect the same gaps looking backward with LAG instead. A gap exists before the current row when the previous value is more than 1 less than it.
This is fully equivalent; choose whichever reads more naturally for the question. Some interviewers prefer LEAD because the gap is described relative to the row that precedes it, matching how people speak.
WITH stepped AS (
SELECT n, LAG(n) OVER (ORDER BY n) AS prev_n
FROM seq
)
SELECT prev_n + 1 AS gap_start,
n - 1 AS gap_end
FROM stepped
WHERE n - prev_n > 1
ORDER BY gap_start;Listing Every Missing Value
Sometimes the interviewer truly wants the full list of missing numbers, not just ranges. The robust approach is to generate the complete expected sequence and anti-join it against what exists. In Postgres, generate_series builds the full range:
Every integer in the expected span that is not present in seq is a missing value. This also handles gaps at the very edges if you know the intended min and max.
SELECT g.n AS missing_value
FROM generate_series(
(SELECT MIN(n) FROM seq),
(SELECT MAX(n) FROM seq)
) AS g(n)
LEFT JOIN seq s ON s.n = g.n
WHERE s.n IS NULL
ORDER BY g.n;Cross-Dialect Series Generation
Not every engine has generate_series. Know the alternatives:
- Postgres:
generate_series(1, 100). - SQL Server: a recursive CTE or a numbers/tally table.
- MySQL 8: a recursive CTE that counts up to the max.
A recursive CTE is the portable fallback. It produces the same expected sequence to anti-join against.
WITH RECURSIVE nums AS (
SELECT (SELECT MIN(n) FROM seq) AS n
UNION ALL
SELECT n + 1 FROM nums
WHERE n + 1 <= (SELECT MAX(n) FROM seq)
)
SELECT nums.n AS missing_value
FROM nums
LEFT JOIN seq s ON s.n = nums.n
WHERE s.n IS NULL;Gaps In Calendar Dates
For missing dates, generate a full calendar with a daily step and anti-join. This is the standard "which days had no orders" query:
Combine it with the range technique by applying LEAD over the actual dates to report missing date spans instead of individual days, using + INTERVAL '1 day' for the boundaries.
SELECT d::date AS missing_day
FROM generate_series(
DATE '2026-01-01', DATE '2026-01-31',
INTERVAL '1 day') AS d
LEFT JOIN daily_logins l ON l.login_date = d::date
WHERE l.login_date IS NULL
ORDER BY missing_day;Edge Gaps Beyond The Data
A subtle trap: LEAD/LAG only finds gaps between present values. If a number is missing before the minimum or after the maximum present value, the window approach cannot see it because there is no neighboring row.
If the interviewer defines an expected full range (say IDs 1 through 100) and your data starts at 5, you must use the generate-series anti-join bounded by the declared range, not the data's own min and max. Always clarify whether the expected boundaries are fixed.
SELECT g.n AS missing_value
FROM generate_series(1, 100) AS g(n)
LEFT JOIN seq s ON s.n = g.n
WHERE s.n IS NULL;Per-Group Gap Detection
For per-user gaps, partition the LEAD/LAG by the group column so a gap is never reported across two different users' streams:
Each user's missing ranges are computed independently. As with islands, forgetting to partition silently merges users and produces phantom gaps spanning unrelated rows.
WITH stepped AS (
SELECT user_id, n,
LEAD(n) OVER (PARTITION BY user_id ORDER BY n) AS next_n
FROM seq_per_user
)
SELECT user_id, n + 1 AS gap_start, next_n - 1 AS gap_end
FROM stepped
WHERE next_n - n > 1
ORDER BY user_id, gap_start;Choosing The Right Gap Method
Decision guide for the interview:
- Want compact ranges and only internal gaps? Use
LEAD/LAG, filtering where the step exceeds 1. - Want every individual missing value or gaps beyond the data's edges? Use generate-series anti-join against the declared full range.
Mentioning both options and when each applies signals depth. The LEAD method is cheaper; the series method is more complete.
Quick Check
Pin down the edge-case trap.
Recap: Finding Gaps
Gap detection, locked in:
- Report gaps as ranges: gap_start = value + 1, gap_end = next_value - 1.
LEAD(or symmetricLAG) filtered where the step exceeds 1 finds internal gaps cheaply.- generate-series anti-join lists every missing value and catches edge gaps against a declared range.
- Recursive CTEs generate the series where
generate_seriesis absent. - Partition by the group column for per-user gaps.
- Always clarify the expected boundaries.
Finally we tackle the richest variant: islands defined by date and status changes.
Frequently asked questions
Is the “Finding Gaps in a Sequence” lesson free?
Yes — the full text of “Finding Gaps in a Sequence” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Finding Gaps in a Sequence”?
Detecting missing values and the start/end of each gap. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Finding Gaps in a Sequence” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.