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

with 
    t1 as(
        #国庆期间北京成功订单中司机成功接单三次及以上的司机id和订单总额记录
        select
            tgcr.city,
            driver_id,
            count(tgco.order_id) `order_nums`,
            sum(fare) `fare_sums`
        from tb_get_car_record tgcr
        join tb_get_car_order tgco
        on tgcr.order_id=tgco.order_id 
        where tgcr.city='北京' 
        and date(tgco.finish_time) between '2021-10-01' and '2021-10-07'
		#不能加这个 只要是接单就算
      --  and start_time is not null
        group by tgcr.city,driver_id
        having order_nums>=3
    )

select 
    city,
    round(avg(order_nums),3) `avg_order_num`,
    round(avg(fare_sums),3) `avg_income`
from t1
group by city