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

# 24.3.5 9:08 —— 9:33  25min

# 字段：city、avg_order_num、avg_income
# 时间：2021-10-01  —— 2021-10-07

# tb1：链接 + 筛选
with tb1 as(
    select driver_id,city, date(finish_time) as dt,fare
    from tb_get_car_order left join tb_get_car_record using(order_id) 
    where  date(finish_time) >= '2021-10-01' 
    AND date(finish_time) <= '2021-10-07' 
    AND city = '北京'
    # AND mileage is not null 
    # 筛选接单次数在3次以上的司机
    # 此处假设接单但是取消的不算入计算（预先排除了）
    # 但是从本题来看，就算取消订单也算在内
),
# tb2：筛选司机 接单三次以上
tb2 as(
    select driver_id,count(dt) as cnt
    from tb1
    group by driver_id
    having count(dt) >= 3
)

# 结果查询：聚合---筛选符合条件的司机 
select city,
round(count(dt)/count(distinct driver_id),3) as avg_order_num,
round(sum(fare)/ count(distinct driver_id),3) as avg_income
from tb1
where driver_id in (select driver_id from tb2)
group by city