[SQL] 리트코드 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.

 

https://leetcode.com/problems/consecutive-numbers/description/

LeetCode - The World's Leading Online Programming Learning Platform

예시

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 l.num AS ConsecutiveNums
FROM logs AS l
    INNER JOIN logs AS l_next ON l.id + 1 = l_next.id
    INNER JOIN logs AS l_next2 ON l.id + 2 = l_next2.id
WHERE l.num = l_next.num AND l.num = l_next2.num

SELECT DISTINCT l.num AS ConsecutiveNums
FROM (
    SELECT num
        , LEAD(num, 1) OVER (ORDER BY Id) AS next
        , LEAD(num, 2) OVER (ORDER BY Id) AS afternext
    FROM logs
) l
WHERE l.num = l.next AND l.num = l.afternext

---

SQL을 독학하시는 분들에게 도움이 되길 바라며,

혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.