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

SELECT product_id,
    ROUND(SUM(if(t1.repurchase > 1, 1, 0)) / COUNT(repurchase), 3) as repurchase_rate
    from(
select product_id, uid, count(*) as repurchase
from tb_product_info join tb_order_detail using(product_id)
                     join tb_order_overall using(order_id)
where tag = '零食' and event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
group by product_id, uid) t1
group by product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;