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

我的代码

with recursive t(n) as (
    select 1
    union all
    select n+1 from t where n<12
)

select a.m month,a.id active_drivers,case when b.cnt is not null then b.cnt else 0 end accepted_rides from (
select a.m,a.id from (
select a.m,convert(a.id,unsigned integer) id,row_number() over(partition by a.m order by a.id desc) id1 from(
select a.y m,case when  b.id is not null then @id:=b.id else @id end id from(
select n y from t)a left join(
select year(join_date)y,month(join_date)m,sum(case when join_date<='2020-12-31' then 1 else 0 end) over(order by join_date) id
from Drivers 
where year(join_date)<=2020
)b on a.y=b.m and b.y=2020
,(select @id:=0) as init 
)a 
)a where a.id1=1
)a left join (
select month(a.requested_at) m,count(*)cnt 
from(
select * from Rides where year(requested_at)=2020
)a left join AcceptedRides b on a.ride_id=b.ride_id
where b.ride_id is not null
group by month(a.requested_at)
)b on a.m=b.m

大神的代码：

报告2020年每个月的以下统计信息：1) 截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）

2) 该月接受的乘车次数（accepted_rides）。

WITH RECURSIVE
    mon_time AS (
        -- 递归生成月份
        SELECT 1 AS month
        UNION ALL
        SELECT month + 1
        FROM mon_time
        WHERE month <= 11),
    active_driver AS (
        -- 生成每个月激活的司机数，2020年以前的归到1月份
        SELECT IF(YEAR(d.join_date) < '2020', 1, MONTH(d.join_date)) AS `month`,
               COUNT(*)                                              AS `driver`
        FROM Drivers d
        WHERE YEAR(d.join_date) < '2021'
        GROUP BY IF(YEAR(d.join_date) < '2020', 1, MONTH(d.join_date)) ),
    accepted_ride AS (
        -- 计算每个月的订单
        SELECT MONTH(r.requested_at) AS `month`, COUNT(*) AS `order`
        FROM Rides r
                 JOIN AcceptedRides a ON r.ride_id = a.ride_id
        WHERE YEAR(r.requested_at) = '2020'
        GROUP BY MONTH(r.requested_at)) 

SELECT a.month,
       IFNULL(SUM(b.driver) OVER (ORDER BY a.month), 0) AS `active_drivers`,
       IFNULL(c.`order`, 0)                             AS `accepted_rides`
FROM mon_time a
         LEFT JOIN active_driver b ON a.month = b.month
         LEFT JOIN accepted_ride c ON a.month = c.month