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

with 
t0 as(
    select
        a.product_id,
        b.uid,
        date(b.event_time) as dt
    from tb_order_detail a
    left join tb_order_overall b ON a.order_id = b.order_id
    left join tb_product_info c ON a.product_id = c.product_id
    where b.status = 1 
        and c.tag = '零食'
        and date(b.event_time) >= 
        (select date_sub(max(date(event_time)),interval 89 day)
         from tb_order_overall
         where status = 1))

select
    product_id,
    round(sum(repurchase_cnt)/count(distinct uid),3) as repurchase_rate
from(select
        product_id,uid,
        case when count(distinct dt)>=2 then 1 else 0 end as repurchase_cnt
     from t0
     group by product_id,uid) e
group by product_id
order by repurchase_rate desc,product_id asc
limit 3