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

select product_id,round(sum(cct)/count(uid),3)repurchase_rate
from 
(select uid,product_id,if(count(uid)>1,1,0) as cct
from
(select t1.product_id,event_time,uid,max(event_time)over() as max_dt
from tb_order_detail as t1 left join tb_order_overall as t2
on t1.order_id=t2.order_id
left join tb_product_info t3
on t1.product_id=t3.product_id
where tag="零食"and status=1)a 
where datediff(max_dt,event_time)<=89
group by uid,product_id)b
group by product_id
order by repurchase_rate desc ,product_id
limit 3