Im between cherry

Leet Code | MySQL | 180. Consecutive Numbers 본문

데이터분석/practice_query

Leet Code | MySQL | 180. Consecutive Numbers

meal 2020. 8. 30. 14:30

180. Consecutive Numbers

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

 

Consecutive Numbers - LeetCode

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

leetcode.com

 

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

 

Comments