262. Trips and Users | Hard 🔥

문제

 

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.

 

테이블 정보

 

일별 Cancellation Rate은 '취소 건수 / 요청 건수'와 같이 계산한다.

단, client와 driver 모두 banned = 'No' 조건을 만족하는 경우만 고려한다.

 

Solution

SELECT t.request_at AS Day
     , ROUND(COUNT(CASE 
                       WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN t.id 
                   END) / COUNT(t.id), 2) AS 'Cancellation Rate'
FROM Trips t
    JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No'
    JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at

/*
SELECT t.request_at Day
     , ROUND(COUNT(CASE WHEN t.status REGEXP 'cancelled' THEN t.id END)/COUNT(*), 2) 'Cancellation Rate'
FROM Trips t
    JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No'
    JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Day

SELECT request_at Day
     , ROUND(COUNT(CASE WHEN status REGEXP 'cancelled' THEN id END)/COUNT(*), 2) 'Cancellation Rate'
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'
GROUP BY Day
*/

 

✅ Trips 테이블의 client_id, driver_id 모두 Users 테이블 user_id 컬럼의 외래 키이므로 2번 조인한다.

✅ 이때, banned = 'No' 조건을 조인 단계에 적용하여 불필요한 행을 조기에 제거한다.

✅ t.status가 'cancelled_by_driver' 또는 'cancelled_by_client'인 경우 해당 t.id를 세어 취소된 여행 수를 집계한다.