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

select product_id,
round(sum(repurchase)/count(uid),3) as repurchase_rate
from(
select uid,product_id,if(count(event_time)>1,1,0) as repurchase
from tb_order_detail as t1
inner join tb_order_overall as t2 using(order_id)
inner join tb_product_info as t3 using(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)
as t1
group by product_id
order by repurchase_rate desc,product_id
limit 0,3