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

with t1 as
(select  date(event_time) as dt,
sum(if (fare is not null,1,0)) as daily_complete,
sum(if (fare is null,1,0)) as daily_cancel
from tb_get_car_record
inner join tb_get_car_order
using(order_id)
where date(event_time) between '2021-09-25' AND '2021-10-03'
GROUP BY date(event_time)
order by  date(event_time)
)

select dt, 
round(sum(daily_complete)over (order by dt rows 6 preceding)/7,2) as daily_complete,
round(sum(daily_cancel) over(order by dt rows 6 preceding)/7,2) as cancel_num_7d
from t1
where dt between '2021-10-01' AND '2021-10-03'

写出来虽然能跑通，但是显示答案错误