题解 | #店铺901国庆期间的7日动销率和滞销率#

with t1 as(
select t1.product_id,date(t3.event_time) as dt,
(select count(distinct product_id) from tb_product_info ledt join tb_order_detail 
using(product_id) where shop_id=901) as total  from tb_product_info t1 
join tb_order_detail t2 
on t1.product_id=t2.product_id
join tb_order_overall t3 
on t2.order_id=t3.order_id
where shop_id=901),

t2 as(select date(event_time) dt from tb_order_overall where date(event_time) between '2021-10-01' AND '2021-10-03'),

t3 as(
select distinct t2.dt,t1.product_id,t1.total
from t2 left join t1 on 
datediff(t2.dt,t1.dt) between 0 and 6
order by t2.dt)

select dt,
IFNULL(round(count(distinct product_id)/total,3),0) as sale_rate,
ifnull(round(1-count(distinct product_id)/total,3),1) as unsale_rate
from t3 group by dt