00:00:00

Description

Editorial

Monthly Driver Utilization Percentage

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

rides =

[{"ride_id":1,"user_id":55,"requested_at":"2020-03-04"},{"ride_id":2,"user_id":66,"requested_at":"2020-03-18"},{"ride_id":3,"user_id":77,"requested_at":"2020-06-03"},{"ride_id":4,"user_id":88,"requested_at":"2020-11-02"},{"ride_id":5,"user_id":99,"requested_at":"2020-11-09"},{"ride_id":6,"user_id":44,"requested_at":"2020-12-09"},{"ride_id":7,"user_id":33,"requested_at":"2021-01-02"}]

drivers =

[{"driver_id":1,"join_date":"2020-10-24"},{"driver_id":5,"join_date":"2020-02-16"},{"driver_id":7,"join_date":"2020-03-08"},{"driver_id":8,"join_date":"2020-01-13"},{"driver_id":10,"join_date":"2019-12-10"}]

accepted_rides =

[{"ride_id":1,"driver_id":10,"ride_distance":8,"ride_duration":15},{"ride_id":2,"driver_id":10,"ride_distance":5,"ride_duration":11},{"ride_id":3,"driver_id":8,"ride_distance":12,"ride_duration":21},{"ride_id":4,"driver_id":1,"ride_distance":20,"ride_duration":30},{"ride_id":5,"driver_id":7,"ride_distance":17,"ride_duration":28},{"ride_id":6,"driver_id":5,"ride_distance":7,"ride_duration":12},{"ride_id":7,"driver_id":8,"ride_distance":9,"ride_duration":16}]

Loading problem...

101

00:00:00

Description

Editorial

Monthly Driver Utilization Percentage

HARD30 pts

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

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

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

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

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

rides =

drivers =

accepted_rides =

Monthly Driver Utilization Percentage

Hints

Monthly Driver Utilization Percentage

Hints