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

WITH sum_purchase as(
SELECT uid
    ,product_id
    ,count(product_id) purchase_cnt
FROM tb_order_overall
JOIN tb_order_detail USING(order_id)
JOIN tb_product_info USING(product_id)
WHERE date(event_time) between date_sub((SELECT max(date(event_time)) FROM tb_order_overall),interval 89 day) and (SELECT max(date(event_time)) FROM tb_order_overall) 
AND status = 1
AND tag = '零食'
GROUP BY uid,product_id
)
SELECT product_id
    ,round(count(if(purchase_cnt > 1,1,null))/count(product_id),3) repurchase_rate
FROM sum_purchase 
GROUP BY product_id
ORDER BY repurchase_rate desc,product_id
LIMIT 3