262. Trips and Users (LeetCode)

Trips and Users - LeetCode

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.

풀이

제출한 답

select 
    t.request_at as day,
    round(sum(case when t.status <> 'completed' then 1 else 0 end) / count(*), 2) as 'Cancellation Rate'
from trips as t 
join users as u_1 on t.client_id = u_1.users_id
join users as u_2 on t.driver_id = u_2.users_id
where t.request_at between '2013-10-01' and '2013-10-03'
and u_1.banned = 'No' and u_2.banned = 'No'
group by t.request_at
order by t.request_at

👉 status = 'cancelled' 조건에 대해 status <> 'complete' 으로 접근, 분기 나눌 때는 case when ~ then else

2023.03.27