Trips and Users

hard sql hash map group by

Problem

Given Trips(client_id, driver_id, status, request_at) and Users(users_id, banned), output the cancellation rate of unbanned-user requests for each day between 2013-10-01 and 2013-10-03, rounded to two decimals.

Inputtrips=[(1,10,'completed','10-01'),(2,11,'cancelled_by_driver','10-01'),(3,12,'completed','10-02')]; banned={11}

Output[('10-01', 0.50), ('10-02', 0.00)]

10-01 has 1 trip after filtering banned user 11 (trip with id 2 dropped) → 0/1=0.00. Without banning: 1 cancelled / 2 total = 0.50.

trips (id,driver,status,date; …)

banned ids

SELECT Request_at AS Day,
       ROUND(SUM(CASE WHEN Status != 'completed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS "Cancellation Rate"
FROM Trips
WHERE 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')
  AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Request_at;

Explanation

We want each day's cancellation rate among trips where neither the rider nor the driver is banned. The query first throws away banned-user trips, then groups the survivors by day and divides cancelled by total.

Two subqueries build the banned list, and Client_Id NOT IN (...) AND Driver_Id NOT IN (...) ensures a trip is kept only when both people are clean. A date filter restricts to the reporting window.

Inside each day's group, SUM(CASE WHEN Status != 'completed' THEN 1 ELSE 0 END) counts the cancelled trips, and COUNT(*) counts all kept trips. Dividing them and wrapping in ROUND(..., 2) gives the rate to two decimals.

Example: on 10-01 there are two trips, but the one by banned user 11 is dropped, leaving one completed trip, so the rate is 0 / 1 = 0.00. Without that ban it would have been 1 / 2 = 0.50.

Time: O(T log T) Space: O(D)