题解 | 近7天骑手履约时效看板

with t1 as (
    select
        max(delivered_ts) as max_date
    from
        parcel
),
t2 as(
select
    c.courier_id,
    c.courier_name,
    c.city,
    count(*) as orders_7d,
    sum(case when timestampdiff(minute,p.shipped_ts,p.delivered_ts) <= p.promised_minutes then 1 else 0 end) as on_time_7d,
    round(avg(timestampdiff(minute,p.shipped_ts,p.delivered_ts)),2) as avg_minutes_7d
from
    courier c
join
    parcel p on c.courier_id = p.courier_id
where
    p.delivered_ts between date_sub((select max_date from t1), interval 7 day) and (select max_date from t1)
group by
    c.courier_id,c.courier_name,c.city)

select
    courier_id,
    courier_name,
    city,
    orders_7d,
    on_time_7d,
    round(on_time_7d/orders_7d ,2 ) as on_time_rate,
    avg_minutes_7d,
    rank() over(partition by city order by round(on_time_7d/orders_7d,2) desc,avg_minutes_7d asc) as rank_in_city
from
    t2
order by 
    city,rank_in_city,courier_id