SQL177大厂面试真题

统计国庆头三天里，每天的近7日日均订单完成量和日均订单取消量

代码如下：

select * from

(  

    select dt,

    round(sum(finish_num)over(order by dt rows 6 preceding)/7,2) as finish_num_7d,

    round(sum(cancel_num)over(order by dt rows 6 preceding)/7,2) as cancel_num_7d from

    (  

        select date(order_time)dt,

        sum(case when start_time is not null then 1 else 0 end)finish_num,

        sum(case when start_time is null then 1 else 0 end)cancel_num from tb_get_car_order

        group by date(order_time)

        order by dt ASC

    )t

)a

where dt between '2021-10-01' and '2021-10-03'