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

select too.dt_123 dt, 
       round(count(distinct tod.product_id)/avg(cnt),3) sale_rate, 
       (1- round(count(distinct tod.product_id)/avg(cnt),3)) unsale_rate
from (select date(event_time) dt_123
      from tb_order_overall) too
left join (select order_id, date(event_time) dt
           from tb_order_overall) too_2
on datediff(too.dt_123, too_2.dt) between 0 and 6
left join (select order_id, product_id 
      from tb_order_detail 
      where product_id in (select product_id 
                           from tb_product_info 
                           where shop_id = 901)) tod 
using(order_id)
join (select count(product_id) cnt 
      from tb_product_info 
      group by shop_id 
      having shop_id = 901) tpi
where too.dt_123 between '2021-10-01' and '2021-10-03'
group by too.dt_123
order by dt