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

select product_id,round(sum(if(cnt=2,1,0))/count(),3) rate from ( select product_id,uid,count() cnt from ( select overall.uid,info.product_id from tb_order_detail detail join tb_order_overall overall on detail.order_id = overall.order_id and overall.status = 1 and event_time >= ( SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY) FROM tb_order_overall ) join tb_product_info info on info.product_id = detail.product_id and info.tag = '零食' )t1 group by product_id,uid )t2 group by product_id order by rate desc,product_id limit 3;