This project involves analysing the cancellation rate for ride requests, specially from users who are not banned. Given two tables, trips and users containing information on trips and users respectively. Calculate the cancellation rate for carpool requests involving users who are not banned. A ride request is considered cancelled if it is cancelled either by the customer or by the driver. The cancellation rate is calculated by dividing the number of cancelled request (involving non-banned users) by the total number of requests (with non-banned users) each day.
Table name: Trips
id | client_id | driver_id | city_id | status | request_at |
1 | 1 | 10 | 1 | completed | 2023-07-12 |
2 | 2 | 11 | 1 | cancelled_by_driver | 2023-07-12 |
3 | 3 | 12 | 6 | completed | 2023-07-12 |
4 | 4 | 13 | 6 | cancelled_by_client | 2023-07-12 |
5 | 1 | 10 | 1 | completed | 2023-07-13 |
6 | 2 | 11 | 6 | completed | 2023-07-13 |
7 | 3 | 12 | 6 | completed | 2023-07-13 |
8 | 2 | 12 | 12 | completed | 2023-07-14 |
9 | 3 | 10 | 12 | completed | 2023-07-14 |
10 | 4 | 13 | 12 | cancelled_by_driver | 2023-07-14 |
Table name: Users
users_id | banned | role |
1 | No | client |
2 | Yes | client |
3 | No | client |
4 | No | client |
10 | No | driver |
11 | No | driver |
12 | No | driver |
13 | No | driver |
Output should be as follows
day | cancellation rate |
2023-07-12 | 0.33 |
2023-07-13 | 0.00 |
2023-07-14 | 0.50 |
