> For the complete documentation index, see [llms.txt](https://zqt0.gitbook.io/leetcode/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://zqt0.gitbook.io/leetcode/sql/1635.hopper-company-queries-i.md). # 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'); ```