Given a table of integer ids, write a single SQL query that returns the first and last id of every missing range (gap) in the sequence.
Input
numbers(sequence_id INTEGER) — distinct integer ids, not necessarily contiguous.
Task
A gap is a maximal run of one or more consecutive integers that are absent from the table but lie strictly between two present ids. For each gap, return its first missing id and its last missing id. Do not consider any range before the minimum present id or after the maximum present id.
Output
One row per gap, columns in this exact order:
gap_start (INTEGER) — first missing id of the rangegap_end (INTEGER) — last missing id of the range
Order by gap_start ascending. If there are no gaps, return no rows.
Evaluation
Your query is executed against several hidden datasets in a DuckDB sandbox. Output is compared row-for-row against the reference result.
Worked example
Present ids: 1, 2, 4, 6. The missing ids are 3 (between 2 and 4) and 5 (between 4 and 6).
Expected output:
| gap_start | gap_end |
|-----------|---------|
| 3 | 3 |
| 5 | 5 |