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

WITH t AS(SELECT
            uid,
            t3.product_id,
            COUNT(t3.product_id)  AS order_times
        FROM tb_order_detail t3
        JOIN tb_order_overall t2 ON t3.order_id = t2.order_id
        JOIN tb_product_info t1 ON t3.product_id = t1.product_id
        WHERE DATEDIFF((SELECT MAX(event_time) FROM tb_order_overall), event_time) < 90
            AND tag = '零食' AND status = 1
        GROUP BY t3.product_id, uid 
)
SELECT 
    product_id,
    ROUND(SUM(IF(order_times > 1, 1, 0)) / COUNT(DISTINCT uid), 3) AS repurchase_rate 
FROM t
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3

先在子查询中统计出90天内每种零食类商品被每个顾客购买的次数，然后在主查询中计算商品的复购率，可以用SUM函数配合IF函数计算出“购买次数大于两次的用户数”，除以总人数就可得到复购率。最后再加上排序和取前三个的条件即可。