题解 | #零食类商品中复购率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 too.event_time > ( SELECT DATE_SUB(MAX( t.event_time ),INTERVAL 90 DAY) 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