LeetcodeSQL: 180. Consecutive Numbers [implementing Lag and Lead]

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.

Solution:

select distinct l0.num as ConsecutiveNums
from Logs l0 
inner join Logs l1 on l1.id - 1 = l0.id 
inner join Logs l2 on l1.id + 1 = l2.id
where l0.num = l1.num and l1.num = l2.num

Post Reference: Vikram Aristocratic Elfin Share