with t1 as(
select order_id,o.courier_id,o.zone_id,order_time,delivered_time,status,fee,
    zone_name,zone_type,courier_name,grade
from Orders o
join Zones z on o.zone_id=z.zone_id
join Couriers c on c.courier_id=o.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,
    sum(if(delivered_time like '2023-02%',1,0)) as peak_2023_02_delivered,
    sum(if(delivered_time like '2024-02%',1,0)) as peak_2024_02_delivered,
    sum(if(delivered_time like '2024-01%',1,0)) as peak_2024_01_delivered,
    sum(if(delivered_time like '2024-02%',1,0))-sum(if(delivered_time like '2023-02%',1,0)) as yoy_delta,
    sum(if(delivered_time like '2024-02%',1,0))-sum(if(delivered_time like '2024-01%',1,0)) as mom_delta,
    round(avg(if(delivered_time like '2024-02%',timestampdiff(minute,order_time,delivered_time),null)),2) as avg_peak_minutes_2024_02
from t1
group by zone_id,zone_name),
t3 as(
select zone_name,courier_name
from(select
        zone_name,courier_name,
        dense_rank() over(partition by zone_name order by count(order_id) desc,courier_id asc) rk
    from t1
    where delivered_time like '2024-02%'
    group by zone_name,order_id,courier_id) a
where rk=1)

select distinct zone_id,t2.zone_name,peak_2023_02_delivered,peak_2024_02_delivered,peak_2024_01_delivered,yoy_delta,mom_delta,avg_peak_minutes_2024_02,t3.courier_name top_courier_2024_02
from t2,t3
where t2.zone_name=t3.zone_name
order by zone_id,t2.zone_name