[리트코드] 262. Trips and Users - MySQL

https://leetcode.com/problems/trips-and-users/

Trips and Users - LeetCode

---

• 문제
  • 2013-10-01~2013-10-03 3일간 매일 여행 취소율을 구하라
    • banned한 사람들을 제외하고

• 풀이

3일간 banned 하지 않은 사람들의 여행 정보를 가져오자

SELECT STATUS
     , REQUEST_AT
FROM TRIPS AS T
    INNER JOIN USERS AS UC
        ON T.CLIENT_ID = UC.USERS_ID
    INNER JOIN USERS AS UD
        ON T.DRIVER_ID = UD.USERS_ID
WHERE T.REQUEST_AT BETWEEN '2013-10-01' AND '2013-10-03'
    AND UC.BANNED = 'NO' AND UD.BANNED = 'NO';

INPUT

OUTPUT

각 날짜별 전체 개수와 취소 개수를 구해보자.

CASE문을 활용해서 'COMPLETED'가 아닌 것의 수 세기

SELECT REQUEST_AT
    -- 취소 개수
    , SUM(CASE WHEN STATUS != 'COMPLETED' THEN 1 ELSE 0 END) AS CANCEL_COUNT
    -- 전체
    , COUNT(*) AS TOTAL_COUNT
FROM TRIPS AS T
    INNER JOIN USERS AS UC
        ON T.CLIENT_ID = UC.USERS_ID
    INNER JOIN USERS AS UD
        ON T.DRIVER_ID = UD.USERS_ID
WHERE T.REQUEST_AT BETWEEN '2013-10-01' AND '2013-10-03'
    AND UC.BANNED = 'NO' AND UD.BANNED = 'NO'
GROUP BY REQUEST_AT;

윈도우 함수의 결과를 조건/계산 할 수 없으므로 서브쿼리를 활용하여 나누면 된다.

• 정답 코드

SELECT REQUEST_AT AS 'Day'
     , ROUND(CANCEL_COUNT/TOTAL_COUNT,2) AS 'Cancellation Rate'
FROM (
    SELECT REQUEST_AT
        , SUM(CASE WHEN STATUS != 'COMPLETED' THEN 1 ELSE 0 END) AS CANCEL_COUNT
        , COUNT(*) AS TOTAL_COUNT
    FROM TRIPS AS T
        INNER JOIN USERS AS UC
            ON T.CLIENT_ID = UC.USERS_ID
        INNER JOIN USERS AS UD
            ON T.DRIVER_ID = UD.USERS_ID
    WHERE T.REQUEST_AT BETWEEN '2013-10-01' AND '2013-10-03'
        AND UC.BANNED = 'NO' AND UD.BANNED = 'NO'
    GROUP BY REQUEST_AT
) T;