코딩테스트 SQL 코드카타 (Consecutive Numbers)

문제

Consecutive Numbers - LeetCode

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. 첫번째 숫자와 두번째, 세번째 구분하기
2. 만약 같다면 반환

 

---

코드

select distinct num as ConsecutiveNums
from 
(select num, lead(num, 1) over (order by id)as next, lead(num,2) over (order by id) as next2
 from Logs) as nums
 where num = next and next = next2

 

---

링크

LeetCode/0180-consecutive-numbers at main · K-MarkLee/LeetCode

LeetCode/0180-consecutive-numbers at main · K-MarkLee/LeetCode