Window functions are super handy to turn a correlated subquery into a join, because they make group-wise limits fairly easy (just put a ROW_NUMBER() concerning the relevant partition, i.e. what would be your GROUP BY in the correlated subquery, in your query and select from that query filtering on rank; then join on whatever you previously correlated on).