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

select 
    t2.city,
    t2.driver_id,
    avg_grade,
    round(count(*)/count(distinct date(order_time)),1) avg_order_num,
    round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage
from
(
    select 
        city,
        driver_id,
        avg_grade,rank() over(partition by city order by avg_grade desc) rk
    from
    (
        select 
            rd.city,driver_id,round(sum(grade)/count(*),1) avg_grade
        from tb_get_car_record rd
        join tb_get_car_order od
        on rd.uid = od.uid
        where grade is not null
        group by city,driver_id
    )t1
)t2
join tb_get_car_order
on t2.driver_id = tb_get_car_order.driver_id
where t2.rk = 1
group by city,t2.driver_id
order by avg_order_num ;