FIND THE CANCELLATION RATE USING TRIPS AND USERS TABLES

PROBLEM STATEMENT:

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

IdClient_IdDriver_IdCity_IdStatusRequest_at
11101completed2013-10-01
22111cancelled_by_driver2013-10-01
33126completed2013-10-01
44136cancelled_by_client2013-10-01
51101completed2013-10-02
62116completed2013-10-02
73126completed2013-10-02
821212completed2013-10-03
931012completed2013-10-03
1041312cancelled_by_driver2013-10-03

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

Users_IdBannedRole
1Noclient
2Yesclient
3Noclient
4Noclient
10Nodriver
11Nodriver
12Nodriver
13Nodriver

Write a SQL query to find the cancellation rate of requests made by unbanned users between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

DayCancellation Rate
2013-10-010.33
2013-10-020.00
2013-10-030.50

SOLUTION:

SELECT a.request_at AS Day,
Round(SUM(a.can) / SUM(a.comp), 2) AS Cancellation_Rate
FROM (SELECT request_at,
CASE
WHEN T.status = T.status THEN 1
ELSE 0
END comp,
CASE
WHEN T.status IN( ‘cancelled_by_client’, ‘cancelled_by_driver’
) THEN
1
ELSE 0
END AS can
FROM trips T
join users U
ON T.client_id = U.users_id
WHERE U.banned = ‘No’) a
GROUP BY a.request_at

Comments

comments

Leave a Reply