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

with t as (
            select
                date(order_time) as dt,
                order_id,
                mileage,
                start_time
            from 
                tb_get_car_order
            where 
                datediff('2021-10-03', date(order_time)) <= 8
        )


select
    dt,
    round(sum(finish_num)/7, 2) as finish_num_7d,
    round(sum(cancel_num)/7, 2) as cancel_num_7d

from 
    (
        select 
            distinct *

        from 
            (
                select 
                    dt 
                from 
                    t 
                where 
                    dt between '2021-10-01' and '2021-10-03'
            ) t3
        left join 
            (
               select
                    date(order_time) as dtt,
                    count(if(mileage is not null, 1, null)) as finish_num,
                    count(if(mileage is null, 1, null)) as cancel_num
                from 
                    tb_get_car_order
                where 
                    datediff('2021-10-03', date(order_time)) <= 8 
                group by
                    dtt
            ) t4
        on datediff(dt, dtt) between 0 and 6
    ) t5
group by
    dt
order by 
    dt