Monthly Driver Utilization Percentage

Hard

A mobility analytics team wants a monthly utilization KPI for its driver network. Table: drivers driver_id: unique driver identifier join_date: date when the driver became active on the platform Table: rides ride_id: unique ride request identifier user_id: rider identifier requested_at: date when the ride was requested Table: accepted_rides ride_id: accepted ride identifier (maps to rides.ride_id) driver_id: driver who accepted and completed the ride ride_distance: trip distance for that accepted ride ride_duration: trip duration for that accepted ride Task: For each month of calendar year 2020, return the percentage of available drivers who were working. Definitions: Available drivers in month M: drivers whose join_date is on or before the last day of month M. Working drivers in month M: distinct drivers who completed at least one accepted ride with requested_at in month M of 2020. Data quality rule: Only count a working ride if the driver's join_date is on or before that ride's requested_at. (This protects against noisy records where accepted_rides may reference an impossible pre-join assignment.) Metric: working_percentage = (working_drivers / available_drivers) * 100 If available_drivers is 0 for a month, working_percentage is 0.00. Round to 2 decimal places. Output requirements: Return exactly 12 rows for months 1 through 12. Return columns in exact order: month working_percentage Sort by month in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 10        | 2019-12-10 |
| 8         | 2020-01-13 |
| 5         | 2020-02-16 |
| 7         | 2020-03-08 |
| 1         | 2020-10-24 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 1       | 55      | 2020-03-04   |
| 2       | 66      | 2020-03-18   |
| 3       | 77      | 2020-06-03   |
| 4       | 88      | 2020-11-02   |
| 5       | 99      | 2020-11-09   |
| 6       | 44      | 2020-12-09   |
| 7       | 33      | 2021-01-02   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 1       | 10        | 8             | 15            |
| 2       | 10        | 5             | 11            |
| 3       | 8         | 12            | 21            |
| 4       | 1         | 20            | 30            |
| 5       | 7         | 17            | 28            |
| 6       | 5         | 7             | 12            |
| 7       | 8         | 9             | 16            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":25.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":25.0},
  {"month":7,"working_percentage":0.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":40.0},
  {"month":12,"working_percentage":20.0}
]

Explanation

March has 4 available drivers and 1 distinct working driver (10), so 25.00. In November, 5 drivers are available and 2 distinct drivers worked (1 and 7), so 40.00. The 2021 ride is ignored for the 2020 report.

Example 2

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 42        | 2021-03-01 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 100     | 9       | 2020-06-10   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 100     | 42        | 11            | 18            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":0.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":0.0},
  {"month":7,"working_percentage":0.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":0.0},
  {"month":12,"working_percentage":0.0}
]

Explanation

No driver is available during 2020, so each month has denominator 0 and therefore reports 0.00 by definition.

Example 3

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 1         | 2019-11-01 |
| 2         | 2020-07-15 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 21      | 500     | 2020-07-01   |
| 22      | 501     | 2020-07-20   |
| 23      | 502     | 2020-07-22   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 21      | 2         | 10            | 20            |
| 22      | 2         | 4             | 9             |
| 23      | 1         | 8             | 14            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":0.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":0.0},
  {"month":7,"working_percentage":100.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":0.0},
  {"month":12,"working_percentage":0.0}
]

Explanation

For July, both drivers are available by month end. Ride 21 is ignored for driver 2 because it is before that driver's join_date. Rides 22 and 23 make both drivers working in July, giving 2/2 = 100.00.

Constraints

0 <= number of rows in drivers <= 1800
0 <= number of rows in rides <= 3200
0 <= number of rows in accepted_rides <= 3200
drivers.driver_id is unique
rides.ride_id is unique
accepted_rides.ride_id is unique
accepted_rides.ride_id always references an existing rides.ride_id
join_date and requested_at are valid calendar dates
driver_id and ride_id are positive integers
Return exactly 12 rows (months 1 through 12)
Return exactly columns: month, working_percentage
Round working_percentage to 2 decimal places
If available_drivers = 0, working_percentage must be 0.00
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: month, working_percentage

Preparing your editor. You can read the problem while it loads.

Monthly Driver Utilization Percentage

Hard

A mobility analytics team wants a monthly utilization KPI for its driver network. Table: drivers driver_id: unique driver identifier join_date: date when the driver became active on the platform Table: rides ride_id: unique ride request identifier user_id: rider identifier requested_at: date when the ride was requested Table: accepted_rides ride_id: accepted ride identifier (maps to rides.ride_id) driver_id: driver who accepted and completed the ride ride_distance: trip distance for that accepted ride ride_duration: trip duration for that accepted ride Task: For each month of calendar year 2020, return the percentage of available drivers who were working. Definitions: Available drivers in month M: drivers whose join_date is on or before the last day of month M. Working drivers in month M: distinct drivers who completed at least one accepted ride with requested_at in month M of 2020. Data quality rule: Only count a working ride if the driver's join_date is on or before that ride's requested_at. (This protects against noisy records where accepted_rides may reference an impossible pre-join assignment.) Metric: working_percentage = (working_drivers / available_drivers) * 100 If available_drivers is 0 for a month, working_percentage is 0.00. Round to 2 decimal places. Output requirements: Return exactly 12 rows for months 1 through 12. Return columns in exact order: month working_percentage Sort by month in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 10        | 2019-12-10 |
| 8         | 2020-01-13 |
| 5         | 2020-02-16 |
| 7         | 2020-03-08 |
| 1         | 2020-10-24 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 1       | 55      | 2020-03-04   |
| 2       | 66      | 2020-03-18   |
| 3       | 77      | 2020-06-03   |
| 4       | 88      | 2020-11-02   |
| 5       | 99      | 2020-11-09   |
| 6       | 44      | 2020-12-09   |
| 7       | 33      | 2021-01-02   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 1       | 10        | 8             | 15            |
| 2       | 10        | 5             | 11            |
| 3       | 8         | 12            | 21            |
| 4       | 1         | 20            | 30            |
| 5       | 7         | 17            | 28            |
| 6       | 5         | 7             | 12            |
| 7       | 8         | 9             | 16            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":25.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":25.0},
  {"month":7,"working_percentage":0.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":40.0},
  {"month":12,"working_percentage":20.0}
]

Explanation

Example 2

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 42        | 2021-03-01 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 100     | 9       | 2020-06-10   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 100     | 42        | 11            | 18            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":0.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":0.0},
  {"month":7,"working_percentage":0.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":0.0},
  {"month":12,"working_percentage":0.0}
]

Explanation

No driver is available during 2020, so each month has denominator 0 and therefore reports 0.00 by definition.

Example 3

Input

drivers:
| driver_id | join_date  |
|-----------|------------|
| 1         | 2019-11-01 |
| 2         | 2020-07-15 |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 21      | 500     | 2020-07-01   |
| 22      | 501     | 2020-07-20   |
| 23      | 502     | 2020-07-22   |

accepted_rides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 21      | 2         | 10            | 20            |
| 22      | 2         | 4             | 9             |
| 23      | 1         | 8             | 14            |

Output

[
  {"month":1,"working_percentage":0.0},
  {"month":2,"working_percentage":0.0},
  {"month":3,"working_percentage":0.0},
  {"month":4,"working_percentage":0.0},
  {"month":5,"working_percentage":0.0},
  {"month":6,"working_percentage":0.0},
  {"month":7,"working_percentage":100.0},
  {"month":8,"working_percentage":0.0},
  {"month":9,"working_percentage":0.0},
  {"month":10,"working_percentage":0.0},
  {"month":11,"working_percentage":0.0},
  {"month":12,"working_percentage":0.0}
]

Explanation

Constraints

0 <= number of rows in drivers <= 1800
0 <= number of rows in rides <= 3200
0 <= number of rows in accepted_rides <= 3200
drivers.driver_id is unique
rides.ride_id is unique
accepted_rides.ride_id is unique
accepted_rides.ride_id always references an existing rides.ride_id
join_date and requested_at are valid calendar dates
driver_id and ride_id are positive integers
Return exactly 12 rows (months 1 through 12)
Return exactly columns: month, working_percentage
Round working_percentage to 2 decimal places
If available_drivers = 0, working_percentage must be 0.00
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: month, working_percentage