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

SELECT product_id,
    ROUND(COUNT(IF(purchase_cnt > 1, uid, NULL)) / COUNT(uid), 3) AS repurchase_rate
FROM (
    SELECT product_id, uid,
        COUNT(product_id) AS purchase_cnt
    FROM tb_product_info
        JOIN tb_order_detail
        USING(product_id)
        JOIN tb_order_overall
        USING(order_id)
    WHERE status = 1
        AND DATEDIFF((SELECT MAX(DATE(event_time)) FROM tb_order_overall), DATE(event_time)) < 90
        AND tag = '零食'
    GROUP BY product_id, uid
) user_purchase_t
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3