>I don't get the fuss about why subqueries are unreadable. You read them top to bottom and the details are exactly where they are executed while with CTE's the definition and usages could be pages apart. Happy debugging.

Because you don't read them top to bottom if you want to understand. You read them from the inside going out, which means you have to first figure out what is the innermost query. That can be difficult and it's easy to think some clauses are part of one query when they're really part of the containing or contained query.

This conversation reminds me of the difference between what I called the nested and sequential styles of the FROM clause. You can write almost any query using nested JOINs:

FROM TableA
  JOIN TableB
    JOIN TableC
      JOIN TableD
         ON join condition for TableC and TableD
      ON join condition for TableB and TableC
    ON join condition for TableA and TableB

But I find those can get really hard to follow and comprehend and must prefer the sequential style:

FROM TableA
  JOIN TableB
    ON join condition for TableA and TableB
  JOIN TableC
    ON join condition for TableB and TableC
  JOIN TableD
    ON join condition for TableC and TableD

In much the same way, I find that CTEs flatten a query and make it easier to see and comprehend the pieces.

Tamar