In PostgreSQL, you can use CTEs to force certain subqueries to run before others. That technique would be useful here, and would clean up the code a bit.
http://technet.microsoft.com/en-us/library/ms190766(v=sql.10...
In SQL Server, aside from recursion, CTEs are purely syntactic sugar and thus a tool for organizing code. Whereas in Postgres, due to the optimization boundary, they are a tool for query tuning. If you rewrite a query in Postgres using CTEs just to clean it up, you are doing it wrong, since the CTEs will also affect the planner.
It's good to learn the name.
I think of it as each section builds a temp table.
This can be a good or bad thing, but it's sometimes important to know that it's happening.