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

with tb as (select tpi.product_id,date(tpi.release_time) release_dt
,date(too.event_time) sale_dt,shop_id
from 
tb_product_info tpi
left join tb_order_detail tod
on tpi.product_id=tod.product_id
left join tb_order_overall too
on too.order_id=tod.order_id
where status=1
and release_time<'2021-10-04'
and event_time between '2021-09-25' and '2021-10-04')

select dt,round(sum(is_sale)/sum(is_shelf),3) sale_rate,
round(1-sum(is_sale)/sum(is_shelf),3) unsale_rate
from(
select dt,product_id,
max(if(datediff(dt,sale_dt) between 0 and 6,1,0 )) as is_sale,
max(if(release_dt<dt,1,0)) as is_shelf
from tb as t1
join (select sale_dt as dt from tb where sale_dt between '2021-10-01' and '2021-10-04'
group by sale_dt) as t2
where shop_id=901
group by t2.dt,product_id) as t
group by dt  order by dt