TIL - 250517(SQL_leetcode_3개)

문제1(링크)

SELECT
    DISTINCT(num) AS ConsecutiveNums
FROM (
SELECT
    *
    , LAG(num) OVER (ORDER BY id) prev_num
    , LEAD(num) OVER (ORDER BY id) next_num
FROM Logs
) a
WHERE num = prev_num
AND num = next_num

비고 : 이 문제는 13일 2번 문제이다. 오늘 오류가 있는 것을 알아서 다시 풀었다. 아래와 같은 경우 오류가 나서 num에 DISTINCT를 추가했다.

문제2(링크)

# 코드 1
WITH a AS (
    SELECT
        client_id,
        driver_id,
        request_at,
        status
    FROM Trips
    WHERE client_id IN (SELECT users_id FROM Users WHERE banned = 'No')
    AND driver_id IN (SELECT users_id FROM Users WHERE banned = 'No'
    AND request_at BETWEEN '2013-10-01' AND '2013-10-03')
)
SELECT
    request_at AS `Day`,
    ROUND(SUM(status != 'completed') / COUNT(*), 2) AS `Cancellation Rate`
FROM a
GROUP BY request_at
ORDER BY request_at;

# 코드 2
SELECT
    request_at AS `Day`,
    ROUND(SUM(status != 'completed') / COUNT(*), 2) AS `Cancellation Rate`
FROM Trips t
JOIN Users u1 ON t.client_id = u1.users_id AND u1.banned = 'No'
JOIN Users u2 ON t.driver_id = u2.users_id AND u2.banned = 'No'
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
ORDER BY request_at;

비고 : 나는 1번 코드로 풀었는데 2번이 좀 더 좋다고 평가한다. 확장성, 가독성, 최적화 등 모든 것에서 코드 2가 뛰어나다.

문제3(링크)

SELECT 
    player_id
    , MIN(event_date) first_login
FROM Activity
GROUP BY 1
ORDER BY 1