How do you report the missing ranges in a sequence of invoice numbers using LEAD()?

## The query ```sql WITH bounds AS ( SELECT invoice_no, LEAD(invoice_no) OVER (ORDER BY invoice_no) AS next_no FROM invoices ) SELECT invoice_no + 1 AS gap_start, next_no - 1 AS gap_end, next_no - invoice_no - 1 AS missing_count FROM bounds WHERE next_no > invoice_no + 1 ORDER BY gap_start; ``` For invoice numbers 1, 2, 5, 6, 10 the pairs are (1,2), (2,5), (5,6), (6,10), (10,NULL). Only two rows satisfy the predicate: `2 → 5` yields the gap 3–4, and `6 → 10` yields 7–9. The last row is filtered out automatically because `LEAD()` returns NULL past the end of the partition and `NULL > 11` is unknown, which `WHERE` treats as not true. That is a genuinely useful consequence of three-valued logic — you do not need a special case for the final row. ## Why this is the mirror image of islands An island query needs a synthetic run id because the rows of a run must be collapsed together. A gap query needs no such thing: every gap is fully described by the pair of rows that bracket it, and `LEAD()` puts that pair on one row. No `GROUP BY`, no running sum — just a look-ahead and a filter. If an interviewer asks for both islands and gaps, the islands need the row-number trick and the gaps need only this. ## Dates instead of integers ```sql WITH bounds AS ( SELECT reading_date, LEAD(reading_date) OVER (ORDER BY reading_date) AS next_date FROM readings ) SELECT reading_date + INTERVAL '1' DAY AS gap_start, next_date - INTERVAL '1' DAY AS gap_end FROM bounds WHERE next_date > reading_date + INTERVAL '1' DAY; ``` The logic is identical; only the step changes. Date arithmetic is the least portable part, so check your engine's spelling. Note also that a gap here means *calendar* days: if weekends or holidays are legitimately absent, the query reports them as gaps unless you restrict the comparison to a calendar of expected days. ## What this technique cannot see **The edges.** Values missing before the smallest stored value or after the largest leave no bracketing pair, so they cannot be detected. If the question is "which numbers between 1 and 1000 were never issued", you must supply 1 and 1000 explicitly — for instance by unioning sentinel rows `0` and `1001` into the input so the first and last real values acquire neighbours, or by generating the intended range and anti-joining it against the table. **Individual values.** The output is ranges, which is usually what you want for a long sequence. Turning `3–4` into two rows `3` and `4` means producing values that are in no table, which needs a numbers table or a generated series; the portable route is a recursive `WITH` that counts from `gap_start` to `gap_end`, and several engines offer a built-in generator instead. **Duplicates and NULLs.** Duplicate values make `LEAD()` return the same value as the current row; the predicate is then false and the pair is harmlessly skipped, so duplicates do not create false gaps — but they do inflate the row count, and deduplicating first keeps the intent clear. NULLs in the ordering column are not values in the sequence at all; exclude them in `WHERE` rather than letting the engine's NULL ordering decide where they land. **Per-entity sequences.** If each branch or each year has its own numbering, add `PARTITION BY branch_id` to the `OVER` clause and carry `branch_id` into the output; otherwise the query reports a fake gap every time it crosses from one branch's range to the next. ## Interpreting the result A gap in an id sequence is not automatically evidence of missing business data. Identity columns and sequences are allowed to leave holes — rolled-back transactions, cached blocks and deleted rows all consume numbers permanently — so a gap report over a surrogate key usually says nothing about lost invoices. The report is meaningful when the numbering is a business-controlled sequence with a requirement to be contiguous, or when the column is a natural sequence such as a reading number or a page number. Saying this in an interview shows you understand what the query is evidence of, which is the difference between running the query and answering the question.