데이터 분석/SQL 연습문제
262. Trips and Users (LeetCode)
중급닌자 연습생
2023. 3. 27. 19:44
Trips and Users - LeetCode
Can you solve this real interview question? Trips and Users - Table: Trips +-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at |
leetcode.com
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
