with tb1 as(
select product_id, shop_id, event_time, release_time
from tb_order_detail left join tb_order_overall using(order_id)
left join tb_product_info using(product_id))

select distinct date(event_time) dt,
round(7d_num/total_num,3) a, round(1-(7d_num/total_num),3) b
from(
select event_time,
(select count(distinct product_id) 
from tb1 as tb2 
where shop_id=901 and datediff(tb1.event_time,tb2.event_time) between 0 and 6) 7d_num,
(select count(distinct product_id)
from tb1 as tb3
where shop_id=901 and tb1.event_time>=tb3.release_time) total_num
from tb1) tb4
where date(event_time) between '2021-10-01' and '2021-10-03'
order by dt

#整体思路：用select内的子查询遍历对比下单时间（主查询1次，子查询内整体遍历1次），计算7日内的销售商品数及总上架商品数。
#①三表连接，形成一张底表tb1；
#②计算7日内销售商品数量：主查询从tb1取下单时间，子查询从tb1(别名tb2)取商品号，但加入时间判断是tb1.event_time与tb2.event_time相差6天以内；
#③计算截至当日总上架商品数：同理用子查询遍历，加入时间判断tb1.event_time>=tb3.release_time；
#④最后筛选计算即可。