00:00:00

Description

Editorial

Daily Unbanned Trip Cancellation Rate

HARD35 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

trips =

[{"id":1,"client_id":101,"driver_id":201,"city_id":1,"status":"completed","request_at":"2013-10-01"},{"id":2,"client_id":102,"driver_id":202,"city_id":1,"status":"cancelled_by_driver","request_at":"2013-10-01"},{"id":3,"client_id":101,"driver_id":201,"city_id":1,"status":"cancelled_by_client","request_at":"2013-10-02"},{"id":4,"client_id":103,"driver_id":201,"city_id":2,"status":"completed","request_at":"2013-10-04"}]

users =

[{"users_id":101,"banned":"No","role":"client"},{"users_id":102,"banned":"No","role":"client"},{"users_id":103,"banned":"No","role":"client"},{"users_id":201,"banned":"No","role":"driver"},{"users_id":202,"banned":"No","role":"driver"}]

Loading problem...

101

00:00:00

Description

Editorial

Daily Unbanned Trip Cancellation Rate

HARD35 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

trips =

users =

Daily Unbanned Trip Cancellation Rate

Hints

Daily Unbanned Trip Cancellation Rate

Hints