题解 | 统计骑手信息

with t1 as (
select
    zone_id
    ,zone_name
    ,count(
        case when left(order_time,7) = '2023-02' and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id 
        end
    ) as peak_2023_02_delivered
    ,count(
        case when (left(order_time,7) = '2024-02') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id 
        end
    ) as peak_2024_02_delivered
    ,count(
        case when (left(order_time,7) = '2024-01') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id 
        end
    ) as peak_2024_01_delivered
    ,count(
        case when (left(order_time,7) = '2024-02') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id end)
        -
    count(
        case when left(order_time,7) = '2023-02' and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id end)
    as yoy_delta 
    ,count(
        case when (left(order_time,7) = '2024-02') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id end)
        -
    count(
        case when (left(order_time,7) = '2024-01') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then order_id end)
    as mom_delta
    ,round(avg(
        case when (left(order_time,7) = '2024-02') and (hour(order_time) between 11 and 13 or hour(order_time) between 18 and 20)
        then timestampdiff(minute,order_time,delivered_time)
        end
    ),2) as avg_peak_minutes_2024_02 
from
    Orders o join Zones z using(zone_id)
    join Couriers c using(courier_id)
group by zone_id,zone_name
),
t2 as (
select 
    zone_id
    ,zone_name
    ,courier_name
    ,row_number() over (partition by zone_id,zone_name order by count(*) desc,courier_id) as rk
from
    Orders o join Zones z using(zone_id)
    join Couriers c using(courier_id)
group by
    zone_id,zone_name,courier_name,courier_id
)
select t1.zone_id
        ,t1.zone_name
        ,t1.peak_2023_02_delivered
        ,t1.peak_2024_02_delivered
        ,t1.peak_2024_01_delivered
        ,t1.yoy_delta
        ,t1.mom_delta
        ,t1.avg_peak_minutes_2024_02
        ,t2.courier_name as top_courier_2024_02 
from t1 left join t2 using(zone_id,zone_name)
where t2.rk = 1