Write a query to detect gaps in a sequential invoice_number column.

Answer

Compare each invoice_number to the one immediately before it (via a window function) - a gap exists wherever the difference is greater than 1.

Solution

SELECT invoice_number AS gap_starts_after,
       next_invoice - invoice_number - 1 AS missing_count
FROM (
  SELECT invoice_number,
         LEAD(invoice_number) OVER (ORDER BY invoice_number) AS next_invoice
  FROM Invoices
) x
WHERE next_invoice - invoice_number > 1;

Step by Step

1. LEAD(invoice_number) OVER (ORDER BY invoice_number) attaches the next invoice number (in sorted order) to every row.
2. If numbers are perfectly sequential, next_invoice - invoice_number always equals 1.
3. Any row where that difference is greater than 1 marks the point right before a gap - and the size of the gap is next_invoice - invoice_number - 1.

Alternative Method: ROW_NUMBER Comparison

SELECT invoice_number
FROM (
  SELECT invoice_number, ROW_NUMBER() OVER (ORDER BY invoice_number) AS rn
  FROM Invoices
) x
WHERE invoice_number - rn <> (SELECT MIN(invoice_number) FROM Invoices) - 1;

This classic "islands and gaps" technique compares each invoice number to its own row position: if numbers were perfectly sequential starting from the minimum, invoice_number - rn would be constant across all rows. Any row where it deviates marks a gap - though the LEAD() approach above is generally more direct and easier to explain.

Key Exam Traps

• The last row always has next_invoice = NULL from LEAD(), since there's nothing after it - the WHERE next_invoice - invoice_number > 1 condition naturally excludes it (a comparison against NULL is never true), which is actually the correct behavior here.
• Duplicate invoice numbers would make next_invoice - invoice_number = 0, which this query doesn't flag as a "gap" - if duplicates are also a data quality concern, that's a separate check.
• Assuming invoices start at 1: The gap-detection logic works regardless of the starting number, but if the requirement is specifically "gaps from the sequence's true starting point," the first invoice's own starting value should also be validated against an expected baseline.