Leetcode Trips and Users problem solution

In this Leetcode Trips and Users problem solution 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”.

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.

Return the result table in any order. Round Cancellation Rate to two decimal points.

Leetcode Trips and Users problem solution

Problem solution in MYSQL.

select t.Request_at as Day,
        cast(sum(case when Status <> 'completed' then 1.00 else 0.00 end)/count(*) as decimal(10,2)) as 'Cancellation Rate'
from Trips as t
join Users as uc on t.Client_Id = uc.Users_Id and uc.Banned = 'No'
join Users as ud on t.Driver_id = ud.Users_Id and ud.Banned = 'No'
where t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at

Problem solution in Oracle.

select request_at Day,
round(sum(case when status ='cancelled_by_driver' or status = 'cancelled_by_client' then 1 else 0 end)/count(*),2) as "Cancellation Rate"
from trips
where request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from users where banned ='Yes')
and driver_id not in (select users_id from users where Banned = 'Yes')
group by request_at
order by request_at;

coding problems