Consecutive Numbers - LeetCode

Consecutive Numbers 문제

출처 : LeetCode 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 starting from 1.

Q.

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.

---

질문

연속으로 같은 값이 3번 이상 나온 num을 찾아서 출력하라!

내 답안 📕

WITH num_lead_lag AS (
    SELECT *
         , LEAD(num, 1) OVER(ORDER BY id) AS lead_num
         , LAG(num, 1) OVER(ORDER BY id) AS lag_num 
    FROM Logs
)

SELECT DISTINCT num AS ConsecutiveNums
FROM num_lead_lag
WHERE num = lead_num
  AND num = lag_num

만약 num이 3개 연속으로 있다면,
이전 행, 현재 행, 다음 행이 모두 같은 값이 되야한다.