These days there is a new problem in the market “Uber\Ola Wale Bhaiya Problem”. 

I think you might have faced this problem once in your life.

Suraz is also tired of this problem. While coming back from Office with his friends every day Suraz, used to book a taxi ride to his home.

Every time he books a cab, they(Uber\Ola Wale Bhaiya) accept it, and ask him "sir where will you go?", and if they don't wish to go, they keep stalling you so that you cancel it and bear the cancellation fees. Suraz literally needs to offer them 100/200+ more as he wants to reach his early, then they agree to take him, and sometimes they asked "Is the payment online or in cash?", the moment they hear online, most of the time they deny to take you.

Suraz has many times reported this issue to their respective companies. After having a deep conversation with the cab managers, Cab companies have decided to fine-tune their cancellation rate.

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.

Problem Statement:- 

Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day. Round Cancellation Rate to two decimal points.

Return the result table in any order.

Below are SQL tables that we have to use to solve this problem.

The query result format is in the following example.

Sample Input:-

Sample Output:-

Solution:-

Before moving to the final solution I would suggest you give it a try and then jump into the solution.

As per the problem statement, we have to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) every day and 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.

So first of all we have to calculate the total no of rides that are not completed and in simple words, those are canceled (by client or driver) and we have to divide the total no of rides by the unbanned user on the requested date.

Final Query:-

SELECT request_at AS Day,
ROUND(SUM(case when status != 'completed' then 1 else 0 end)*1.0 / 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')
GROUP BY request_at;

Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.