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

select dt,round(count(distinct product_id)/onsale_cnt,3) sale_rate,round((1-count(distinct product_id)/onsale_cnt),3)unsale_rate
from 
(select date(event_time) dt,count(distinct product_id) onsale_cnt  
from tb_order_overall,  tb_product_info
where release_time<=event_time
and shop_id='901'    
and date(event_time)  between '2021-10-1' and'2021-10-3'
group by dt )  l1  
left join 
(select date(event_time) dt1,t1.product_id
from tb_order_detail t1 left join tb_order_overall t2 on t1.order_id=t2.order_id
left join tb_product_info t3 on t1.product_id=t3.product_id
where shop_id='901'
) l2        
on datediff(dt,dt1) between 0 and 6
group by dt
order by dt