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

select city,
       driver_id,
       avg_grade,
       avg_order_num,
       avg_mileage
from(select city,
            driver_id,  
            round(avg(grade),1) avg_grade,
            round(count(a.order_id)/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) as rk
      from tb_get_car_order a
      left join tb_get_car_record b
      on a.order_id = b.order_id
      group by city,driver_id) a
where rk = 1
order by avg_order_num