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

WITH 
date_range AS (
    SELECT 
        DATE_SUB(DATE(MAX(event_time)), INTERVAL 89 DAY) AS start_date,
        DATE(MAX(event_time)) AS end_date
    FROM tb_order_overall
    WHERE status = 1  -- 只统计已付款订单
),

product_purchases AS (
    SELECT
        d.product_id,
        o.uid,
        COUNT(*) AS purchase_count
    FROM tb_order_detail d
    JOIN tb_order_overall o ON d.order_id = o.order_id
    JOIN tb_product_info p ON d.product_id = p.product_id
    WHERE 
        p.tag = '零食'
        AND o.status = 1  -- 已付款订单
        AND DATE(o.event_time) BETWEEN (SELECT start_date FROM date_range) 
                                   AND (SELECT end_date FROM date_range)
    GROUP BY d.product_id, o.uid
)

SELECT
    product_id,
    ROUND(
        SUM(CASE WHEN purchase_count >= 2 THEN 1 ELSE 0 END) / 
        COUNT(DISTINCT uid),  -- 去重计算总人数
        3
    ) AS repurchase_rate
FROM product_purchases
GROUP BY product_id
ORDER BY 
    repurchase_rate DESC,
    product_id
LIMIT 3;