注意事项

首先要明确某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数。

近90天指包含最大日期（记为当天）在内的近90天。最大日期就是tb_order_overall表里的最大日期，用Max即可求出。近90天用datediff函数

select p1.product_id,ifnull(round(buy_2/buy,3),0) as repurchase_rate from 
tb_product_info as p1 left join (
# 计算每个产品购买的人数
select o1.product_id,count(distinct o2.uid) as buy from 
tb_order_detail as o1 left join tb_order_overall as o2 
on o1.order_id=o2.order_id 
    where datediff((select max(event_time) from 
    tb_order_overall),o2.event_time) < 90 
    and o2.status !=2 
     group by o1.product_id )p2 
     on p1.product_id=p2.product_id 
  left join (
# 计算每个产品购买人数超过2人的记录
select t1.product_id ,count(distinct t1.uid) as buy_2 from (
# 计算每个产品、每个用户的购买次数
select o1.product_id,o2.uid,count(*) as cnt from 
tb_order_detail as o1 left join tb_order_overall as o2 
on o1.order_id=o2.order_id where datediff((select max(event_time) from 
   tb_order_overall),date(o2.event_time)) < 90 
    and o2.status !=2 
    group by o1.product_id,o2.uid) t1 
      where t1.cnt>=2 group by t1.product_id) p3 
      on p1.product_id=p3.product_id where p1.tag='零食'
      order by repurchase_rate desc,p1.product_id asc limit 3