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

# 请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。
select dt, round(a / c, 3) sale_rate, round(1 - a / c, 3) unsale_rate 
from
(select distinct date(event_time) dt,
 (
 # 获取7天已销售产品数
	select count(distinct (if(shop_id <> 901, null, product_id)))
	from tb_order_overall o
	join tb_order_detail d using(order_id)
	join tb_product_info i using(product_id)
	where timestampdiff(day, date(event_time), date(b.event_time)) between 0 and 6 and shop_id = 901
) a,
(
# 获取店铺已上架总产品数
	select count(distinct product_id) from tb_product_info
	where shop_id = 901
) c
from tb_order_overall b
# 获取国庆前三天数据
where date(event_time) between '2021-10-01' and '2021-10-03'
) t
order by dt