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

WITH t1 AS (
    SELECT
        order_id,
        driver_id,
        IFNULL(fare,0) AS fare
    FROM tb_get_car_order
    WHERE DATE(order_time) BETWEEN '2021-10-01'
    AND '2021-10-07'
),
t2 AS (
    SELECT
        order_id,
        city
    FROM tb_get_car_record
),
t3 AS (
    SELECT t1.*,
           t2.city
    FROM t1
    LEFT JOIN t2
    ON t1.order_id = t2.order_id
    HAVING t2.city = '北京'
),
t4 AS (
  SELECT driver_id,
         COUNT(driver_id) AS order_count,
         SUM(fare) AS fare_sum
    FROM t3
    GROUP BY driver_id
    HAVING order_count >= 3
),
t5 AS (
    SELECT '北京' AS city,
           ROUND(avg(order_count),3) AS avg_order_num,
           ROUND(avg(fare_sum),3) AS avg_income
    FROM t4
)
SELECT * FROM t5;

# 这道题关键在于不要过滤掉null值，将其转化为 0 即可！