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

with tmp 
as
(  
    select
    uid
    ,tb_order_detail.product_id product_id
    ,count(1) cnt
    from tb_order_detail
    left join tb_order_overall
    on tb_order_detail.order_id = tb_order_overall.order_id
    left join tb_product_info
    on tb_order_detail.product_id =tb_product_info.product_id
    where tag = '零食'
    and datediff((select date(max(event_time)) from tb_order_overall), date(event_time)) < 90
    and (status = 1 or status = '1')
    group by uid, product_id
)
select
product_id
,round(sum(if(cnt >= 2, 1, 0)) / count(uid), 3) repurchase_rate
from tmp
group by product_id
order by repurchase_rate desc, product_id
limit 3