题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
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函数计算出“购买次数大于两次的用户数”，除以总人数就可得到复购率。最后再加上排序和取前三个的条件即可。
查看30道真题和解析