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

with t1 as(
select  date(event_time) sold_time
        ,product_id
        ,shop_id
        ,date(release_time) onsale_time
from tb_order_overall
left join tb_order_detail using(order_id)
left join tb_product_info using(product_id)) #把所有有销量的商品列出来，包括901店和其他店铺

,t2 as(select  '2021-10-01' as dt
        ,count(product_id) onsale_product
from tb_product_info
where shop_id=901 and date(release_time)<='2021-10-01'
union
select  '2021-10-02' as dt
        ,count(product_id) onsale_product
from tb_product_info
where shop_id=901 and date(release_time)<='2021-10-02'
union
select  '2021-10-03' as dt
        ,count(product_id) onsale_product
from tb_product_info
where shop_id=901 and date(release_time)<='2021-10-03') #得出国庆前三天，901店的在架商品数量

,t3 as(select   dt
        ,count(distinct case when sold_time >= onsale_time and shop_id=901 then product_id
              when sold_time >= onsale_time and shop_id!=901 then null end)product_id  #上架时间要比销售时间早，是901店铺的商品直接输出product_id，不是901店铺的商品输出null，因为count(null)=0，所以即使901没有销量，后面也能输出动销率
from t2
left join t1 on timestampdiff(day,sold_time,dt) between 0 and 6
group by dt) 

select  dt
        ,round(product_id/onsale_product,3) sale_rate
        ,round(1-product_id/onsale_product,3) sale_rate
from t3
join t2 using(dt)
where dt in (select date(event_time) from tb_order_overall) #国庆头三天里存在销售订单的话，就输出查询结果