Question

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.

SQL Schema

Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')

My Interesting Code

select distinct Num as ConsecutiveNums
from (
    select lag(Num) over() as preNum,
        Num,
        lead(Num) over() as nextNum
    from Logs
) temp
where preNum = Num and Num = nextNum

My Perspective

For this question, if you want to find a number that appears consecutively for at least three times, you can find each number’s previous number and next number, using “lag over” and “lead over”. Then put them into one line.

According to the requirement, if the new table - “temp” - has at least one line that all elements are equal, it can address the problem. More importantly, you may have the same result, and do not forget to use “distinct” to address the problem.

Also, there are three useful links, and I think they can help you. (chinese version)