180. Consecutive Numbers
SQL 50 · Advanced Select and Joins (day 5). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One number that appears 3 times in a row.
2. Approach
LAG and LEAD over ORDER BY id in a CTE, keep num = prev = next, SELECT DISTINCT.
3. The trap
A number can qualify more than once; DISTINCT.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
WITH x AS (
SELECT num,
LAG(num) OVER (ORDER BY id) AS prev_num,
LEAD(num) OVER (ORDER BY id) AS next_num
FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM x
WHERE num = prev_num AND num = next_num;