select
product_id,
format(count(case when rk = 2 and active_date >= date_sub(max_date,interval 89 day) then uid else null end)/count(distinct uid),3) as repurchase_rate
from
(
select
distinct a.product_id,uid,date_format(event_time,'%Y-%m-%d') as active_date,max_date,dense_rank() over(partition by uid,a.product_id order by date_format(event_time,'%Y-%m-%d') desc) as rk
from tb_product_info a join tb_order_detail b on a.product_id=b.product_id and a.tag='零食'
join tb_order_overall c on b.order_id=c.order_id
join (select date_format(max(event_time),'%Y-%m-%d') as max_date from tb_order_overall) d
) t1 group by product_id
order by repurchase_rate desc,product_id limit 3
