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

SELECT
	product_id,
	round( sum( IF ( dt >= 2, 1, 0 ))/ count(*), 3 ) AS repurchase_rate 
FROM
	(
	SELECT
		uid,
		d.product_id,
		count( cnt ) AS dt 
	FROM
		tb_order_detail d
		LEFT JOIN tb_order_overall o ON d.order_id = o.order_id
		LEFT JOIN tb_product_info p ON p.product_id = d.product_id 
	WHERE
		tag = '零食' 
		AND event_time BETWEEN DATE_SUB(( SELECT max( event_time ) FROM tb_order_overall ), INTERVAL 89 DAY ) 
		AND ( SELECT max( event_time ) FROM tb_order_overall ) 
	GROUP BY
		1,
		2 
	ORDER BY
		2,
		1 
	) tmp 
GROUP BY
	1 
ORDER BY
	2 DESC,
	1 
	LIMIT 3