[leetcode / MySQL] 262. Trips and Users

문제

💡 The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

Return the result table in any order.

 

테이블 형태

 

풀이

# Users 테이블에서 banned=Yes인 사람의 주문은 Trips 테이블에서 모두 제거한다
# 취소된 건을 날짜별 전체 주문 건수로 나눈다

WITH trips_n AS(
    SELECT * FROM Trips
    WHERE client_id NOT IN (SELECT users_id AS client_id FROM Users WHERE banned='Yes' AND role='client')
        AND driver_id NOT IN (SELECT users_id AS driver_id FROM Users WHERE banned='Yes' AND role='driver')
)

SELECT request_at AS Day
    , ROUND( COUNT(CASE WHEN status LIKE 'cancelled%' THEN id END) / COUNT(id), 2) AS 'Cancellation Rate'
FROM trips_n
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Day

 

해설

WITH trips_n AS(
    SELECT * FROM Trips
    WHERE client_id NOT IN (SELECT users_id AS client_id FROM Users WHERE banned='Yes' AND role='client')
        AND driver_id NOT IN (SELECT users_id AS driver_id FROM Users WHERE banned='Yes' AND role='driver')
)

• 1️⃣WITH절로 집계 대상만 포함된 테이블 만들기
• Users(U) 테이블에서 차단당한 고객이나 드라이버가 포함된 주문 건은 모든 집계에서 제외하고, 10-01 ~ 10-03일 사이 날짜별 전체 주문 건수에서 취소 건수의 비율을 구해야 한다
• 테이블 U에서 차단당한 클라이언트나 드라이버의 ID를 구해 NOT IN 을 활용해 해당 데이터에 포함되지 않는(=차단 당하지 않은) 데이터만 필터링해 trips_n(t) 테이블을 만들어 집계할 테이블로 사용했다

SELECT request_at AS Day
    , ROUND( COUNT(CASE WHEN status LIKE 'cancelled%' THEN id END) / COUNT(id), 2) AS 'Cancellation Rate'
FROM trips_n
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Day

• 2️⃣조건을 적용해 날짜 별 취소 건수 비율 구하기
• WHERE문에서 날짜 조건을 지정해 10-01 ~ 10-03일 사이의 주문 내역만 처리할 수 있도록 데이터를 필터링하였고, 주문 날짜인 Day를 기준으로 그룹핑해 날짜별로 집계할 수 있도록 했다
• 취소된 건 수는 CASE문을 사용해 status 가 cancelled를 포함하면 id를 COUNT하는 것으로 집계했고, 이를 날짜별 전체 주문 건수(=COUNT(id))로 나눈 뒤 ROUND를 사용해 반올림하여 소수 둘째자리까지 나타냈다
• 띄어쓰기가 포함된 컬럼은 따옴표로 감싸줘야 한다(오류나서 쿼리 보다가 발견함)
• 900ms로 91.01%보다 빠른 쿼리다