Leetcode: Consecutive Numbers

Consecutive Numbers



Similar Problems:


Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.


## Blog link: https://code.dennyzhang.com/consecutive-numbers
SET @group_number=0;
SET @id_number1=0;
SET @id_number2=0;

select distinct Num as ConsecutiveNums
from
    (select group_id, Num, count(1) as item_count
    from
        (select Num, new_group, (if(new_group=1, @group_number:=@group_number+1, @group_number)) AS group_id
        from 
            (select t1.Id, t1.Num, if(t1.Num=t2.Num, 0, 1) as new_group
            from
            (select *, (@id_number1:=@id_number1+1) as id_number
            from Logs) as t1 left join 
            (select *, (@id_number2:=@id_number2+1) as id_number
            from Logs) as t2
            on t1.id_number = t2.id_number + 1) as t3
        order by t3.Id asc) as t4
    group by group_id, Num
    having count(1)>=3) as t5
group by Num
linkedin
github
slack

Share It, If You Like It.

Leave a Reply

Your email address will not be published.