题解 | #每个城市中评分最高的司机信息#

with t1 as(
select t1.city,t2.driver_id,avg(grade) as avg from tb_get_car_record t1 join tb_get_car_order t2 on t1.uid=t2.uid and t1.order_id=t2.order_id
where t2.grade is not null
group by t1.city,t2.driver_id
order by avg desc),

t2 as(
select *,rank()over(partition by city order by avg desc) as rk from t1),

t3 as(
select * from t2 where rk =1)


select t3.city,t4.driver_id,
round(avg(t4.grade),1) as avg_grade,
round(count(t4.order_id)/count(distinct date(t4.order_time)),1) as avg_order_num,
round(sum(mileage)/count(distinct date(t4.order_time)),3) as avg_mileage
from tb_get_car_order t4 join t3 on t4.driver_id=t3.driver_id
group by t4.driver_id,t3.city
order by avg_order_num