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

with main as 
(
select
 pro.product_id, oo.uid, 
 rank()over(partition by pro.product_id, oo.uid order by event_time) as rank_times
 from tb_order_detail od
 left join tb_product_info pro using (product_id)
 left join tb_order_overall oo using (order_id)
where pro.tag='零食'
and status=1
and
 datediff(date((select max(event_time) from tb_order_overall)),date(event_time))<90
 )
select 
 product_id,
 round(count(distinct if(rank_times>=2,uid,null))/count(distinct uid),3) as repurchase_rate
 from main
 group by product_id
 order by repurchase_rate desc, product_id
 limit 3




# count(case when rank_times>=2 then 1 else null end)