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

select
    city, driver_id, avg_grade, avg_order_num, avg_mileage
from
    (select
        city, 
        driver_id,
        round(avg(grade),1) avg_grade,
        round(count(*)/count(distinct date(order_time)),1) avg_order_num,
        round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage,
        rank() over(partition by city order by avg(grade) desc) rk
    from
        tb_get_car_record t1
    inner join
        tb_get_car_order t2
    using(order_id)
    group by
        city, driver_id) t
where rk=1
order by avg_order_num;