LeetCode 262. Trips and Users
考点 | 难度 |
---|---|
Database | Hard |
题目
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.
思路
用not in
所有被banned的users对client_id
进行筛选。
答案
SELECT t.Request_at AS 'Day', round(
sum(case t.Status when 'cancelled_by_driver' then 1
when 'cancelled_by_client' then 1
else 0 end) / count(*), 2) AS 'Cancellation Rate'
FROM Trips t
WHERE '2013-10-01' <= t.Request_at and t.Request_at <= '2013-10-03'
and t.Client_Id in (SELECT Users_Id FROM Users u WHERE Banned = 'No')
and t.Driver_Id in (SELECT Users_Id FROM Users u WHERE Banned = 'No')
GROUP BY T.Request_at