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

WITH a AS(SELECT
    city,
    driver_id,
    ROUND(SUM(grade)/COUNT(order_id), 1) AS avg_grade
FROM
    tb_get_car_record r
JOIN
    tb_get_car_order o USING(order_id)
WHERE
    grade is not null
GROUP BY
    city, driver_id
ORDER BY
    driver_id),

b AS (
    SELECT
        city, driver_id, RANK() OVER (PARTITION BY city ORDER BY avg_grade DESC) AS ranking
    FROM
        a
),

c AS (SELECT city, driver_id FROM b WHERE ranking = 1)

SELECT
    city,
    driver_id,
    ROUND(AVG(grade), 1) AS avg_grade,
    ROUND(COUNT(order_id)/COUNT(DISTINCT DATE(order_time)), 1) AS avg_order_num,
    SUM(mileage)/COUNT(DISTINCT DATE(order_time)) AS avg_mileage
FROM
    tb_get_car_record r
JOIN
    tb_get_car_order o USING(order_id)
WHERE 
    (city, driver_id) in (SELECT * FROM c)
GROUP BY
    driver_id, city
ORDER BY
    avg_order_num