Find Numbers Appearing at Least Three Times Consecutively in SQL

Question

Given a `logs` table with `id` (autoincrementing) and `num`, write a SQL query to find all numbers that appear at least three times consecutively.

Accepted Answer

Optimal Solution:
WITH lagged_logs AS (
  SELECT 
    num,
    LEAD(num, 1) OVER (ORDER BY id) AS next_num,
    LEAD(num, 2) OVER (ORDER BY id) AS next_next_num
  FROM logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM lagged_logs
WHERE num = next_num 
  AND num = next_next_num;

Technical Explanation:
We use `LEAD(num, 1)` and `LEAD(num, 2)` ordered by `id` to inspect the subsequent two rows in the same record. When `num = next_num AND num = next_next_num`, it appeared 3 consecutive times.