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

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;