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

SELECT
product_id,
ROUND(COUNT(rep_num = '2' OR NULL)/COUNT(uid), 3) AS repurchase_rate

FROM(
    SELECT
    uid,
    product_id,
    SUM(tag) AS rep_num

    FROM(
        SELECT
        product_id,
        uid,
        (SELECT MAX(DATE(event_time)) FROM tb_order_overall) AS today,
        DATE_SUB((SELECT today), INTERVAL 89 DAY) AS start_dt,
        IF(DATE(event_time) BETWEEN (SELECT start_dt) AND (SELECT today), 1, 0) AS tag

        FROM tb_order_detail AS od
        LEFT JOIN tb_order_overall AS oo
            USING(order_id)
        LEFT JOIN tb_product_info AS po
            USING(product_id)

        WHERE status = '1' AND tag = '零食') AS t1

    GROUP BY uid, product_id) AS t2

GROUP BY product_id

ORDER BY repurchase_rate DESC, product_id

LIMIT 3