题解 | #零食类商品中复购率top3高的商品#

select product_id,round(sum(fugou)/count(*),3) repurchase_rate
from 
(
select distinct od.product_id,oo.uid,if(count(if(oo.status=1,1,-1))over(partition by od.product_id,oo.uid)>1,1,0) fugou
from tb_product_info pi
left join tb_order_detail od using(product_id)
join tb_order_overall oo using(order_id)
join (select max(event_time)m_t from tb_order_overall) t2
where pi.tag='零食' and oo.status !=0 and datediff(date(m_t),date(event_time)) < 90
)t1
group by product_id
order by repurchase_rate desc
limit 3