# 1635.(Hard)Hopper 公司查询 I 表: 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”表中存在。 ``` 编写SQL查询以报告2020年每个月的以下统计信息： 截至某月底，当前在Hopper公司工作的驾驶员数量（active\_drivers）。 该月接受的乘车次数（accepted\_rides）。 返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。 查询结果格式如下例所示。 ``` 表 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 | active_drivers | accepted_rides | +-------+----------------+----------------+ | 1 | 2 | 0 | | 2 | 3 | 0 | | 3 | 4 | 1 | | 4 | 4 | 0 | | 5 | 5 | 0 | | 6 | 5 | 1 | | 7 | 5 | 1 | | 8 | 5 | 1 | | 9 | 5 | 0 | | 10 | 6 | 0 | | 11 | 6 | 2 | | 12 | 6 | 1 | +-------+----------------+----------------+ 截至1月底->两个活跃的驾驶员（10,8），没有被接受的行程。 截至2月底->三个活跃的驾驶员（10,8,5），没有被接受的行程。 截至3月底->四个活跃的驾驶员（10,8,5,7），一个被接受的行程（10）。 截至4月底->四个活跃的驾驶员（10,8,5,7），没有被接受的行程。 截至5月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。 截至6月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（13）。 截至7月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（7）。 截至8月底->五个活跃的驾驶员（10,8,5,7,4），一位接受的行程（17）。 截至9月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。 截至10月底->六个活跃的驾驶员（10,8,5,7,4,1），没有被接受的行程。 截至11月底->六个活跃的驾驶员（10,8,5,7,4,1），两个被接受的行程（20,5）。 截至12月底->六个活跃的驾驶员（10,8,5,7,4,1），一个被接受的行程（2）。 ``` 来源：力扣（LeetCode） 链接： 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。 ## Solution 1. `tm` 表生成 '2020-01' 至 '2020-12'的序列作为主表依次left join上另外3个表 2. `LEFT JOIN Drivers` 的条件是 `<=` 年月（本月**之前**） 3. `LEFT JOIN Rides` 的条件是 `=` 年月（本月**之内**） ```sql with recursive t AS ( SELECT 1 as month UNION SELECT month+1 FROM t WHERE month<12 ), tm as ( SELECT month, LEFT(DATE_ADD('2019-12-01',INTERVAL month MONTH),7) ym from t ) SELECT month, count(DISTINCT Drivers.driver_id) active_drivers, count(DISTINCT AcceptedRides.ride_id) accepted_rides from tm LEFT JOIN Drivers on DATE_FORMAT(join_date,'%Y-%m')<= ym LEFT JOIN Rides on DATE_FORMAT(requested_at,'%Y-%m') = ym LEFT JOIN AcceptedRides using(ride_id) GROUP BY month; ``` ## Table Schema ```sql Create table If Not Exists Drivers (driver_id int, join_date date); Create table If Not Exists Rides (ride_id int, user_id int, requested_at date); Create table If Not Exists AcceptedRides (ride_id int, driver_id int, ride_distance int, ride_duration int); Truncate table Drivers; insert into Drivers (driver_id, join_date) values ('10', '2019-12-10'); insert into Drivers (driver_id, join_date) values ('8', '2020-1-13'); insert into Drivers (driver_id, join_date) values ('5', '2020-2-16'); insert into Drivers (driver_id, join_date) values ('7', '2020-3-8'); insert into Drivers (driver_id, join_date) values ('4', '2020-5-17'); insert into Drivers (driver_id, join_date) values ('1', '2020-10-24'); insert into Drivers (driver_id, join_date) values ('6', '2021-1-5'); Truncate table Rides; insert into Rides (ride_id, user_id, requested_at) values ('6', '75', '2019-12-9'); insert into Rides (ride_id, user_id, requested_at) values ('1', '54', '2020-2-9'); insert into Rides (ride_id, user_id, requested_at) values ('10', '63', '2020-3-4'); insert into Rides (ride_id, user_id, requested_at) values ('19', '39', '2020-4-6'); insert into Rides (ride_id, user_id, requested_at) values ('3', '41', '2020-6-3'); insert into Rides (ride_id, user_id, requested_at) values ('13', '52', '2020-6-22'); insert into Rides (ride_id, user_id, requested_at) values ('7', '69', '2020-7-16'); insert into Rides (ride_id, user_id, requested_at) values ('17', '70', '2020-8-25'); insert into Rides (ride_id, user_id, requested_at) values ('20', '81', '2020-11-2'); insert into Rides (ride_id, user_id, requested_at) values ('5', '57', '2020-11-9'); insert into Rides (ride_id, user_id, requested_at) values ('2', '42', '2020-12-9'); insert into Rides (ride_id, user_id, requested_at) values ('11', '68', '2021-1-11'); insert into Rides (ride_id, user_id, requested_at) values ('15', '32', '2021-1-17'); insert into Rides (ride_id, user_id, requested_at) values ('12', '11', '2021-1-19'); insert into Rides (ride_id, user_id, requested_at) values ('14', '18', '2021-1-27'); Truncate table AcceptedRides; insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('10', '10', '63', '38'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('13', '10', '73', '96'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('7', '8', '100', '28'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('17', '7', '119', '68'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('20', '1', '121', '92'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('5', '7', '42', '101'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('2', '4', '6', '38'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('11', '8', '37', '43'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('15', '8', '108', '82'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('12', '8', '38', '34'); insert into AcceptedRides (ride_id, driver_id, ride_distance, ride_duration) values ('14', '1', '90', '74'); ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://zqt0.gitbook.io/leetcode/sql/1635.hopper-company-queries-i.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.