Find maximal islands of consecutive integers
Return start_value, end_value, and length for each maximal consecutive integer island. Ignore NULLs and deduplicate values before forming islands. Return only the requested columns and follow the specified ordering. This question probes after deduplication, value minus row number stays constant within each consecutive run.
Input
Setup script defining numbers(value INT).
Output
start_value, end_value, length; ascending start_value.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE numbers(value INT); INSERT INTO numbers VALUES (1),(2),(2),(3),(7),(8),(10),(NULL);
Expected result
start_value | end_value | length 1 | 3 | 3 7 | 8 | 2 10 | 10 | 1
Another dataset (1)
CREATE TABLE numbers(value INT); INSERT INTO numbers VALUES (5),(5),(NULL);
Expected result
start_value | end_value | length 5 | 5 | 1
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Remove duplicate numbers first.
- Subtract ROW_NUMBER from value and group by that difference.
Target complexity: time O(n log n) · space O(n)