Consecutive Numbers
Problem
Find all numbers that appear at least three times consecutively in the Logs table ordered by id.
SQL: SELECT DISTINCT num AS ConsecutiveNums FROM (SELECT num, LAG(num,1) OVER (ORDER BY id) p1, LAG(num,2) OVER (ORDER BY id) p2 FROM Logs) t WHERE num = p1 AND num = p2;
Logs.num order by id = [1, 1, 1, 2, 1, 2, 2][1]SELECT DISTINCT num AS ConsecutiveNums
FROM (SELECT num,
LAG(num, 1) OVER (ORDER BY id) AS p1,
LAG(num, 2) OVER (ORDER BY id) AS p2 FROM Logs) t
WHERE num = p1 AND num = p2;Explanation
We want numbers that show up in three rows in a row when the table is ordered by id. The neat trick is to line up each row next to its two predecessors and check if all three are equal.
The window function LAG does exactly that. LAG(num, 1) OVER (ORDER BY id) fetches the num from one row back, and LAG(num, 2) fetches the value from two rows back. We call these p1 and p2.
In the outer query we keep only rows where num = p1 AND num = p2 — meaning the current value equals both of the two values just before it. That is the definition of three consecutive equal numbers. We wrap it in SELECT DISTINCT so a value appearing in many long runs is reported just once.
Example: for [1, 1, 1, 2, 1, 2, 2], the third 1 has p1 = 1 and p2 = 1, so it matches. No other value ever has two equal predecessors, so the answer is just [1].
Because LAG only peeks at fixed offsets, the database can compute this in a single ordered pass.