0/304

00:00:00

Description

Editorial

Rolling Quarter Ride Metrics

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

rides =

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

drivers =

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

acceptedrides =

[{"ride_id":10,"driver_id":10,"ride_distance":63,"ride_duration":38},{"ride_id":13,"driver_id":10,"ride_distance":73,"ride_duration":96},{"ride_id":7,"driver_id":8,"ride_distance":100,"ride_duration":28},{"ride_id":17,"driver_id":7,"ride_distance":119,"ride_duration":68},{"ride_id":20,"driver_id":1,"ride_distance":121,"ride_duration":92},{"ride_id":5,"driver_id":7,"ride_distance":42,"ride_duration":101},{"ride_id":2,"driver_id":4,"ride_distance":6,"ride_duration":38}]

Rolling Quarter Ride Metrics

Hints

Rolling Quarter Ride Metrics

Hints