262. Trips and Users / LeetCode, SQL, MySQL

문제

source: LeetCode

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 SQL query 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.

 

 

 

 

제출답안(MySQL)

WITH users_ubnd AS (
    SELECT users_id
    FROM users
    WHERE banned = 'No' 
),

oct_1_thru_3 AS (
    SELECT 
        request_at, 
        CASE WHEN status REGEXP'^cancelled' THEN 1
        ELSE 0 END AS cancellation
    FROM 
        trips
    WHERE 
        client_id IN (SELECT users_id FROM users_ubnd)
        AND
        driver_id IN (SELECT users_id FROM users_ubnd)
        AND
        request_at BETWEEN '2013-10-01' AND '2013-10-03'
)

SELECT 
    request_at AS Day,
    ROUND(
        AVG(cancellation), 2
        ) AS 'Cancellation Rate'
FROM oct_1_thru_3
GROUP BY request_at

 

 

 

풀이(MySQL)

전략

>> CTE에서 다음 데이터를 모은다

  a. ban 되지 않은 유저 id 목록

  b. ban 되지 않은 client & driver의 2013-10-01 ~ 2013-10-3의 trip 데이터

    - b 항목은 확률을 계산하기 쉽게 id 와 cancel 여부를 이진법화 (cancel의 경우 1, 아닐 경우 0)

WITH users_ubnd AS (
    SELECT users_id
    FROM users
    WHERE banned = 'No' 
),

oct_1_thru_3 AS (
    SELECT 
        request_at, 
        CASE WHEN status REGEXP'^cancelled' THEN 1
        ELSE 0 END AS cancellation
    FROM 
        trips
    WHERE 
        client_id IN (SELECT users_id FROM users_ubnd)
        AND
        driver_id IN (SELECT users_id FROM users_ubnd)
        AND
        request_at BETWEEN '2013-10-01' AND '2013-10-03'
)

 

oct_1_thru_3에서 날짜별로 그룹화 후 cancel 여부 평균계산

 

SELECT 
    request_at AS Day,
    ROUND(
        AVG(cancellation), 2
        ) AS 'Cancellation Rate'
FROM oct_1_thru_3
GROUP BY request_at

 

 

 

문제 바로가기(MySQL)