[180_MEDIUM] 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.

 

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.

문제 조건

연속적으로 세번 이상 나타나는 숫자를 찾으시오

 

쿼리 

# windowfunction 

select distinct l.num as ConsecutiveNums
from (select num,
            lead(num,1) over(order by id asc) as next,
            lead(num,2) over(order by id asc) as after_next
     from logs) l
where l.num = l.next
and l.num = l.after_next

# self-join
select distinct l1.num as ConsecutiveNums
from logs l1
    join logs l2 on l1.id+1 = l2.id
    join logs l3 on l2.id+1 = l3.id 
where l1.num = l2.num
and l1.num = l3.num