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

select city,driver_id,avg_grade,round(order_cnt/date_cnt,1) avg_order_num,round(mileage_sum/date_cnt,3) avg_mileage
from 
(
select city,driver_id,round(avg(grade),1) avg_grade,count(distinct date(order_time)) date_cnt,count(a.order_id) order_cnt,sum(mileage) mileage_sum from 
tb_get_car_order a left join tb_get_car_record b
on a.order_id = b.order_id
where driver_id in 
(
select driver_id from
(
select city,driver_id,avg_grade,dense_rank()over(partition by city order by avg_grade desc) ranking from
(
select city,driver_id,round(avg(grade),1) avg_grade from 
tb_get_car_order a left join tb_get_car_record b
on a.uid = b.uid
where grade is not null
group by city,driver_id
) c
) d
where ranking =1
)
group by city,driver_id
) e
order by avg_order_num;