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

/*
思路：
输出：北京司机平均接单次数，平均兼职收入（显示3位小数），只显示接单>=3的司机的数据
需要获取条件：
    ①清洗得出每个司机的接单数和每个司机的总收入，关联客户表，筛选北京订单。
    ②计算核心指标，不用处理接单但未履约的订单，因为题目只要求输出接单数，且未履约订单的收入为NULL，在使用聚合函数时会被自动忽略。
*/

WITH
sj AS (
    SELECT
        d.driver_id,k.city,
        COUNT(*) AS dds,
        SUM(fare) AS sr
    FROM tb_get_car_order AS d
    INNER JOIN tb_get_car_record AS k
        ON (k.order_id = d.order_id)
    WHERE
        d.order_time >= '2021-10-01 00:00:00'
        AND d.order_time < '2021-10-08 00:00:00'
        AND k.city = '北京'
    GROUP BY d.driver_id,k.city
)

SELECT
    city,
    ROUND(AVG(dds),3) AS avg_order_num,
    ROUND(AVG(sr),3) AS avg_income
FROM sj
WHERE dds >= 3
GROUP BY city;