0/304

00:00:00

Description

Editorial

Monthly Driver Capacity and Completed Trip Totals

HARD30 pts

A mobility analytics team wants a month-by-month 2020 operations report that combines supply capacity and fulfilled demand.

Table: driver_registry

driver_id: unique driver identifier
onboard_date: date the driver became available on the platform

Table: trip_requests

trip_id: unique trip request identifier
rider_id: rider who submitted the request
requested_at: date the request was created

Table: completed_trips

trip_id: trip request that reached completion
driver_id: driver assigned to the completed trip
trip_miles: distance traveled
trip_minutes: duration of the trip

Task: For every month in calendar year 2020 (month = 1 through 12), return:

active_drivers: drivers onboarded by the end of that month.
accepted_trips: number of requests in that month that appear in completed_trips.

Clarifications:

A driver counts as active for month m if onboard_date is before the first day of month m+1.
accepted_trips is based on the request date in trip_requests, not on distance or duration.
Return all 12 months even when one or both metrics are zero.

Output requirements:

Return exactly these columns in order:
- month
- active_drivers
- accepted_trips
Sort by month ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

driver_registry:
| driver_id | onboard_date |
|-----------|--------------|
| 1         | 2019-12-20   |
| 2         | 2020-02-10   |
| 3         | 2020-02-28   |
| 4         | 2020-06-01   |
| 5         | 2021-01-01   |

trip_requests:
| trip_id | rider_id | requested_at |
|---------|----------|--------------|
| 101     | 900      | 2020-01-15   |
| 102     | 901      | 2020-02-20   |
| 103     | 902      | 2020-02-26   |
| 104     | 903      | 2020-06-02   |
| 105     | 904      | 2020-06-15   |
| 106     | 905      | 2020-12-31   |
| 107     | 906      | 2021-01-01   |

completed_trips:
| trip_id | driver_id | trip_miles | trip_minutes |
|---------|-----------|------------|--------------|
| 102     | 2         | 11         | 19           |
| 104     | 4         | 7          | 14           |
| 106     | 1         | 23         | 31           |
| 107     | 5         | 9          | 13           |

Output

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

Explanation

Driver capacity grows when new onboard dates are crossed (February and June). Accepted trips count only completed request IDs whose request date is in 2020.

Example

Input

driver_registry:
| driver_id | onboard_date |
|-----------|--------------|
| 11        | 2020-01-01   |
| 12        | 2020-12-31   |
| 13        | 2019-05-20   |
| 14        | 2021-03-01   |

trip_requests:
| trip_id | rider_id | requested_at |
|---------|----------|--------------|
| 201     | 1        | 2019-12-31   |
| 202     | 2        | 2020-01-01   |
| 203     | 3        | 2020-12-31   |
| 204     | 4        | 2021-01-01   |

completed_trips:
| trip_id | driver_id | trip_miles | trip_minutes |
|---------|-----------|------------|--------------|
| 201     | 13        | 8          | 10           |
| 202     | 11        | 9          | 11           |
| 203     | 12        | 10         | 12           |
| 204     | 14        | 5          | 8            |

Output

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

Explanation

Boundary dates matter: 2020-01-01 contributes from January onward, while 2020-12-31 contributes only in December. Requests outside 2020 do not affect accepted_trips.

Example

Input

driver_registry:
| driver_id | onboard_date |
|-----------|--------------|
| 21        | 2020-11-20   |
| 22        | 2021-02-01   |

trip_requests:
| trip_id | rider_id | requested_at |
|---------|----------|--------------|
| 301     | 500      | 2020-03-10   |
| 302     | 501      | 2020-11-22   |
| 303     | 502      | 2020-12-30   |

completed_trips:
| trip_id | driver_id | trip_miles | trip_minutes |
|---------|-----------|------------|--------------|
| (no rows) |

Output

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

Explanation

Even with requests in 2020, accepted_trips remains zero when no request is completed. Driver activity starts only after the onboarding month arrives.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in driver_registry <= 250000
0 <= number of rows in trip_requests <= 400000
0 <= number of rows in completed_trips <= 400000
driver_registry.driver_id is unique
trip_requests.trip_id is unique
completed_trips.trip_id is unique
Each completed_trips.trip_id exists in trip_requests.trip_id
All date fields are valid calendar dates in ISO format
1 <= driver_id, rider_id, trip_id <= 1000000000
0 <= trip_miles <= 1000000
0 <= trip_minutes <= 1000000
Output must include exactly 12 rows for month values 1 through 12
Output columns must be month, active_drivers, accepted_trips
Sort by month ascending
SQL submissions use SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with exact output columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

trip_requests =

[{"trip_id":101,"rider_id":900,"requested_at":"2020-01-15"},{"trip_id":102,"rider_id":901,"requested_at":"2020-02-20"},{"trip_id":103,"rider_id":902,"requested_at":"2020-02-26"},{"trip_id":104,"rider_id":903,"requested_at":"2020-06-02"},{"trip_id":105,"rider_id":904,"requested_at":"2020-06-15"},{"trip_id":106,"rider_id":905,"requested_at":"2020-12-31"},{"trip_id":107,"rider_id":906,"requested_at":"2021-01-01"}]

completed_trips =

[{"trip_id":102,"driver_id":2,"trip_miles":11,"trip_minutes":19},{"trip_id":104,"driver_id":4,"trip_miles":7,"trip_minutes":14},{"trip_id":106,"driver_id":1,"trip_miles":23,"trip_minutes":31},{"trip_id":107,"driver_id":5,"trip_miles":9,"trip_minutes":13}]

driver_registry =

[{"driver_id":1,"onboard_date":"2019-12-20"},{"driver_id":2,"onboard_date":"2020-02-10"},{"driver_id":3,"onboard_date":"2020-02-28"},{"driver_id":4,"onboard_date":"2020-06-01"},{"driver_id":5,"onboard_date":"2021-01-01"}]

Monthly Driver Capacity and Completed Trip Totals

Hints

Monthly Driver Capacity and Completed Trip Totals

Hints