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

WITH T1 AS (
    SELECT
    driver_id,
    city,
    round(avg(grade),1) 平均评分,
    round(count(order_id)/count(DISTINCT date(order_time)),1) 接单数量,
    round(sum(mileage)/count(DISTINCT date(order_time)),3) 平均里程
    FROM
    tb_get_car_order JOIN tb_get_car_record USING(order_id)
    GROUP BY
    driver_id, city
),
T2 AS (
    SELECT
    *,
    rank() over(partition by city order by 平均评分 DESC) 排名
    FROM
    T1 
)
SELECT
    city,
    driver_id,
    平均评分 avg_grade,
    接单数量 avg_order_num,
    平均里程 avg_mileage
FROM
    T2
WHERE
    排名 = 1
ORDER BY 
    avg_order_num