0180. Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers

Description

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.

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

Return the result table in any order.

The query result format is in the following example.

Example 1:

**Input:** 
Logs table:
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
**Output:** 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
**Explanation:** 1 is the only number that appears consecutively for at least three times.

ac

# Write your MySQL query statement below
select distinct Num ConsecutiveNums from (
select
Num,
case
    when @prev = Num then @cnt := @cnt + 1
    when (@prev := Num) is not null then @cnt := 1
end cnt
from Logs l, (Select @cnt := 1, @prev := NULL) c
) a
where a.cnt >= 3

Last updated