题解 | 统计骑手信息

with
    temp0 as (
        select
            z.zone_id,
            count(*) as peak_2024_02_delivered,
            round(
                sum(timestampdiff(minute, order_time, delivered_time)) / count(*),
                2
            ) as avg_peak_minutes_2024_02
        from
            Zones z
            inner join Orders o on z.zone_id = o.zone_id
        where
            year(delivered_time) = 2024
            and month(delivered_time) = 2
            and (
                hour(delivered_time) >= 11
                and hour(delivered_time) <= 13
                or hour(delivered_time) >= 18
                and hour(delivered_time) <= 20
            )
            and status = "delivered"
        group by
            z.zone_id
    ),
    temp1 as (
        select
            z.zone_id,
            count(*) as peak_2024_01_delivered
        from
            Zones z
            inner join Orders o on z.zone_id = o.zone_id
        where
            year(delivered_time) = 2024
            and month(delivered_time) = 1
            and (
                hour(delivered_time) >= 11
                and hour(delivered_time) <= 13
                or hour(delivered_time) >= 18
                and hour(delivered_time) <= 20
            )
            and status = "delivered"
        group by
            z.zone_id
    ),
    temp2 as (
        select
            z.zone_id,
            count(*) as peak_2023_02_delivered
        from
            Zones z
            inner join Orders o on z.zone_id = o.zone_id
        where
            year(delivered_time) = 2023
            and month(delivered_time) = 2
            and (
                hour(delivered_time) >= 11
                and hour(delivered_time) <= 13
                or hour(delivered_time) >= 18
                and hour(delivered_time) <= 20
            )
            and status = "delivered"
        group by
            z.zone_id
    ),
    temp3 as (
        select
            z.zone_id,
            count(*) as courier_count,
            c.courier_name
        from
            Zones z
            inner join Orders o on z.zone_id = o.zone_id
            inner join Couriers c on c.courier_id = o.courier_id
        where
            year(delivered_time) = 2024
            and month(delivered_time) = 2
            and (
                hour(delivered_time) >= 11
                and hour(delivered_time) <= 13
                or hour(delivered_time) >= 18
                and hour(delivered_time) <= 20
            )
            and status = "delivered"
        group by
            z.zone_id,
            c.courier_name
    ),
    temp4 as (
        select
            zone_id,
            row_number() over (
                partition by
                    zone_id
                order by
                    courier_count desc,
                    Couriers.courier_id asc
            ) as rk,
            temp3.courier_name as top_courier_2024_02
        from
            temp3
            inner join Couriers on temp3.courier_name = Couriers.courier_name
    )
select
    t0.zone_id,
    zone_name,
    ifnull(peak_2023_02_delivered, 0) as peak_2023_02_delivered,
    peak_2024_02_delivered,
    ifnull(peak_2024_01_delivered, 0) as peak_2024_01_delivered,
    ifnull(
        peak_2024_02_delivered - ifnull(peak_2023_02_delivered, 0),
        0
    ) as yoy_delta,
    ifnull(
        peak_2024_02_delivered - ifnull(peak_2024_01_delivered, 0),
        0
    ) as mom_delta,
    avg_peak_minutes_2024_02,
    top_courier_2024_02
from
    temp0 t0
    left join temp1 t1 on t0.zone_id = t1.zone_id
    left join temp2 t2 on t2.zone_id = t0.zone_id
    left join temp4 t4 on t4.zone_id = t0.zone_id
    inner join Zones z on z.zone_id = t0.zone_id
where
    rk = 1
order by
    t0.zone_id,
    zone_name