题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

SELECT city,
    ROUND(AVG(order_cnt), 3) AS avg_order_num,
    ROUND(AVG(total_income), 3) AS avg_income
FROM (
    SELECT driver_id, city,
        COUNT(order_id) AS order_cnt,
        SUM(IF(fare IS NULL, 0, fare)) AS total_income
    FROM tb_get_car_order
        JOIN tb_get_car_record
        USING(order_id)
    WHERE DATE_FORMAT(order_time, '%Y%m%d') BETWEEN '20211001' AND '20211007'
        AND city = '北京'
    GROUP BY driver_id, city
) order_income_cnt
WHERE order_cnt >= 3
GROUP BY city