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

WITH T1 AS (
SELECT
uid,
product_id,
if(count(event_time)>1,1,0) repurchase
FROM
tb_order_detail C JOIN tb_product_info A USING(product_id) JOIN tb_order_overall B USING(order_id)
WHERE tag = '零食' AND event_time >= (SELECT date_sub(max(event_time), interval 89 day) FROM tb_order_overall)
GROUP BY 1,2
)

SELECT
product_id,
round(sum(repurchase)/count(*),3) repurchase_rate
FROM T1
GROUP BY 1
ORDER BY 2 DESC, 1 ASC
LIMIT 3