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

select product_id,
			 round(count(if(repurchase_cnt>1,uid,null))/count(uid),3) as repurchase_rate
from (
	select product_id,uid,
				 count(if(datediff(max_date,date(event_time))<=89,1,null)) as repurchase_cnt
	from tb_product_info
	left join tb_order_detail using(product_id)
	left join tb_order_overall using(order_id)
	left join (select max(date(event_time)) as max_date
						 from tb_order_overall) as t on 1
	where tag='零食'
	group by product_id,uid
	  ) as a
group by product_id
order by repurchase_rate desc,product_id
limit 3