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

select product_id,round(sum(case when ranking>1 then 1 else 0 end)/count(DISTINCT uid),3) rate from 
(select product_id,uid,ROW_NUMBER() over(partition by product_id,uid order by event_time) ranking
from tb_order_detail left join tb_product_info using (product_id) left JOIN tb_order_overall using (order_id)
where tag='零食' and status=1 and datediff((select max(event_time) from tb_order_overall),event_time)<90)t
group by  product_id
order by rate desc,product_id asc
limit 3