题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
select distinct t1.product_id , round(count(distinct case when rn >= 2 then t1.uid end) / count(distinct t1.uid), 3) as repurchase_rate from( select pi.product_id , oo.uid , row_number() over (partition by pi.product_id, oo.uid order by oo.event_time) as rn from tb_product_info as pi left join tb_order_detail as od on pi.product_id = od.product_id left join tb_order_overall as oo on oo.order_id = od.order_id where pi.tag = "零食" and date(oo.event_time) between date_sub((select max(date(event_time)) from tb_order_overall), interval 89 day) and (select max(date(event_time)) from tb_order_overall) ) as t1 group by t1.product_id order by repurchase_rate desc, product_id limit 3 offset 0;