Values Appearing Three or More Times Consecutively

Mediumsql

You are given a table named logs that records readings from a sensor. Each row has an id that increases by 1 with no gaps, and a num value observed at that position. A value is considered "persistent" if it appears on three or more consecutive rows (consecutive meaning consecutive id values).

Write ONE query that returns the distinct num values that appear three or more times in a row. The result should have a single column named num. Order the output by num ascending.

Example:

Input:

logs table:
id | num
1  | 1
2  | 1
3  | 1
4  | 2
5  | 1
6  | 2
7  | 2
8  | 2
9  | 2

Output:

num
1
2

Reasoning:

The value 1 appears on ids 1,2,3 (three consecutive rows), so it qualifies. The value 2 appears on ids 6,7,8,9 (four consecutive rows), so it also qualifies. The lone 1 at id 5 and the lone 2 at id 4 are not part of any run of length three, so they are ignored. Both qualifying values are returned once each, ordered by num.

Learn About topic

Contributors:

Deep-ML

Contribute