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

select 
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from 
    (
        select
            city,
            driver_id,
            round(avg(grade), 1) as avg_grade,
            round(count(order_id)/count(distinct date(order_time)), 1) avg_order_num,
            round(sum(mileage)/count(distinct date(order_time)), 3) as avg_mileage,
            dense_rank() over(partition by city order by round(avg(grade), 1) desc) as rk
        from
            tb_get_car_order a
        left join 
            tb_get_car_record b
        using(order_id)
        group by
            city,
            driver_id
    ) t1
where 
    rk = 1
order by
    avg_order_num