Rolling Quarter Ride Metrics

Hard

A mobility analytics team wants a stable monthly KPI that smooths short-term volatility. Tables: drivers driver_id: unique identifier of a driver join_date: date the driver joined the platform rides ride_id: unique ride request id user_id: rider identifier requested_at: request date acceptedrides ride_id: unique accepted ride id (always references rides.ride_id) driver_id: driver who completed the ride ride_distance: distance of the accepted ride ride_duration: duration of the accepted ride Task: For each start month from 1 through 10 in year 2020, compute the trailing 3-month operational averages: average_ride_distance = (total accepted ride_distance in month m, m+1, m+2) / 3 average_ride_duration = (total accepted ride_duration in month m, m+1, m+2) / 3 Clarifications: Only rides requested during year 2020 are eligible. A ride contributes only when it appears in acceptedrides. Months without accepted rides contribute 0 to the 3-month sums. Return one row for each month m in [1, 10]. Output requirements: Return columns in this exact order: month average_ride_distance average_ride_duration Round both averages to 2 decimal places. Order by month ascending. 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  |
| 4         | 2020-05-17  |
| 1         | 2020-10-24  |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 1       | 54      | 2020-02-09   |
| 10      | 63      | 2020-03-04   |
| 19      | 39      | 2020-04-06   |
| 13      | 52      | 2020-06-22   |
| 7       | 69      | 2020-07-16   |
| 17      | 70      | 2020-08-25   |
| 20      | 81      | 2020-11-02   |
| 5       | 57      | 2020-11-09   |
| 2       | 42      | 2020-12-09   |

acceptedrides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 10      | 10        | 63            | 38            |
| 13      | 10        | 73            | 96            |
| 7       | 8         | 100           | 28            |
| 17      | 7         | 119           | 68            |
| 20      | 1         | 121           | 92            |
| 5       | 7         | 42            | 101           |
| 2       | 4         | 6             | 38            |

Output

[
  {"month":1,"average_ride_distance":21.0,"average_ride_duration":12.67},
  {"month":2,"average_ride_distance":21.0,"average_ride_duration":12.67},
  {"month":3,"average_ride_distance":21.0,"average_ride_duration":12.67},
  {"month":4,"average_ride_distance":24.33,"average_ride_duration":32.0},
  {"month":5,"average_ride_distance":57.67,"average_ride_duration":41.33},
  {"month":6,"average_ride_distance":97.33,"average_ride_duration":64.0},
  {"month":7,"average_ride_distance":73.0,"average_ride_duration":32.0},
  {"month":8,"average_ride_distance":39.67,"average_ride_duration":22.67},
  {"month":9,"average_ride_distance":54.33,"average_ride_duration":64.33},
  {"month":10,"average_ride_distance":56.33,"average_ride_duration":77.0}
]

Explanation

Each row uses a 3-month window. For month 1, aggregate January+February+March accepted ride metrics, divide each sum by 3, and round to two decimals. Repeat for months 2 through 10.

Example 2

Input

drivers:
| driver_id | join_date   |
|-----------|-------------|
| 1         | 2019-01-01  |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 100     | 200     | 2020-01-10   |
| 101     | 201     | 2020-02-10   |

acceptedrides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| (no rows) |

Output

[
  {"month":1,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":2,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":3,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":4,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":5,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":6,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":7,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":8,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":9,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":10,"average_ride_distance":0.0,"average_ride_duration":0.0}
]

Explanation

When there are no accepted rides, each month contributes 0 to the rolling windows, so every output average is 0.

Example 3

Input

drivers:
| driver_id | join_date   |
|-----------|-------------|
| 11        | 2018-07-01  |
| 12        | 2019-03-14  |

rides:
| ride_id | user_id | requested_at |
|---------|---------|--------------|
| 201     | 70      | 2019-12-31   |
| 202     | 71      | 2020-01-01   |
| 203     | 72      | 2020-10-15   |
| 204     | 73      | 2021-01-01   |

acceptedrides:
| ride_id | driver_id | ride_distance | ride_duration |
|---------|-----------|---------------|---------------|
| 201     | 11        | 40            | 20            |
| 202     | 12        | 90            | 60            |
| 203     | 11        | 30            | 15            |
| 204     | 12        | 999           | 999           |

Output

[
  {"month":1,"average_ride_distance":30.0,"average_ride_duration":20.0},
  {"month":2,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":3,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":4,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":5,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":6,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":7,"average_ride_distance":0.0,"average_ride_duration":0.0},
  {"month":8,"average_ride_distance":10.0,"average_ride_duration":5.0},
  {"month":9,"average_ride_distance":10.0,"average_ride_duration":5.0},
  {"month":10,"average_ride_distance":10.0,"average_ride_duration":5.0}
]

Explanation

Only 2020 rides count. Accepted rows tied to 2019 and 2021 requests are ignored even though they exist in acceptedrides.

Constraints

1 <= number of rows in drivers <= 300000
0 <= number of rows in rides <= 500000
0 <= number of rows in acceptedrides <= number of rows in rides
driver_id is unique in drivers
ride_id is unique in rides
ride_id is unique in acceptedrides
Each acceptedrides.ride_id refers to an existing rides.ride_id
ride_distance and ride_duration are non-negative integers
Return exactly 10 rows for start months 1..10
Round each average to 2 decimal places
Return columns: month, average_ride_distance, average_ride_duration
Sort output by month ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

drivers: | driver_id | join_date | |-----------|-------------| | 10 | 2019-12-10 | | 8 | 2020-01-13 | | 5 | 2020-02-16 | | 7 | 2020-03-08 | | 4 | 2020-05-17 | | 1 | 2020-10-24 | rides: | ride_id | user_id | requested_at | |---------|---------|--------------| | 1 | 54 | 2020-02-09 | | 10 | 63 | 2020-03-04 | | 19 | 39 | 2020-04-06 | | 13 | 52 | 2020-06-22 | | 7 | 69 | 2020-07-16 | | 17 | 70 | 2020-08-25 | | 20 | 81 | 2020-11-02 | | 5 | 57 | 2020-11-09 | | 2 | 42 | 2020-12-09 | acceptedrides: | ride_id | driver_id | ride_distance | ride_duration | |---------|-----------|---------------|---------------| | 10 | 10 | 63 | 38 | | 13 | 10 | 73 | 96 | | 7 | 8 | 100 | 28 | | 17 | 7 | 119 | 68 | | 20 | 1 | 121 | 92 | | 5 | 7 | 42 | 101 | | 2 | 4 | 6 | 38 |

[ {"month":1,"average_ride_distance":21.0,"average_ride_duration":12.67}, {"month":2,"average_ride_distance":21.0,"average_ride_duration":12.67}, {"month":3,"average_ride_distance":21.0,"average_ride_duration":12.67}, {"month":4,"average_ride_distance":24.33,"average_ride_duration":32.0}, {"month":5,"average_ride_distance":57.67,"average_ride_duration":41.33}, {"month":6,"average_ride_distance":97.33,"average_ride_duration":64.0}, {"month":7,"average_ride_distance":73.0,"average_ride_duration":32.0}, {"month":8,"average_ride_distance":39.67,"average_ride_duration":22.67}, {"month":9,"average_ride_distance":54.33,"average_ride_duration":64.33}, {"month":10,"average_ride_distance":56.33,"average_ride_duration":77.0} ]

drivers: | driver_id | join_date | |-----------|-------------| | 1 | 2019-01-01 | rides: | ride_id | user_id | requested_at | |---------|---------|--------------| | 100 | 200 | 2020-01-10 | | 101 | 201 | 2020-02-10 | acceptedrides: | ride_id | driver_id | ride_distance | ride_duration | |---------|-----------|---------------|---------------| | (no rows) |

[ {"month":1,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":2,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":3,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":4,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":5,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":6,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":7,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":8,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":9,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":10,"average_ride_distance":0.0,"average_ride_duration":0.0} ]

drivers: | driver_id | join_date | |-----------|-------------| | 11 | 2018-07-01 | | 12 | 2019-03-14 | rides: | ride_id | user_id | requested_at | |---------|---------|--------------| | 201 | 70 | 2019-12-31 | | 202 | 71 | 2020-01-01 | | 203 | 72 | 2020-10-15 | | 204 | 73 | 2021-01-01 | acceptedrides: | ride_id | driver_id | ride_distance | ride_duration | |---------|-----------|---------------|---------------| | 201 | 11 | 40 | 20 | | 202 | 12 | 90 | 60 | | 203 | 11 | 30 | 15 | | 204 | 12 | 999 | 999 |

[ {"month":1,"average_ride_distance":30.0,"average_ride_duration":20.0}, {"month":2,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":3,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":4,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":5,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":6,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":7,"average_ride_distance":0.0,"average_ride_duration":0.0}, {"month":8,"average_ride_distance":10.0,"average_ride_duration":5.0}, {"month":9,"average_ride_distance":10.0,"average_ride_duration":5.0}, {"month":10,"average_ride_distance":10.0,"average_ride_duration":5.0} ]