Leet Code | MySQL | 180. Consecutive Numbers

180. Consecutive Numbers

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

Consecutive Numbers - LeetCode

 

Write a SQL query to find all numbers that appear at least three times consecutively.

 

 

-- 윈도우함수로 풀기
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.nex AND l.Num = l.afternext
 

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
-- two times consecutively
 WHERE l.Num = l_next.Num AND l_next.Num = l_next2.Num