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

#t1查询10-1至10-3有交易记录的日期
with t1 as(
    select distinct date(event_time) as dt
    from tb_order_overall
    where date(event_time) between '2021-10-01' and '2021-10-03'),
#t2查询901店铺售卖的商品信息 ，先不按日期聚合
    t2 as(
        select date(event_time) as dt,
       td.product_id

        from tb_order_overall too
        join tb_order_detail td
        on too.order_id = td.order_id
        join tb_product_info tp
        on tp.product_id = td.product_id

        where status =1
        and shop_id=901
       
    )

#主查询
select t1.dt,
round(count(distinct t2.product_id)/(select count(product_id) from tb_product_info where date(release_time) <= t1.dt and shop_id = 901),3) as  sale_rate,
round(1-count(distinct t2.product_id)/(select count(product_id) from tb_product_info where date(release_time) <= t1.dt and shop_id = 901),3) as unsale_rate
from t1 
left join t2 
on datediff(t1.dt,t2.dt) between 0 and 6
group by t1.dt
order by t1.dt