여정에 대한 기록을 하는 테이블과 유저에 대한 테이블이 있다. User가 client가 될 수도 있고, driver가 될 수도 있다.
문제:
그 날의 Ban되지 않은 유저가 요청한 Canceled인 상태의 Trip에 총 Trip 횟수를 나누어 표시하라.
Trip에서 client와 driver 양쪽이 다 ban이 되지 않고 2013-10-01에서 2013-10-03 사이의 Trip으로 소숫점 2번째 자리의 취소율을 구하면 된다.
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.
The query result format is in the following example.
예시 )
Explanation: On 2013-10-01: - There were 4 requests in total, 2 of which were canceled. - However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation. - Hence there are 3 unbanned requests in total, 1 of which was canceled. - The Cancellation Rate is (1 / 3) = 0.33
On 2013-10-02: - There were 3 requests in total, 0 of which were canceled. - The request with Id=6 was made by a banned client, so it is ignored. - Hence there are 2 unbanned requests in total, 0 of which were canceled. - The Cancellation Rate is (0 / 2) = 0.00
On 2013-10-03: - There were 3 requests in total, 1 of which was canceled. - The request with Id=8 was made by a banned client, so it is ignored. - Hence there are 2 unbanned request in total, 1 of which were canceled. - The Cancellation Rate is (1 / 2) = 0.50
풀이
정답지가 딱 두개의 컬럼인데 Cancellation Rate는 어떤 집계가 필요하고 Day는 Group by를 하면 되겠다라는 생각으로 접근을 했다. 그리고 집계를 할 때 필요한 소스가 무엇인지 생각해본다. Unbanned driver와 client의 취소 횟수와 트립횟수를 찾아서 나누면 된다. 그래서 날짜별로 status가 completed가 아닌 것으로 먼저 테이블을 만들고 그 테이블을 가지고 전체횟수를 나눴다.
select x.request_at Day,
round(sum(cast(x.CntCanceled as decimal(7, 2))) / cast(count(x.request_at) as decimal(7, 2)), 2) as 'Cancellation Rate'
from (select t.request_at, case when t.status <> 'completed' then 1 else 0 end as CntCanceled
from trips t
left outer join users u on t.client_id = u.users_id and u.role = 'client'
left outer join users u1 on t.driver_id = u1.users_id and u1.role = 'driver'
where u.banned = 'No'
and u1.banned = 'No'
and t.request_at between '2013-10-01' and '2013-10-03') x
where x.request_at between '2013-10-01' and '2013-10-03'
group by x.request_at
