Well John the other consensus is that a CTE and a derived table in a subquery are about the same thing. The big advantage for me would be readability, testing, and documentation. The keyword WITH right at the top tells me that I'm looking at as CTE while the table in a subquery can come as a surprise. As for testing I construct the CTE one level at a time with a test query at the bottom so that I can check the results. While I'm in progress I put in block comments to remind me later what I was trying to accomplish.

For a production facility they wanted a "Station Throughput" report. The table had the product number, the station number, the product weight, and the date/time one row per item produced. For the time range that is simple and there is even an index for that. But they want to see the station name and that is in the station table. Sometimes they want all stations in the plant and sometimes just the stations in a certain department. Department ID is in the station table and department name is in the department table. Oh, the results need to be shown in items per hour.

Using CTEs this query is quite readable. I needed station number within a department in two places. That would have been complex with subqueries.