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

select
    product_id,
    round(sum(if(rk >= 2, 1, 0))
    /
    sum(if(rk=1, 1, 0)), 3) repurchase_rate
from
    (select
        t1.product_id,
        uid,
        rank() over(partition by product_id, uid order by event_time) rk
    from
    tb_order_detail t1
    join tb_order_overall t2
    on t1.order_id = t2.order_id
    join tb_product_info t3
    on t1.product_id = t3.product_id
    where tag = '零食' and event_time >= (
            SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
            FROM tb_order_overall)) t
group by product_id
order by repurchase_rate desc, product_id
limit 3