Leetcode SQL (#1635, #1645, #1661)

select 
d.month_num as month, 
ifnull(d.active_drivers,0) as active_drivers, 
ifnull(a.accepted_rides,0) as accepted_rides
from(
    select 
    m.month,  
    m.month_num, 
    count(distinct d.driver_id) as active_drivers
    from(
        select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
        union
        select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
        union
        select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
        union
        select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
        union
        select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
        union
        select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
        union
        select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
        union
        select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
        union
        select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
        union
        select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
        union
        select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
        union
        select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
    ) m
    left join (
        select 
        driver_id, 
        DATE_FORMAT(join_date,'%Y-%m') as join_month
        from Drivers
    ) d 
        on m.month >= d.join_month
    group by 1,2
) d
left join (
    select 
    DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
    count(distinct r.ride_id) as accepted_rides
    from Rides r
        join AcceptedRides a 
            on r.ride_id = a.ride_id
    group by 1
    having count(distinct r.ride_id) > 0
) a 
    on d.month = a.ride_month
order by 1

select 
d.month_num as month, 
case 
    when ifnull(d.active_drivers,0) = 0 then round(0,2)
    when ifnull(d.active_drivers,0) >0 then round((ifnull(a.accepted_rides,0)/ifnull(d.active_drivers,0))*100,2) 
end as working_percentage
from(
    select 
    m.month,  
    m.month_num, 
    count(distinct d.driver_id) as active_drivers
    from(
        select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
        union
        select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
        union
        select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
        union
        select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
        union
        select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
        union
        select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
        union
        select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
        union
        select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
        union
        select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
        union
        select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
        union
        select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
        union
        select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
    ) m
    left join (
        select 
        driver_id, 
        DATE_FORMAT(join_date,'%Y-%m') as join_month
        from Drivers
    ) d 
        on m.month >= d.join_month
    group by 1,2
) d
left join (
    select 
    DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
    count(distinct case when r.ride_id is not null then a.driver_id else null end) as accepted_rides
    from Rides r
        join AcceptedRides a 
            on r.ride_id = a.ride_id
    group by 1
    having count(distinct r.ride_id) >0
) a 
    on d.month = a.ride_month
order by 1

select 
month,
average_ride_distance,
average_ride_duration
from(  
    select 
    month,
    round(avg(ride_distance) over(order by month asc rows between current row and 2 following),2) as average_ride_distance,
    round(avg(ride_duration) over(order by month asc rows between current row and 2 following),2) as average_ride_duration
    from(
        select 
        b.month_num as month, 
        sum(ride_distance) as ride_distance,
        sum(ride_duration) as ride_duration
        from(
            select 
            m.month,  
            m.month_num, 
            d.driver_id, 
            ifnull(r.ride_distance,0) as ride_distance, 
            ifnull(r.ride_duration,0) as ride_duration 
            from(
                select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
                union
                select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
                union
                select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
                union
                select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
                union
                select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
                union
                select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
                union
                select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
                union
                select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
                union
                select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
                union
                select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
                union
                select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
                union
                select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
            ) m
            left join (
                select 
                driver_id, 
                DATE_FORMAT(join_date,'%Y-%m') as join_month
                from Drivers
            ) d 
                on m.month >= d.join_month
            left join (
                select 
                a.driver_id, 
                DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
                sum(a.ride_distance) as ride_distance,
                sum(a.ride_duration) as ride_duration
                from AcceptedRides a
                    left join Rides r 
                        on a.ride_id = r.ride_id
                group by 1,2
            ) r 
                on (m.month = r.ride_month and d.driver_id = r.driver_id )
        ) b
        group by 1
    ) c
) d
where month <= 10
order by 1