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

select t2.product_id, repurchase_rate
from
(select product_id, round(sum(case when cn_uid >=2 then 1 else 0 end)/count(*),3) as repurchase_rate
from
(select product_id, count(uid) as cn_uid
from tb_order_overall right join tb_order_detail on tb_order_overall.order_id = tb_order_detail.order_id
where date(event_time) > date_sub((select date(max(event_time)) from tb_order_overall), interval 90 day)
group by product_id, uid) as t1
group by product_id) as t2 left join tb_product_info on t2.product_id = tb_product_info.product_id
where tag = '零食'
order by repurchase_rate desc, t2.product_id
limit 3