Leetcode Consecutive Numbers problem solution

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.

Problem solution.

select distinct num as ConsecutiveNums
from
(select num, id - cast(row_number() over (partition by num order by id) as SIGNED) rdiff
from
logs)t
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;

coding problems