Consecutive Numbers - 연속된 숫자 찾기

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

Consecutive Numbers - LeetCode

Input : Table Logs

Output : 

처음 쿼리 :  

SELECT L1.Num AS ConsecutiveNums
FROM Logs AS L1
INNER JOIN Logs AS L2 ON L1.Id+1 = L2.id
INNER JOIN Logs AS L3 ON L1.Id + 2 = L3.id
WHERE L1.Num = L2.Num AND L2.Num = L3.Num

DISTINCT 함수를 생략하는 바람에, 3이 4개 연속일 경우 output에 [[3], [3]]이 되어서 Accepted가 되지 않았다.

Submission 결과

---

수정 쿼리:

SELECT DISTINCT L.Num AS ConsecutiveNums
FROM Logs AS L
INNER JOIN (SELECT L1.id AS Id1, L1.Num AS Num1, L2.id AS Id2, L2.Num AS Num2
FROM Logs AS L1
INNER JOIN Logs AS L2 ON L1.Id = (L2.id+1)) AS L3 ON L3.Id2 = L.id+1
WHERE L.Num = L3.Num1 AND L3.Num1 = L3.Num2 AND L3.Num2 = L.Num

Leetcode에는 mysql이 구버전으로 적용되어 있어서, MS SQL Server로 쿼리를 작성해야 한다

SELECT DISTINCT Num AS ConsecutiveNums
FROM (SELECT Num
           , LAG(Num, 1) Over (ORDER BY Id) As 'lag'
           , LAG(Num, 2) Over (ORDER BY id) AS 'lag2'
      FROM Logs) result
WHERE result.num = result.lag AND result.lag2 = result.lag;

LAG() 함수 문법 :

LAG(컬럼명, 칸수) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)