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

with t1 as(
    select
        s2.product_id,
        shop_id,
        date(event_time) dt
    from
        tb_order_overall s1
    join
        tb_order_detail s2
    on s1.order_id = s2.order_id
    join
        tb_product_info s3
    on
        s3.product_id = s2.product_id
)
,t2 as(
    select 
        s1.dt,
        product_num,
        count(distinct case when shop_id = 901 then product_id end) sale_count

    from
       (select dt from t1 where dt between '2021-10-01' and '2021-10-03') s1
    left join 
       t1 s2
    on datediff(s1.dt,s2.dt) between 0 and 6,
    (select count(1) product_num from tb_product_info where shop_id =901) k
    group by 1,product_num
    having count(distinct product_id)>0
)
select
    dt,
    round(sale_count/product_num,3) sale_rate,
    1-round(sale_count/product_num,3) unsale_rate
from
    t2

沙壁题目，这样写才能过