Be careful with window functions, sometimes there are easier solutions that the optimizer can handle pretty well. There also are cases where the query is so big, that splitting it up into an id query and then an object query will make the whole thing faster because the planner has an easier job.
Anyway, if you want to play with subqueries in the FROM clause, you might want to give Blaze-Persistence a shot. It supports CTEs on top of JPA/Hibernate which is essentially like subqueries in the FROM clause.