WITH t AS
(
SELECT DATE(order_time) dt,
ROUND(SUM(COUNT(fare))OVER(ORDER BY DATE(order_time) RANGE INTERVAL 6 DAY PRECEDING)/7,2) finish_num_7d,
ROUND(SUM(COUNT(*)-COUNT(fare))OVER(ORDER BY DATE(order_time) RANGE INTERVAL 6 DAY PRECEDING)/7,2) cancel_num_7d
FROM tb_get_car_order
GROUP BY dt
)
SELECT *
FROM t
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt