with t as (
select courier_id,courier_name,city,
count(parcel_id) as orders_7d,
count(if(timestampdiff(second,shipped_ts,delivered_ts)/60 <= promised_minutes,parcel_id,null)) as on_time_7d,
round(ifnull(count(if(timestampdiff(second,shipped_ts,delivered_ts)/60 <= promised_minutes,parcel_id,null)) / nullif(count(parcel_id),0),0),2) as on_time_rate,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2) as avg_minutes_7d                     
from courier
left join parcel using(courier_id)
where datediff((select max(delivered_ts) from parcel),delivered_ts) <= 7
group by courier_id,courier_name,city
having orders_7d > 0)

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

#窗口函数排名得单独写才行欸，和其他列聚合出现了问题。原本是想一个查询搞定结果的，但是不知道为啥直接用一个查询写on_time_rate和avg_minutes_7d的结果被改变了，把窗口函数那列去掉就没事了，懂的老师麻烦指点一二