#每个商圈在 2024 年 2 月的高峰时段“已送达订单数”和“平均送达时长（分钟），
#每个商圈在  2024 年 1 月、2023 年 2 月的高峰时段“已送达订单数”
#2024 年 2 月每个商圈“高峰已送达订单数”最多的骑手（若并列按骑手ID升序

#对于1，2项可以一次查询
with order_nums as 
(
select h1.zone_id,h1.zone_name,h2.peak_2023_02_delivered,h2.peak_2024_02_delivered,h2.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
from 
Zones as h1 
left outer join
(select zone_id,
sum(if(substr(date(delivered_time),1,7)='2024-02' and status='delivered',1,0)) as peak_2024_02_delivered,
sum(if(substr(date(delivered_time),1,7)='2023-02' and status='delivered',1,0)) as peak_2023_02_delivered,
sum(if(substr(date(delivered_time),1,7)='2024-01' and status='delivered',1,0)) as peak_2024_01_delivered,

round(avg(if(substr(date(delivered_time),1,7)='2024-02' and status='delivered', timestampdiff(MINUTE, order_time, delivered_time), null)) ,2)as avg_peak_minutes_2024_02
from Orders
where (hour(delivered_time)) between 11 and 13 or 
(hour(delivered_time)) between 18 and 20
group by zone_id
) as h2 on h1.zone_id=h2.zone_id
),

courier_info as 
(
select a1.zone_id,a2.courier_id,a2.courier_name
from
(select zone_id,courier_id,count(*) as order_num,
dense_rank()over(partition by zone_id order by count(*) desc,courier_id asc) as rnk
from Orders
where 
substr(date(delivered_time),1,7)='2024-02' 
and status='delivered'
and (
    (hour(delivered_time)) between 11 and 13 or 
(hour(delivered_time)) between 18 and 20
)
group by zone_id, courier_id
) as a1
left join
Couriers as a2 on a1.courier_id=a2.courier_id
where a1.rnk=1 
)

select b1.zone_id,b1.zone_name,b1.peak_2023_02_delivered,b1.peak_2024_02_delivered,b1.peak_2024_01_delivered,
b1.yoy_delta,b1.mom_delta,b1.avg_peak_minutes_2024_02,b2.courier_name as top_courier_2024_02
from order_nums as b1
left join
courier_info as b2 on b1.zone_id=b2.zone_id