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

SELECT 
    product_id,
    ROUND(repurchases / NULLIF(users, 0), 3) AS repurchase_rate
FROM (
    SELECT 
        product_id,
        COUNT(DISTINCT uid) AS users,
        COUNT(DISTINCT CASE WHEN ttl > 1 THEN uid END) AS repurchases
    FROM (
        SELECT 
            b.product_id, 
            a.uid, 
            COUNT(distinct a.order_id) AS ttl,
            MAX(DATE(event_time)) AS max_date,
            MIN(DATE(event_time)) AS min_date
        FROM 
            tb_order_overall a 
        JOIN 
            tb_order_detail b ON a.order_id = b.order_id 
        JOIN tb_product_info c ON b.product_id = c.product_id 
        and c.tag = "零食"
        WHERE 
            a.status =1
        and a.event_time >=  (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
        GROUP BY 
            b.product_id, a.uid
    ) t1
    GROUP BY 
        product_id
) t2
ORDER BY 
    repurchase_rate DESC, 
    product_id ASC 
LIMIT 3;