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

with
    f3 as (
        select distinct
            date(event_time) date1
        from
            tb_order_overall
        where
            date(event_time) between '2021-10-01' and '2021-10-03'
    )
select
date1,
round(count(distinct ttt.product_id)/count(distinct t1.product_id),3) as sale_rate,
round(1-count(distinct ttt.product_id)/count(distinct t1.product_id),3) as unsale_rate
from
    f3
    join tb_product_info t1 on (date1 > date(release_time))
left join 
(select event_time,t.order_id,product_id from tb_order_overall t join tb_order_detail tt on (t.order_id = tt.order_id)) ttt on (t1.product_id = ttt.product_id and datediff(date1,date(event_time)) between 0 and 6)
where
    shop_id = 901 
group by date1
order by date1