Daily Unbanned Trip Cancellation Rate

Hard

You are given two relational tables used by a ride-hailing analytics system. Trips records each ride request: id (unique trip id) client_id driver_id city_id status in {completed, cancelled_by_client, cancelled_by_driver} request_at (date string in YYYY-MM-DD) Users records account status: users_id (unique user id) banned in {Yes, No} role in {client, driver, partner} Compute the daily cancellation rate for valid trips in the inclusive date window: 2013-10-01 2013-10-02 2013-10-03 A trip is valid only when: the client exists in Users and is unbanned, and the driver exists in Users and is unbanned. For each day with at least one valid trip, return: Day Cancellation Rate Where: Cancellation Rate = cancelled_valid_trips / total_valid_trips A cancelled trip is one with status cancelled_by_client or cancelled_by_driver. Round cancellation rate to two decimal places. Return rows in any order. This problem must be solvable in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Trips:
| id | client_id | driver_id | city_id | status              | request_at  |
|----|-----------|-----------|---------|---------------------|-------------|
| 1  | 101       | 201       | 1       | completed           | 2013-10-01  |
| 2  | 102       | 202       | 1       | cancelled_by_driver | 2013-10-01  |
| 3  | 101       | 201       | 1       | cancelled_by_client | 2013-10-02  |
| 4  | 103       | 201       | 2       | completed           | 2013-10-04  |

Users:
| users_id | banned | role   |
|----------|--------|--------|
| 101      | No     | client |
| 102      | No     | client |
| 103      | No     | client |
| 201      | No     | driver |
| 202      | No     | driver |

Output

[
  {"Day":"2013-10-01","Cancellation Rate":0.5},
  {"Day":"2013-10-02","Cancellation Rate":1.0}
]

Explanation

Only dates in the target window are considered. On 2013-10-01, one out of two valid trips is cancelled. On 2013-10-02, one out of one valid trips is cancelled. The trip on 2013-10-04 is ignored.

Example 2

Input

Trips:
| id | client_id | driver_id | city_id | status              | request_at  |
|----|-----------|-----------|---------|---------------------|-------------|
| 1  | 1         | 10        | 1       | completed           | 2013-10-01  |
| 2  | 2         | 11        | 1       | cancelled_by_driver | 2013-10-01  |
| 3  | 1         | 11        | 1       | completed           | 2013-10-01  |

Users:
| users_id | banned | role   |
|----------|--------|--------|
| 1        | No     | client |
| 2        | Yes    | client |
| 10       | No     | driver |
| 11       | No     | driver |

Output

[
  {"Day":"2013-10-01","Cancellation Rate":0.0}
]

Explanation

Trip id=2 is excluded because the client is banned. Among valid trips (id=1, id=3), none is cancelled.

Example 3

Input

Trips:
| id | client_id | driver_id | city_id | status              | request_at  |
|----|-----------|-----------|---------|---------------------|-------------|
| 1  | 5         | 50        | 3       | cancelled_by_client | 2013-10-03  |
| 2  | 5         | 51        | 3       | completed           | 2013-10-03  |

Users:
| users_id | banned | role   |
|----------|--------|--------|
| 5        | No     | client |
| 50       | No     | driver |
| 51       | Yes    | driver |

Output

[
  {"Day":"2013-10-03","Cancellation Rate":1.0}
]

Explanation

Trip id=2 is excluded because the driver is banned. The only remaining valid trip is cancelled, so the day rate is 1.0.

Constraints

1 <= number of rows in Trips <= 100,000
1 <= number of rows in Users <= 100,000
Trips.id is unique
Users.users_id is unique
Trip status is one of: completed, cancelled_by_client, cancelled_by_driver
Dates are ISO strings (YYYY-MM-DD)
Only include request_at in [2013-10-01, 2013-10-03]
A trip contributes only if both client and driver are unbanned
Round cancellation rate to 2 decimals
For Pandas submissions, return DataFrame columns exactly: Day, Cancellation Rate

Trips: | id | client_id | driver_id | city_id | status | request_at | |----|-----------|-----------|---------|---------------------|-------------| | 1 | 101 | 201 | 1 | completed | 2013-10-01 | | 2 | 102 | 202 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 101 | 201 | 1 | cancelled_by_client | 2013-10-02 | | 4 | 103 | 201 | 2 | completed | 2013-10-04 | Users: | users_id | banned | role | |----------|--------|--------| | 101 | No | client | | 102 | No | client | | 103 | No | client | | 201 | No | driver | | 202 | No | driver |

Trips: | id | client_id | driver_id | city_id | status | request_at | |----|-----------|-----------|---------|---------------------|-------------| | 1 | 1 | 10 | 1 | completed | 2013-10-01 | | 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 1 | 11 | 1 | completed | 2013-10-01 | Users: | users_id | banned | role | |----------|--------|--------| | 1 | No | client | | 2 | Yes | client | | 10 | No | driver | | 11 | No | driver |

Trips: | id | client_id | driver_id | city_id | status | request_at | |----|-----------|-----------|---------|---------------------|-------------| | 1 | 5 | 50 | 3 | cancelled_by_client | 2013-10-03 | | 2 | 5 | 51 | 3 | completed | 2013-10-03 | Users: | users_id | banned | role | |----------|--------|--------| | 5 | No | client | | 50 | No | driver | | 51 | Yes | driver |