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

# 一步到位
SELECT DISTINCT
    pi.product_id,
    ROUND(AVG(IF(COUNT(*)>1, 1,0)) OVER(PARTITION BY pi.product_id),3) repurchase_rate
FROM
    tb_product_info pi
INNER JOIN
    tb_order_detail od USING(product_id)
INNER JOIN
    tb_order_overall oo USING(order_id)
WHERE
    DATEDIFF((SELECT MAX(DATE(event_time)) FROM tb_order_overall), DATE(event_time)) < 90 AND tag = '零食'
GROUP BY
    pi.product_id,uid
ORDER BY
    repurchase_rate DESC, pi.product_id
LIMIT
    3