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

with a as(
    select tod.product_id,uid,cnt,event_time
    from tb_order_detail tod,tb_product_info tpi,tb_order_overall too
    where tod.product_id=tpi.product_id and tod.order_id=too.order_id
    and tag='零食'
    and event_time>=(select date_sub(max(event_time),interval 89 day) from tb_order_overall)
)
select product_id,round(sum(cnt>1)/count(*),3) repurchase_rate from
(select product_id,uid,count(uid) cnt from a
group by product_id,uid)b
group by product_id
order by repurchase_rate desc,product_id
limit 0,3