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;
0 Comments