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

select
    * 
from 
    (select 
        dt,
        round(avg(finish_num) over(order by dt ROWS 6 PRECEDING), 2) as finish_num_7d,
        round(avg(cancel_num) over(order by dt ROWS 6 PRECEDING), 2) as cancel_num_7d
    from 
        (select
            date(order_time) as dt,
            sum(case when start_time is not null then 1 else 0 end) as finish_num,
            sum(case when start_time is null then 1 else 0 end) as cancel_num
        from tb_get_car_order
        group by date(order_time)) as t_num
    where dt between '2021-9-25' and '2021-10-3'
    order by dt desc limit 3) as t
order by dt