思路：

1. 从商品信息表tpi中筛选出tag="零食"的记录

2. tpi连接tod(on=product_id)，再连接too(on=order_id)，均进行内连接

3. 使用窗口函数从too中计算出最近日期，注意日期格式转换

4. 计算每个产品每个用户在近90天内的购买次数

5. 对每个产品分组计算购买次数大于1的用户数与总用户数，从而计算出复购率

6. 复购率保留3位，按复购率倒序、按product_id升序，保留前三（注意SQL执行顺序）

代码：

select 
    product_id,
    round(sum(if(purchase_cnt > 1, 1, 0)) / count(uid), 3) as repurchase_rate
from (
    select
        product_id,
        uid,
        sum(if(datediff(recent, dt) <= 89, 1, 0)) as purchase_cnt
    from (
        select
            tod.product_id,
            too.uid,
            date(too.event_time) as dt,
            max(date(too.event_time)) over () as recent
        from tb_order_detail as tod
            inner join tb_product_info as tpi on tod.product_id = tpi.product_id 
            inner join tb_order_overall as too on tod.order_id = too.order_id
        where 
            tpi.tag = "零食"
    ) as t
    group by product_id, uid
) as t2
group by product_id
order by repurchase_rate desc, product_id
limit 3