LeetCode로 공부하기180. Consecutive Numbers

Table: Logs

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

 

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

The 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.

 

문제:  적어도 세 번 이상 연속적으로 나타나는 모든 숫자를 찾으세요.

 

문제풀이1. join을 활용

select distinct l.num as ConsecutiveNums
from logs as l
    join logs as l_next on l.id + 1 = l_next.id
    join logs as l_next2 on l.id + 2 = l_next2.id
where l.num = l_next.num and l.num = l_next2.num

 

문제풀이2 윈도우 함수 활용

select distinct l.num as ConsecutiveNums
from (
    select id, num, 
       lead(num) over(order by id) as lead1,
       lead(num, 2) over(order by id) as lead2
    from logs
) as l
where l.num = l.lead1 and l.num = l.lead2