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

select product_id, round(sum(requency)/count(requency), 3) repurchase_rate from ( select product_id, uid, if(count(event_time)>1,1,0) requency ##利用if和sum并以UID为条件进行分组判断是否回购 from tb_order_detail b join tb_order_overall using(order_id) join tb_product_info d using(product_id) ##不能用inner join，会导致ambiguous product_id。 where tag = '零食' and event_time>=(select date_sub(max(event_time),interval 89 day)from tb_order_overall) ##进行时间限制：但不需要标准格式 group by uid, product_id )a group by product_id order by repurchase_rate desc, product_id limit 3; ##取前三名