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

-- 思路：用户打车记录表关联打车订单表
--         分组过滤统计，然后使用avg函数
with t as (
select 
    driver_id,
    count(o.order_id) as order_num,
    sum(fare) as total_fare
from tb_get_car_order o join  tb_get_car_record r
    on o.order_id = r.order_id
where r.city = '北京'
    and date_format(o.order_time, '%Y-%m-%d') between '2021-10-01' and '2021-10-07'
    # and start_time is not null
group by driver_id having count(o.order_id) >= 3
)

select 
    '北京' as city,
    round(avg(order_num),3) as avg_order_num,
    round(avg(total_fare), 3) as avg_income
from t