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

select
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
from
    (
        select
            *,
            dense_rank() over (
                partition by
                    city
                order by
                    avg_grade desc
            ) rn
        from
            (
                select
                    t1.city,
                    t2.driver_id,
                    round(avg(t2.grade), 1) avg_grade,
                    round(
                        count(*) / count(distinct date_format (order_time, '%Y-%m-%d')),
                        1
                    ) avg_order_num,
                    round(
                        sum(t2.mileage) / count(distinct date_format (order_time, '%Y-%m-%d')),
                        3
                    ) avg_mileage
                from
                    tb_get_car_record t1
                    join tb_get_car_order t2 on t1.order_id = t2.order_id
                group by
                    t1.city,
                    t2.driver_id
            ) a
    ) b
where
    rn = 1
order by
    avg_grade asc