with
t1 as(
    select
        zone_id,
        zone_name,
        courier_id,
        courier_name,
        round(timestampdiff(second,order_time,delivered_time)/60,2) as deliver_time,
        date_format(delivered_time,'%Y%m') as mdate
    from
        Orders
        left join Zones using(zone_id)
        left join Couriers using(courier_id)
    where
        (
            hour(order_time) between 11 and 13
            or
            hour(order_time) between 18 and 20
        )
        and
        status='delivered'
)
,
# 求送单字段
t2 as(
    select
        zone_id,
        zone_name,
        count(if(mdate='202302',1,null)) as peak_2023_02_delivered,
        count(if(mdate='202402',1,null)) as peak_2024_02_delivered,
        count(if(mdate='202401',1,null)) as peak_2024_01_delivered
    from
        t1
    group by
        zone_id,
        zone_name
)
,
# 求202402快递员排名
t3 as(
    select
        zone_name,
        courier_id,
        courier_name,
        dense_rank()over(partition by zone_name order by count(courier_name) desc,courier_id) as peak_rank
    from
        t1
    where
        mdate='202402'
    group by
        zone_name,
        courier_id,
        courier_name
)
,
# 筛选最高排名快递员
t4 as(
    select
        zone_name,
        courier_name
    from
        t3
    where
        peak_rank=1
)
,
# 202402平均送单时间
t5 as(
    select
        zone_name,
        round(avg(deliver_time),2) as avg_peak_minutes_2024_02
    from
        t1
    where
        mdate='202402'
    group by
        zone_name
)
,
t6 as(
    select
        zone_id,
        zone_name,
        peak_2023_02_delivered,
        peak_2024_02_delivered,
        peak_2024_01_delivered,
        peak_2024_02_delivered-peak_2023_02_delivered as yoy_delta,
        peak_2024_02_delivered-peak_2024_01_delivered as mom_delta,
        avg_peak_minutes_2024_02,
        courier_name as top_courier_2024_02
    from
        t2
        left join t5 using(zone_name)
        left join t4 using(zone_name)
    order by
        zone_id,
        zone_name
)

select * from t6