WITH peak_temp as (
select z.zone_id,zone_name,
sum(case when YEAR(delivered_time)=2023 and month(delivered_time)=2 then 1 else  0 end) as  peak_2023_02_delivered,
sum(case when YEAR(delivered_time)=2024 and month(delivered_time)=2 then 1 else  0 end) as peak_2024_02_delivered,
sum(case when YEAR(delivered_time)=2024 and month(delivered_time)=1 then 1 else  0 end) as peak_2024_01_delivered,
round(sum(
case when YEAR(delivered_time)=2024 and month(delivered_time)=2 then 
timestampdiff(second,order_time,delivered_time)/60 else 0 end)/
sum(case when YEAR(delivered_time)=2024 and month(delivered_time)=2 then 1 else  0 end)*1,2)  as avg_peak_minutes_2024_02
from Zones  z
left join Orders  o on o.zone_id=z.zone_id 
and ((HOUR(order_time) BETWEEN 11 AND 13) OR (HOUR(order_time) BETWEEN 18 AND 20))
and status='delivered'
group by  z.zone_id,zone_name
),
--   - avg_peak_minutes_2024_02: 2024-02 高峰时段平均送达时长(分钟, ROUND 保留 2 位小数)
--   - top_courier_2024_02: 2024-02 高峰时段该商圈送达订单数最多的骑手姓名(并列取 courier_id 最小者)
delta_temp as (
select zone_id,zone_name,peak_2024_02_delivered - peak_2023_02_delivered as yoy_delta,
peak_2024_02_delivered - peak_2024_01_delivered as mom_delta,
peak_2023_02_delivered,peak_2024_02_delivered,peak_2024_01_delivered,
avg_peak_minutes_2024_02
from peak_temp  pt
),
best_temp as (
select  zone_id,t.courier_id,courier_name ,rank() over(PARTITION BY zone_id order by count_del desc,courier_id) as rn
from (select zone_id,courier_id ,count(*) as count_del from Orders
where ((HOUR(order_time) BETWEEN 11 AND 13) OR (HOUR(order_time) BETWEEN 18 AND 20))
and status='delivered'
and YEAR(delivered_time)=2024 and month(delivered_time)=2
group by zone_id,courier_id
) t
join Couriers c on c.courier_id=t.courier_id
)
select dt.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 delta_temp dt
join best_temp bt on dt.zone_id=bt.zone_id and rn =1 ;