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

SELECT tbp.product_id, ROUND(COUNT(DISTINCT t2.uid) / COUNT(DISTINCT t1.uid), 3) AS repurchase_rate
FROM

(SELECT  order_id, uid, DATE(event_time) AS dt
FROM tb_order_overall
WHERE DATE(event_time) >= (SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY) FROM tb_order_overall)) AS t1
LEFT OUTER JOIN 

(SELECT order_id, uid,  DATE(event_time) AS dt
FROM tb_order_overall
WHERE DATE(event_time) >= (SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY) FROM tb_order_overall)) AS t2
ON t1.uid = t2.uid 
AND t2.dt > t1.dt

LEFT OUTER JOIN tb_order_detail AS tbo
ON t1.order_id = tbo.order_id
LEFT OUTER JOIN tb_product_info AS tbp
ON tbo.product_id = tbp.product_id
WHERE tag = '零食'
GROUP BY tbp.product_id
ORDER BY repurchase_rate DESC, tbp.product_id
LIMIT 3;

采用自联结的方式做，参考留存率的逻辑