Trips and the Users
ASKED IN INTERVIEWUber
Problem Statement
Calculate the daily cancellation rate of requests with unbanned users (both client and driver must not be banned) between 2024-01-01 and 2024-01-03.
Rules:
- Output columns: day, cancellation_rate.
- cancellation_rate = (Canceled by Client or Driver) / Total Requests.
- Exclude all trips where either the Client or Driver is Banned.
- Round to 2 decimal places.
- Sort by day in ascending order.
Tests your understanding of
Filtering, Joins and Ratios
Input Tables
trips
| trip_id(INTEGER) | client_id(INTEGER) | driver_id(INTEGER) | status(VARCHAR) | request_at(DATE) |
|---|---|---|---|---|
| 1 | 1 | 10 | completed | 2024-01-01 |
| 2 | 2 | 11 | cancelled_by_driver | 2024-01-01 |
| 3 | 3 | 12 | completed | 2024-01-02 |
| 4 | 1 | 12 | cancelled_by_client | 2024-01-02 |
| 5 | 2 | 10 | completed | 2024-01-03 |
users
| users_id(INTEGER) | banned(VARCHAR) | role(VARCHAR) |
|---|---|---|
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
drivers
| driver_id(INTEGER) | rating(DECIMAL) |
|---|---|
| 10 | 4.8 |
Expected Output
| day(DATE) | cancellation_rate(DECIMAL) |
|---|---|
| 2024-01-01 | 0 |
| 2024-01-02 | 0.5 |
Tags
HardASKED IN INTERVIEWFilteringJoinsRatios
30-40 min
36%