题解 | 统计骑手信息

with maxorders as (
    select zone_id, courier_name
    from (
        select zone_id, courier_id, 
            rank() over(partition by zone_id order by count(*) desc, courier_id asc) as rn
        from Orders
        where (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20) and date_format(order_time, "%Y-%m") = '2024-02' and status = 'delivered'
        group by zone_id, courier_id
    ) t
    join Couriers c on c.courier_id = t.courier_id
    where rn = 1
)
select t.zone_id, zone_name, peak_2023_02_delivered, peak_2024_02_delivered,
    peak_2024_01_delivered, yoy_delta, mom_delta,
    avg_peak_minutes_2024_02, courier_name as top_courier_2024_02
from (
select z.zone_id, z.zone_name,
    count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20) and date_format(order_time, "%Y-%m") = '2023-02' and status = 'delivered', 1, null)) as peak_2023_02_delivered,
    count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)  and date_format(order_time, "%Y-%m") = '2024-02' and status = 'delivered', 1, null)) as peak_2024_02_delivered,
    count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)  and date_format(order_time, "%Y-%m") = '2024-01' and status = 'delivered', 1, null)) as peak_2024_01_delivered,
    count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)  and date_format(order_time, '%Y-%m') = '2024-02' and status = 'delivered', 1, null)) - count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20) and date_format(order_time, "%Y-%m") = '2023-02' and status = 'delivered', 1, null)) as yoy_delta,
    count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)  and date_format(order_time, '%Y-%m') = '2024-02' and status = 'delivered', 1, null)) - count(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20) and date_format(order_time, "%Y-%m") = '2024-01' and status = 'delivered', 1, null)) as mom_delta,
    round(avg(if((hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20) and date_format(order_time, '%Y-%m') = '2024-02' and status = 'delivered', TIMESTAMPDIFF(MINUTE, order_time, delivered_time), null)), 2) as avg_peak_minutes_2024_02
from Orders o
join Zones z on o.zone_id = z.zone_id
group by o.zone_id
) t
join maxorders m on m.zone_id = t.zone_id
order by t.zone_id asc, zone_name asc