--比较容易理解的拆解方法。

with tiaojian as (
select 
zs.zone_id,
zs.zone_name,
count(distinct case when date_format(delivered_time,"%Y%m")=202302 then o.order_id end ) as peak_2023_02_delivered,
count(distinct case when date_format(delivered_time,"%Y%m")=202402 then o.order_id end ) as peak_2024_02_delivered,
count(distinct case when date_format(delivered_time,"%Y%m")=202401 then o.order_id end ) as 
peak_2024_01_delivered,
round(
avg(case when date_format(delivered_time,"%Y%m")=202402 then timestampdiff(MINUTE, order_time, delivered_time) end),2) as avg_peak_minutes_2024_02
from Orders o inner join Couriers cs on 
o.courier_id=cs.courier_id
 inner join Zones zs on zs.zone_id=o.zone_id
 where
 (
 hour(delivered_time) between 11 and 13
 or 
 hour(delivered_time) between 18 and 20
 ) and 
 status='delivered' 
 group by zs.zone_id,zs.zone_name
),tiaojian1 as (
select 
t.zone_id,
t.courier_name
from(
select 
zs.zone_id,
courier_name,
zone_name, 
row_number()over(partition by zs.zone_id  order by count(o.order_id) desc,o.courier_id asc) as py
from Orders o inner join Couriers cs on 
o.courier_id=cs.courier_id
 inner join Zones zs on zs.zone_id=o.zone_id
 where
 (
 hour(delivered_time) between 11 and 13
 or 
 hour(delivered_time) between 18 and 20
 ) and 
 status='delivered' 
 and 
 date_format(delivered_time,"%Y%m")=202402
 group by cs.courier_id, zs.zone_id, cs.courier_name, zs.zone_name
) as t 
where
t.py=1
)



select
t.zone_id,
t.zone_name,
t.peak_2023_02_delivered,
t.peak_2024_02_delivered,
t.peak_2024_01_delivered,
t.peak_2024_02_delivered-t.peak_2023_02_delivered as yoy_delta,
t.peak_2024_02_delivered-t.peak_2024_01_delivered as mom_delta,
t.avg_peak_minutes_2024_02 ,
t1.courier_name as top_courier_2024_02
from  tiaojian t inner join tiaojian1 t1 
on t.zone_id=t1.zone_id