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

参考了大佬的思路 注意外层查询的别名是可以在子查询中使用的

select distinct dt, round(cnt/total_cnt,3) sale_rate,round(1-cnt/total_cnt,3) unsale_rate
from
#统计10月1号到10月3号近7天的在销售产品数
(select 
date(event_time) dt,
(select count(distinct product_id) from
tb_product_info join 
tb_order_detail using(product_id)
join tb_order_overall
using(order_id)  
where timestampdiff(day,event_time,tol.event_time) between 0 and 6
and shop_id=901
) cnt,
#统计10月1号到10月3号近7天的已经上架的产品数
(select count(product_id) from tb_product_info 
where date(release_time)<= dt and shop_id=901
) total_cnt
from tb_order_overall tol
where date(event_time) between '2021-10-01' and '2021-10-03'
)
t1
order by dt