1645. Hopper 公司查询 II

表: Drivers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id 是该表具有唯一值的列。
该表的每一行均包含驾驶员的ID以及他们加入 Hopper 公司的日期。

 

表: Rides

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id 是该表具有唯一值的列。
该表的每一行均包含行程 ID(ride_id)，用户 ID(user_id) 以及该行程的日期 (requested_at)。
该表中可能有一些不被接受的乘车请求。

 

表: AcceptedRides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id 是该表具有唯一值的列。
该表的每一行都包含已接受的行程信息。
表中的行程信息都在 "Rides" 表中存在。

 

编写解决方案以报告 2020 年每个月的工作驱动因素 百分比（working_percentage），其中：

 

注意：如果一个月内可用驾驶员的数量为零，我们认为 working_percentage 为 0。

返回按 month 升序 排列的结果表，其中 month 是月份的编号（一月是 1，二月是 2，等等）。将 working_percentage 四舍五入至 小数点后两位。

结果格式如下例所示。

 

示例 1:

输入：
表 Drivers:
+-----------+------------+
| driver_id | join_date  |
+-----------+------------+
| 10        | 2019-12-10 |
| 8         | 2020-1-13  |
| 5         | 2020-2-16  |
| 7         | 2020-3-8   |
| 4         | 2020-5-17  |
| 1         | 2020-10-24 |
| 6         | 2021-1-5   |
+-----------+------------+

表 Rides:
+---------+---------+--------------+
| ride_id | user_id | requested_at |
+---------+---------+--------------+
| 6       | 75      | 2019-12-9    |
| 1       | 54      | 2020-2-9     |
| 10      | 63      | 2020-3-4     |
| 19      | 39      | 2020-4-6     |
| 3       | 41      | 2020-6-3     |
| 13      | 52      | 2020-6-22    |
| 7       | 69      | 2020-7-16    |
| 17      | 70      | 2020-8-25    |
| 20      | 81      | 2020-11-2    |
| 5       | 57      | 2020-11-9    |
| 2       | 42      | 2020-12-9    |
| 11      | 68      | 2021-1-11    |
| 15      | 32      | 2021-1-17    |
| 12      | 11      | 2021-1-19    |
| 14      | 18      | 2021-1-27    |
+---------+---------+--------------+

表 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            |
| 11      | 8         | 37            | 43            |
| 15      | 8         | 108           | 82            |
| 12      | 8         | 38            | 34            |
| 14      | 1         | 90            | 74            |
+---------+-----------+---------------+---------------+
输出：
+-------+--------------------+
| month | working_percentage |
+-------+--------------------+
| 1     | 0.00               |
| 2     | 0.00               |
| 3     | 25.00              |
| 4     | 0.00               |
| 5     | 0.00               |
| 6     | 20.00              |
| 7     | 20.00              |
| 8     | 20.00              |
| 9     | 0.00               |
| 10    | 0.00               |
| 11    | 33.33              |
| 12    | 16.67              |
+-------+--------------------+
解释：
截至 1 月底 --> 2 个活跃的驾驶员 (10, 8)，无被接受的行程。百分比是0%。
截至 2 月底 --> 3 个活跃的驾驶员 (10, 8, 5)，无被接受的行程。百分比是0%。
截至 3 月底 --> 4 个活跃的驾驶员 (10, 8, 5, 7)，1 个被接受的行程 (10)。百分比是 (1 / 4) * 100 = 25%。
截至 4 月底 --> 4 个活跃的驾驶员 (10, 8, 5, 7)，无被接受的行程。百分比是 0%。
截至 5 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，无被接受的行程。百分比是 0%。
截至 6 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (10)。 百分比是 (1 / 5) * 100 = 20%。
截至 7 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (8)。百分比是 (1 / 5) * 100 = 20%。
截至 8 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (7)。百分比是 (1 / 5) * 100 = 20%。
截至 9 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，无被接受的行程。百分比是 0%。
截至 10 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1) 无被接受的行程。百分比是 0%。
截至 11 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1)，2 个被接受的行程 (1, 7)。百分比是 (2 / 6) * 100 = 33.33%。
截至 12 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1)，1 个被接受的行程 (4)。百分比是 (1 / 6) * 100 = 16.67%。

# Write your MySQL query statement below
with t1 as (
    select 1 mon 
    union select 2 union select 3 union select 4 union 
    select 5 union select 6 union select 7 union select 8 union 
    select 9 union select 10 union select 11 union select 12
    )
,t2 as (
    select 1 mon
        ,count(driver_id) active_drivers 
    from Drivers
    where datediff('2020-01-31',join_date)>=0
    union all
    select month(join_date) mon
        ,count(driver_id) active_drivers 
    from Drivers
    where datediff('2020-01-31',join_date)<0
    and datediff('2020-12-31',join_date)>=0
    group by 1
    )
,t3 as (
    select month(requested_at) mon 
        ,count(distinct t5.driver_id) accepted_drivers 
    from Rides t4 
    inner join AcceptedRides t5 
    on t4.ride_id = t5.ride_id
    where left(requested_at,4) = '2020'
    group by 1
    )
select t1.mon `month`
   ,round(
   ifnull(
    ifnull(accepted_drivers,0)
    /sum(ifnull(active_drivers,0)) over (order by t1.mon) * 100
    ,0
   ),2
   ) working_percentage
from t1
left join t2 on t1.mon = t2.mon 
left join t3 on t1.mon = t3.mon