Leetcode 1635. Hopper 公司查询 I

1.题目基本信息

1.1.题目描述

表: 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 年每个月的以下统计信息：

• 截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。
• 该月接受的乘车次数（accepted_rides）。

返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。

1.2.题目地址

https://leetcode.cn/problems/hopper-company-queries-i/description/

2.解题方法

2.1.解题思路

请参考步骤部分

2.2.解题步骤

第一步，通过递归构建月份表months

第二步，根据各个"%Y%m"的大小关系将months和Drivers表格连接起来，并根据month分组聚合统计数量，获取每个月在职司机数active_drivers

第三步，将Rides和AcceptedRides通过ride_id连接起来，过滤出成功接收的记录，并统计各个月份数量

第四步，将T和S表通过month字段连接即为题解

3.解题代码

sql代码

# Write your MySQL query statement below

# 第一步，通过递归构建月份表months
WITH RECURSIVE months AS (
    SELECT 1 AS month 
        UNION 
    SELECT month + 1 AS month FROM months WHERE month <= 11
), T AS (
    # 第二步，根据各个"%Y%m"的大小关系将months和Drivers表格连接起来，并根据month分组聚合统计数量，获取每个月在职司机数active_drivers
    SELECT 
        months.month, 
        COUNT(join_date) AS active_drivers
    FROM 
        months 
            LEFT JOIN 
        Drivers 
        ON CONCAT('2020', LPAD(months.month, 2, '0')) >= DATE_FORMAT(Drivers.join_date, '%Y%m')
    GROUP BY months.month
), S AS (
    # 第三步，将Rides和AcceptedRides通过ride_id连接起来，过滤出成功接收的记录，并统计各个月份数量
    SELECT 
        MONTH(requested_at) AS month, 
        COUNT(*) AS accepted_rides
    FROM 
        Rides LEFT JOIN AcceptedRides USING (ride_id)
    WHERE driver_id IS NOT NULL AND YEAR(requested_at) = 2020
    GROUP BY month
)
# 第四步，将T和S表通过month字段连接即为题解
SELECT 
    month, 
    active_drivers, 
    IFNULL(accepted_rides, 0) AS accepted_rides
FROM 
    T LEFT JOIN S USING (month)

4.执行结果