I strongly disagree with your assessment. Using CTE instead of derived tables make query more readable while providing the same result.

>Again, just my opinion. If I have to analyse a query I don't like to jump to the top to figure out on how the CTE is defined. It does more harm than good IMO in troubleshooting. So yes, I'd like to avoid CTE in subqueries as much as I can. Hence my opinion they are overvalued.
>
>As far as your observation of CTE not being as efficient as could: Yes I've stumped against that wall several times, often causing me to take a different rout to a better performing solution (like executing in two steps, like table variables or temp tables).