526互联

【LeetCode 1635. Hopper 公司查询 I】with recursive生成2020年每月的最后一天

发布时间 2024-01-12 11:34:04作者: yhm138

题目地址

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

代码

-- CTE生成2020年每月的最后一天
WITH RECURSIVE months AS (
  SELECT LAST_DAY('2019-12-01') AS month_end -- 初始日期的前一个月的最后一天
  UNION ALL
  SELECT LAST_DAY(month_end + INTERVAL 1 DAY) -- 下一个月的最后一天
  FROM months
  WHERE month_end < '2020-12-01' -- 递归结束条件
)
,
months2020 as (
    SELECT *, DATE_FORMAT(month_end, '%Y-%m') AS `month` -- 格式化日期，只保留年月
    FROM months
    where DATE_FORMAT(month_end, '%Y')="2020"
)
,
answer_part1 as (
    SELECT  tmp1.`month`, count(distinct driver_id) as active_drivers from
    (SELECT months2020.*, d.driver_id 
    from months2020
    left join Drivers d 
    on d.join_date <= months2020.month_end) tmp1
    group by tmp1.`month`
)
,
answer_part2 as(
    SELECT months2020.`month`, count(distinct ar.ride_id) as accepted_rides
    from   months2020
    left join Rides r 
    on months2020.`month`=DATE_FORMAT(r.requested_at,"%Y-%m")
    join AcceptedRides ar
    on ar.ride_id=r.ride_id
    group by months2020.`month`
)


SELECT cast(right(m2.`month`,2) as unsigned int) as `month`
,ifnull(ap1.active_drivers ,0) as active_drivers
,ifnull(ap2.accepted_rides ,0) as accepted_rides
from months2020 m2
left join answer_part1 ap1  on ap1.`month`= m2.`month` 
left join answer_part2 ap2  on ap2.`month`= m2.`month`