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

SELECT
	M.product_id,
	round( sum( CASE WHEN M.num > 1 THEN 1 ELSE 0 END ) / count( 1 ), 3 ) repurchase_rate 
FROM
	(
	SELECT
		tpi.product_id,
		count( uid ) num 
	FROM
		tb_product_info tpi
		INNER JOIN tb_order_detail tod ON tpi.product_id = tod.product_id
		INNER JOIN tb_order_overall too ON too.order_id = tod.order_id 
	WHERE
		tpi.tag = "零食" 
		AND DATE_ADD( too.event_time, INTERVAL 90 DAY ) > ( SELECT MAX( t.event_time ) FROM tb_order_overall t ) 
	GROUP BY
		tpi.product_id,
		too.uid 
	) M 
GROUP BY
	M.product_id 
ORDER BY
	repurchase_rate DESC,
	M.product_id 
	LIMIT 3