with a as
(select distinct date(event_time) as dt
from tb_order_overall 
where date(event_time) between '2021-10-01' and '2021-10-03'),

b as
(select date(event_time) dt,
td.product_id,
date(release_time) rt,
shop_id

from tb_product_info tp
join tb_order_detail td on  td.product_id = tp.product_id
join tb_order_overall too on too.order_id = td.order_id
where shop_id=901
)

select a.dt,round(count(distinct product_id)/(select count(product_id) from tb_product_info where date(release_time) <= a.dt and shop_id = 901),3) sale_rate,
1-round(count(distinct product_id)/(select count(product_id) from tb_product_info where date(release_time) <= a.dt and shop_id = 901),3) unsale_rate
from a
left join b on datediff(a.dt,b.dt) between 0 and 6

group by a.dt
order by a.dt

二刷没做出来

这里的套路

1）因为“只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。” 先算10-1到10-3中有销售记录的日期，不用限制901，作为CTE a

2）三表连接，提出日期，商品，此时限制901，作为CTE b

3）a和b连接， datediff(a.dt,b.dt) between 0 and 6 ，计算动销率。分子：count(distinct product_id)，分母比较tricky：(select count(product_id) from tb_product_info where date(release_time) <= a.dt and shop_id = 901) 这里限制条件使用了 a.dt