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

with a as(
    select t1.order_id,t1.product_id, t2.uid
    from tb_order_detail t1
    left join tb_order_overall t2 on t1.order_id=t2.order_id
    left join tb_product_info t3 on t1.product_id=t3.product_id
    where 
        datediff((select max(event_time) from tb_order_overall),event_time) <= 89
        and tag='零食'
)

select 
    t1.product_id, 
    round(count(distinct uid)/total_cnt,3) as repurchase_rate
from(
-- total_cnt
    select 
        product_id,
        count(distinct uid) as total_cnt
    from a
    group by product_id
) t1
left join (
-- repur
    select 
        product_id,
        uid
    from a
    group by product_id, uid
    having count(uid) >= 2
) t2
on t1.product_id=t2.product_id
group by t1.product_id,total_cnt
order by repurchase_rate desc
limit 3