题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
http://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
with a as(
select date(order_time) od,
count(start_time) as deal,
count(order_id)-count(start_time) nodeal
from tb_get_car_order t1
group by od
order by od),
b 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')
select dt,
(select round(sum(deal)/count(od),2) from a where od between DATE_ADD(dt,interval -6 day) and dt) as finish_num_7d,
(select round(sum(nodeal)/count(od),2) from a where od between DATE_ADD(dt,interval -6 day) and dt) as cancel_num_7d
from b