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

--比较简单吧
select 
c.courier_id,
c.courier_name,
c.city,
count(parcel_id) as orders_7d,
count(distinct case when timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes then parcel_id end) as on_time_7d,
round(
ifnull(
count(distinct case when timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes then parcel_id end)/count(parcel_id),0),2) as on_time_rate,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2) as avg_minutes_7d,
rank()over(partition by c.city order by ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2) asc ) as rank_in_city
from courier c left join parcel p on 
c.courier_id=p.courier_id
where
datediff((select max( delivered_ts) from parcel),date(delivered_ts))<=6
group by c.courier_id,c.courier_name,c.city