[LeetCode MySQL] 180. Consecutive Numbers

180. Consecutive Numbers

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:

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

Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 is the only number that appears consecutively for at least three times.

 

Solution 1 - JOIN

select distinct l1.num ConsecutiveNums
from logs l1
left join logs l2 on l1.id + 1 = l2.id
left join logs l3 on l2.id + 1 = l3.id
where l1.num = l2.num and l2.num = l3.num

Solution 2 - LAG & LEAD

select distinct n1 ConsecutiveNums
from (select lag(num, 1) over (order by id) n1,
             num n2,
             lead(num, 1) over (order by id) n3
      from logs) result
where n1 = n2 and n2 = n3

---

Reference: 180. Consecutive Numbers