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

WITH t1 AS (
SELECT DISTINCT
DATE(order_time) AS dt

FROM 
tb_get_car_order

WHERE DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-03'),

t2 AS(
SELECT
DATE(order_time) AS dt,
start_time

FROM
tb_get_car_order)


SELECT
t1.dt,
ROUND(COUNT(start_time OR NULL)/7, 2) AS finish_num_7d,
ROUND(COUNT(start_time IS NULL OR NULL)/7, 2) AS cancel_num_7d

FROM t1 
LEFT JOIN t2 ON DATEDIFF(t1.dt, t2.dt) BETWEEN '0' AND '6'

GROUP BY t1.dt

ORDER BY t1.dt