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

-- 1、求出近90天内所有购买零食类商品的信息
WITH t AS
(
SELECT d.product_id,event_time,uid
FROM tb_order_overall o JOIN tb_order_detail d ON o.order_id=d.order_id
JOIN tb_product_info p ON d.product_id=p.product_id
WHERE tag='零食' AND status=1
AND TIMESTAMPDIFF(DAY,DATE(event_time),(SELECT MAX(DATE(event_time)) FROM tb_order_overall))<90
)
-- 2、根据商品id分类，购买人数：COUNT(DISTINCT uid)，重复购买人数：COUNT(uid)-COUNT(DISTINCT uid)
SELECT product_id,
ROUND((COUNT(uid)-COUNT(DISTINCT uid))/COUNT(DISTINCT uid),3) repurchase_rate
FROM t
GROUP BY product_id 
ORDER BY repurchase_rate DESC
LIMIT 3