我个人比较喜欢先准备一个数据底表tb1，然后再进行操作。
思路：①先把3表连接，然后选出最大的日期备用；
②计算每个商品分组内，各个uid分别买了几次，用窗口函数count()onver(partition by 商品,uid)，形成tb2；
③将商品分组，基于tb2进行子查询，count每种商品购买的uid数，count每种商品里购买次数>=2的uid数（用if做个判断即可），最后计算。

with tb1 as(
select product_id, uid, tag, event_time, 
max(event_time)over() as max_time
from 
tb_order_detail left join tb_order_overall using(order_id)
left join tb_product_info using(product_id)
where tag='零食')

select distinct product_id, 
round(count(distinct if(a<2,null,uid))/count(distinct uid),3) repurchase_rate
from(
select product_id, uid,
count(uid)over(partition by product_id, uid) a
from tb1
where date(event_time) between '2021-08-06' and '2021-11-03') as tb2
group by product_id
order by repurchase_rate desc, product_id
limit 3