You are given three tables for a ride-sharing company:
Drivers table contains information about drivers and when they joined the company.
Rides table contains all ride requests (both accepted and rejected).
AcceptedRides table contains details about rides that were actually accepted by drivers.
Write a SQL query to calculate the percentage of working drivers for each month of 2020, where:
- Available drivers in a month = drivers who joined on or before that month
- Working drivers in a month = available drivers who accepted at least one ride during that month
- Working percentage = (working drivers / available drivers) × 100
If no drivers are available in a month, consider the working percentage to be 0.
Return results ordered by month (1=January, 2=February, etc.) and round the percentage to 2 decimal places.
Table Schema
| Column Name | Type | Description |
|---|---|---|
driver_id
PK
|
int | Unique driver identifier |
join_date
|
date | Date when driver joined the company |
| Column Name | Type | Description |
|---|---|---|
ride_id
PK
|
int | Unique ride identifier |
user_id
|
int | ID of user who requested the ride |
requested_at
|
date | Date when ride was requested |
| Column Name | Type | Description |
|---|---|---|
ride_id
PK
|
int | Unique accepted ride identifier |
driver_id
|
int | ID of 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 |
|---|---|
| 10 | 2019-12-10 |
| 8 | 2020-01-13 |
| 5 | 2020-02-16 |
| 7 | 2020-03-08 |
| 4 | 2020-05-17 |
| 1 | 2020-10-24 |
| 6 | 2021-01-05 |
| ride_id | user_id | requested_at |
|---|---|---|
| 6 | 75 | 2020-01-01 |
| 1 | 54 | 2020-02-11 |
| 10 | 63 | 2020-03-07 |
| 19 | 39 | 2020-04-30 |
| 3 | 41 | 2020-06-21 |
| 13 | 52 | 2020-07-20 |
| 7 | 69 | 2020-08-03 |
| 17 | 70 | 2020-08-17 |
| 20 | 81 | 2020-11-02 |
| 5 | 57 | 2020-11-09 |
| 2 | 42 | 2020-12-04 |
| 11 | 68 | 2020-12-17 |
| 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 |
| 11 | 8 | 37 | 43 |
| month | working_percentage |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 25 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 20 |
| 8 | 40 |
| 9 | 0 |
| 10 | 0 |
| 11 | 33.33 |
| 12 | 33.33 |
For January 2020: Available drivers = 2 (driver 10 joined in 2019, driver 8 joined in January). Working drivers = 1 (only driver 10 accepted ride 10). Percentage = 1/2 * 100 = 50.00%
For March 2020: Available drivers = 3 (drivers 10, 8, 5). Working drivers = 1 (only driver 10 accepted ride 10). Percentage = 1/3 * 100 = 33.33%
| driver_id | join_date |
|---|---|
| 1 | 2021-01-01 |
| ride_id | user_id | requested_at |
|---|---|---|
| 1 | 50 | 2020-01-01 |
| ride_id | driver_id | ride_distance | ride_duration |
|---|
| month | working_percentage |
|---|---|
| 1 | 0.00 |
| 2 | 0.00 |
| 3 | 0.00 |
| 4 | 0.00 |
| 5 | 0.00 |
| 6 | 0.00 |
| 7 | 0.00 |
| 8 | 0.00 |
| 9 | 0.00 |
| 10 | 0.00 |
| 11 | 0.00 |
| 12 | 0.00 |
Since the only driver joined in 2021, there are no available drivers in any month of 2020. The working percentage is 0.00% for all months.
Constraints
-
1 ≤ driver_id ≤ 10^6 -
1 ≤ ride_id ≤ 10^6 -
1 ≤ user_id ≤ 10^6 -
join_dateis a valid date -
requested_atis a valid date -
ride_distance ≥ 1 -
ride_duration ≥ 1