题解 | 国庆期间近7日日均取消订单量

with dt as(
    select distinct date(order_time) dt
    from tb_get_car_order 
    where date_format(order_time,'%m%d') between '1001' and'1003'
)
select
    dt,
    round(count(grade)/7,2) finish_num_7d,
    round(sum(case when mileage is null then 1 else 0 end)/7,2) cancel_num_7d
from
    dt s1 
left join
    tb_get_car_order s2
on datediff(dt,date(order_time)) between 0 and 6
group by 1
order by 1 asc