Find Missing IDs in a Sequence (Recursive CTE)
Preview mode. Log in to edit, run, submit, and save progress.
Medium
Find Missing IDs in a Sequence (Recursive CTE)
You are given a Tickets table containing a set of integer IDs. The IDs are supposed to be sequential from the minimum to the maximum, but some are missing. Write a SQL query using a recursive CTE to find all missing IDs between the minimum and maximum ID in the table. Return a single column named missing_id, ordered ascending. If there are no gaps, return an empty result. Table: Tickets
| Column Name | Type | Description |
|---|---|---|
| id | INT | Ticket ID (not sequential) |
Examples
Example 1
The full range is 1 to 15. IDs 4, 7, 8, 13, 14 are absent from the table.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
query.sql