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

好久没刷题，整理一下逻辑，有几个关键点：

（1）“只要当天任一店铺有任何商品的销量就输出该天的结果”，所以在找到国庆前3天的日期有条件：任一店铺是有商品销出的（而非只是901店铺）；

（2）又犯的一个错误：datediff(大,小) 是可能为负的！可能为负！可能为负！

所以整体思路：

（1）找到合要求的dt；

（2）根据与dt的关系，筛选合要求的event_dt和release_dt，从而计算对应当日的销出商品、在售商品

with Bt1 as (
    select
        distinct
        shop_id,
        product_id,
        date(event_time) as event_dt,
        date(release_time) as release_dt
    from tb_product_info as At1
    left join tb_order_detail as At2 using(product_id)
    left join tb_order_overall as At3 using(order_id) 
) 


select 
    dt,
    round(count(distinct Ct2.product_id)/count(distinct Ct1.product_id),3) as sale_rate,
    round(1-count(distinct Ct2.product_id)/count(distinct Ct1.product_id),3) as unsale_rate
from (
    select 
        event_dt as dt
    from Bt1
    where event_dt between '2021-10-01' and '2021-10-03'
) as Bt2
left join (select product_id,release_dt from Bt1 where shop_id='901') as Ct1 on Ct1.release_dt<Bt2.dt
left join (select product_id,event_dt from Bt1 where shop_id='901') as Ct2 on datediff(Bt2.dt,Ct2.event_dt) between 0 and 6
group by dt
order by dt