In this Leetcode Consecutive Numbers problem solution, we need to write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order.

Leetcode Consecutive Numbers problem solution

Problem solution.

select distinct num as ConsecutiveNums
(select num, id - cast(row_number() over (partition by num order by id) as SIGNED) rdiff
group by num,rdiff
having count(num)>=3

Problem solution in MYSQL.

with df as (
    select id
        , 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 df
where num = prev_num
    and num = next_num

Problem solution in Oracle.

select distinct num ConsecutiveNums from
(select num,lead(num,1) over(order by ID) next_num,lead(num,2) over(order by ID) next_next_num
from logs)
where num = next_num and next_num = next_next_num;