Monthly Driver Capacity and Completed Trip Totals

Hard

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 1

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 2

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 3

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.

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

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

Monthly Driver Capacity and Completed Trip Totals

Hard

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 1

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 2

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 3

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.

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