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

with t1 as(
    select uid,product_id,count(*) as cnt
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    JOIN tb_product_info USING(product_id)
    where tag = '零食'
    and date(event_time) >= date_sub(
        (select max(date(event_time))
            from tb_order_overall
            ),interval 89 day)
    and status =1
    group by uid,product_id
)
select product_id,
round(sum(cnt>=2)/count(*),3) as repurchase_rate
from t1
group by product_id
order by  repurchase_rate desc,product_id 
limit 3

sum(cnt>=2)补药再用count算逻辑表达了