Find a Gap in Sequence | Dataford Interview Questions

Write a SQL query to find a gap in a sequence of numbers.

Use sequence_ranges to define inclusive numeric ranges and sequence_values to store observed numbers. Return every number that is absent from its defined range, including ranges with no observed values.

Output

One row per missing number
Columns: range_id, missing_number
Include missing numbers only, ordered by range_id and then missing_number ascending

Schema

sequence_ranges

Column	Type	Description
range_idPK	INT	Identifier for a numeric sequence range
start_number	INT	Inclusive lower bound of the range
end_number	INT	Inclusive upper bound of the range

sequence_values

Column	Type	Description
value_idPK	INT	Identifier for an observed sequence value
range_id	INT	Referenced sequence range
sequence_number	INT	Observed integer in the sequence

Your question is Find a Gap in Sequence. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem