题解 | 2021年国庆在北京接单3次及以上的司机统计信息

WITH tb1 AS (
SELECT city, driver_id, a.order_id, fare, mileage
FROM tb_get_car_order a
LEFT JOIN tb_get_car_record b USING(order_id)
WHERE city = '北京' 
AND DATE_FORMAT(order_time,"%Y-%m-%d") BETWEEN '2021-10-01' AND '2021-10-07'
),
tb2 AS (
    SELECT city, driver_id, COUNT(order_id) AS order_count,
       SUM(fare) AS order_fare
    FROM tb1
    GROUP BY driver_id,city
)
SELECT city, ROUND(avg(order_count),3) AS avg_order_num,
       ROUND(AVG(order_fare),3) AS avg_income
FROM tb2
WHERE order_count >= 3
GROUP BY city