Find values repeated in three consecutive log rows
Return each non-NULL value occurring in at least three consecutive rows when logs are ordered by unique ID. Consecutive means adjacent rows, not necessarily consecutive numeric IDs. Return only the requested columns and follow the specified ordering. This question probes two lag offsets test adjacent row equality, and distinct collapses overlapping triples; null equality does not qualify.
Input
Setup script defining logs(id INT, value INT).
Output
value; distinct ascending values.
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 logs(id INT, value INT); INSERT INTO logs VALUES (1,7),(3,7),(4,7),(5,2),(6,2),(7,2),(9,7);
Expected result
value 2 7
Another dataset (1)
CREATE TABLE logs(id INT, value INT); INSERT INTO logs VALUES (1,7),(2,NULL),(3,7),(4,7),(5,8),(6,8),(7,8);
Expected result
value 8
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Use LAG at offsets one and two.
- Deduplicate values after finding triple endings.
Target complexity: time O(n log n) · space O(n)