LATERAL is Your Friend to Create Local Column Variables in SQL – Java, SQL and jOOQ.
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: SELECT actor_id, name, COUNT(*) ...