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

with
t1 as(
    select
        o.order_id,
        o.uid,
        o.driver_id,
        r.event_time,
        r.end_time,
        o.order_time,
        o.start_time,
        o.finish_time,
        o.mileage,
        coalesce(o.fare,0) as fare,
        o.grade
    from tb_get_car_order o left join tb_get_car_record r using(order_id)
    where
        r.city='北京'
        and date(o.order_time) between '2021-10-01' and '2021-10-07')

select
    '北京' as city,
    round(avg(order_num),3) as avg_order_num,
    round(avg(income),3) as avg_income
from(
    select
        driver_id,
        count(order_id) as order_num,
        sum(fare) as income
    from t1
    group by driver_id
    having order_num>=3
    ) a