leetcode code gist

262-Trips-and-Users.sql

# Write your MySQL query statement below

select all_trips.Day,ct.cancel_purchase,all_trips.all_purchase, case when ct.cancel_purchase then round(ct.cancel_purchase/all_trips.all_purchase,2) else 0.0 end as 'Cancellation Rate' from 
(select count(*) as 'all_purchase', Request_at as Day  from Trips group by Request_at) as all_trips
left join 
(select count(*) as 'cancel_purchase', Request_at as Day  from Trips where Status!='completed' group by Request_at) ct 
on all_trips.Day=ct.Day
order by Day


SELECT Request_at Day, ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) 'Cancellation Rate'
FROM Trips WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03') AND Client_Id IN
(SELECT Users_Id FROM Users WHERE Banned = 'No') GROUP BY Request_at;