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

此题共包含两张表：

表1：tb_get_car_record

表2：tb_get_car_order

要解决的问题：

请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。

解题思路：

1. 国庆7天期间，北京市接单次数和收入
2. 找出接单次数至少3次的司机
3. 算出平均接单数和平均兼职收入

SELECT city, 
	   ROUND(AVG(order_num), 3) as avg_order_num,
	   ROUND(AVG(income), 3) as avg_income
FROM (
    SELECT driver_id, city, COUNT(order_id) as order_num, SUM(fare) as income
    FROM tb_get_car_order
    JOIN tb_get_car_record USING(order_id)
    WHERE city = "北京" 
	and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007'
    GROUP BY driver_id, city
    HAVING COUNT(order_id) >= 3
) t1
GROUP BY city