코딩테스트연습(SQL)
262. Trips and Users / LeetCode, SQL, MySQL
LearnerToRunner
2023. 3. 14. 22:44
문제
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)
728x90