SQL interview question: Find Missing IDs in a Sequence
Detect Missing Order IDs
The operations team suspects some orders were lost in a system migration. They need to find gaps in the order ID sequence.
Find all missing order_id values in the sequence. Orders should be numbered sequentially with no gaps.
Return missing_order_id. Use a recursive CTE to generate the expected sequence.
Skills: sequence generation and LEFT JOIN to find gaps
Tables
missing_orders 7 rows
- order_id integer PK
- customer_name text
- order_date text
| order_id | customer_name | order_date |
|---|---|---|
| 1 | Alice | 2025-01-01 |
| 2 | Bob | 2025-01-02 |
| 4 | Carol | 2025-01-04 |