Gap Detection in Sequences — Solution & ExplanationProblems / Gap Detection in Sequences / EditorialApproachUse the LEAD window function to pair each ID with the next one in sequence:

LEAD(id) OVER (ORDER BY id) gives the next ID for each row.

Filter where next_id - id > 1 — these are the rows with gaps.

The gap range is [id + 1, next_id - 1].

Why LEAD?LEAD looks ahead one row in the ordered partition. By comparing each ID to its successor, we can detect discontinuities in a single pass.Alternative Approaches

Self-join: Join the table to itself on a.id + 1 = b.id and find where the join misses. Less efficient.

Generate series: Generate all IDs in the range and find which are missing. Works but wasteful for sparse data.

Complexity

Time: O(n log n) for the sort, O(n) for the scan

Space: O(n) for the window buffer

Hints

LEAD(id) OVER (ORDER BY id) gives you the next ID in sequence for each row.

A gap exists wherever the next ID minus the current ID is greater than 1.

The gap range is from (current_id + 1) to (next_id - 1).

Solve Gap Detection in Sequences yourself →