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).
Basically: SELECT ... FROM (SELECT ..., ROW_NUMBER() OVER (PARTITION BY my_group [ORDER BY...]) AS rank) AS foo WHERE foo.rank <= 10
Without window functions you can usually only do this using a correlated subquery, which usually is much slower for fairly obvious reasons.
That's another common variation, using a where in the outer query to select only a few of the rows from the window function.
And I've also used them as a better performing version of a full outer join - do a select of the combined fields from a window function query that is selected off of a UNION ALL. You have to jump a lot of hoops to do it, but the performance win can be worthwhile.
Basically: SELECT ... FROM (SELECT ..., ROW_NUMBER() OVER (PARTITION BY my_group [ORDER BY...]) AS rank) AS foo WHERE foo.rank <= 10
Without window functions you can usually only do this using a correlated subquery, which usually is much slower for fairly obvious reasons.