LeetCode180. Consecutive Numbers

Table: Logs

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

 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.

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.

 SELECT DISTINCT num as ConsecutiveNums

FROM (

     SELECT id, num, 

    LAG(num) OVER(ORDER BY id) as prev_num,

    LEAD(num) OVER(ORDER BY id) as next_num

    FROM Logs

 ) l

 WHERE l.num = l.prev_num

 AND l.prev_num = l.next_num

 AND l.num = l.next_num;

Ref:

https://dotblogs.com.tw/supershowwei/2019/05/13/155428