LeetCode（数据库）- Hopper 公司查询 I

WITH RECURSIVE cal AS(
    SELECT 1 month
    UNION ALL
    SELECT month + 1 FROM cal WHERE month < 12
),
# 计算 active_drivers
t1 AS(SELECT month, COUNT(*) active_drivers
FROM cal LEFT JOIN Drivers ON DATEDIFF(CONCAT('2020-', month, '-1'), DATE_FORMAT(join_date, '%Y-%m-1')) >= 0
WHERE join_date < '2021-1-1'
GROUP BY month),
# 计算 accepted_rides
t2 AS(SELECT month, COUNT(IF(month = MONTH(requested_at), 1, NULL)) accepted_rides
FROM cal LEFT JOIN Drivers ON DATEDIFF(CONCAT('2020-', month, '-1'), DATE_FORMAT(join_date, '%Y-%m-1')) >= 0 
LEFT JOIN (Rides JOIN AcceptedRides USING(ride_id)) USING(driver_id) 
WHERE requested_at BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY month),
# 合并
t3 AS(SELECT month, active_drivers, 0 accepted_rides FROM t1
UNION ALL
SELECT month, 0 active_drivers, accepted_rides FROM t2
UNION ALL
SELECT month, 0 active_drivers, 0 accepted_rides FROM cal)
SELECT month, SUM(active_drivers) active_drivers, SUM(accepted_rides) accepted_rides
FROM t3
GROUP BY month
ORDER BY month