180. Consecutive Numbers

Find every number that appears at least three times in a row, ordered by id, as ConsecutiveNums.

lag reads the two previous rows in id order. Comparing ids as well guards against gaps in the ids, which the problem's data does not have but real data might.

Lesson: window functions.

Solutions

SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT num,
           lag(num, 1) OVER (ORDER BY id) AS prev1,
           lag(num, 2) OVER (ORDER BY id) AS prev2
    FROM Logs
) t
WHERE num = prev1 AND num = prev2;