[LeetCode] 180. Consecutive Numbers

문제

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번 연속되는 숫자를 출력하면 된다.

 

출력

 

쿼리

SELECT 
    DISTINCT A.NUM AS ConsecutiveNums
FROM 
    LOGS A
JOIN 
    LOGS B 
    ON A.ID + 1 = B.ID 
    AND A.NUM = B.NUM
JOIN 
    LOGS C 
    ON A.ID + 2 = C.ID 
    AND A.NUM = C.NUM;

 

SELECT 
    DISTINCT A.NUM AS ConsecutiveNums
FROM 
    (
        SELECT 
            NUM,
            LEAD(NUM, 1) OVER (ORDER BY ID) AS CON1,
            LEAD(NUM, 2) OVER (ORDER BY ID) AS CON2
        FROM 
            LOGS
    ) A
WHERE 
    A.NUM = A.CON1 
    AND A.CON1 = A.CON2;

 

하나는 LEAD를 사용해서 풀어봤다. 속도는 LEAD 사용한 쿼리가 더 느리게 나왔다.

 

 

 

문제 출처

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