You have three tables: Drivers, Rides, and AcceptedRides.
The Drivers table contains driver information including their join dates. The Rides table contains all ride requests (some may not be accepted). The AcceptedRides table contains information about rides that were actually completed.
Write a SQL query to compute the average_ride_distance and average_ride_duration for every 3-month window starting from January-March 2020 to October-December 2020.
Requirements:
- Round both averages to 2 decimal places
- The average is calculated by summing the total values from the three months and dividing by 3
- Return results ordered by month (starting month number: 1=January, 2=February, etc.)
- Include all 3-month windows even if no rides occurred
Table Schema
| Column Name | Type | Description |
|---|---|---|
driver_id
PK
|
int | Unique driver identifier |
join_date
|
date | Date when driver joined Hopper company |
| Column Name | Type | Description |
|---|---|---|
ride_id
PK
|
int | Unique ride identifier |
user_id
|
int | User who requested the ride |
requested_at
|
date | Date when ride was requested |
| Column Name | Type | Description |
|---|---|---|
ride_id
PK
|
int | Reference to ride from Rides table |
driver_id
|
int | Driver who accepted the ride |
ride_distance
|
int | Distance of the ride in units |
ride_duration
|
int | Duration of the ride in minutes |
Input & Output
| driver_id | join_date |
|---|---|
| 1 | 2019-12-10 |
| 2 | 2020-01-08 |
| ride_id | user_id | requested_at |
|---|---|---|
| 1 | 75 | 2020-01-01 |
| 2 | 54 | 2020-02-11 |
| 3 | 63 | 2020-03-04 |
| 4 | 99 | 2020-04-12 |
| ride_id | driver_id | ride_distance | ride_duration |
|---|---|---|---|
| 1 | 1 | 20 | 30 |
| 2 | 2 | 40 | 40 |
| 3 | 1 | 35 | 45 |
| month | average_ride_distance | average_ride_duration |
|---|---|---|
| 1 | 31.67 | 38.33 |
| 2 | 25 | 28.33 |
| 3 | 11.67 | 15 |
| 4 | 0 | 0 |
| 5 | 0 | 0 |
| 6 | 0 | 0 |
| 7 | 0 | 0 |
| 8 | 0 | 0 |
| 9 | 0 | 0 |
| 10 | 0 | 0 |
For January-March window: rides 1,2,3 with distances (20+40+35)/3 = 31.67. For February-April: rides 2,3,4 but ride 4 wasn't accepted, so (40+35+0)/3 = 25.00. Later windows have no rides so averages are 0.00.
| driver_id | join_date |
|---|---|
| 1 | 2020-01-01 |
| ride_id | user_id | requested_at |
|---|---|---|
| 1 | 75 | 2020-01-01 |
| ride_id | driver_id | ride_distance | ride_duration |
|---|
| month | average_ride_distance | average_ride_duration |
|---|---|---|
| 1 | 0.00 | 0.00 |
| 2 | 0.00 | 0.00 |
| 3 | 0.00 | 0.00 |
| 4 | 0.00 | 0.00 |
| 5 | 0.00 | 0.00 |
| 6 | 0.00 | 0.00 |
| 7 | 0.00 | 0.00 |
| 8 | 0.00 | 0.00 |
| 9 | 0.00 | 0.00 |
| 10 | 0.00 | 0.00 |
When no rides are accepted, all 3-month windows show 0.00 for both average distance and duration. The query still generates all required windows from month 1 to 10.
Constraints
-
1 ≤ driver_id ≤ 300 -
1 ≤ ride_id ≤ 1000 -
1 ≤ user_id ≤ 500 -
1 ≤ ride_distance ≤ 100 -
1 ≤ ride_duration ≤ 120 - All dates are in 2020
- Each accepted ride exists in the Rides table