Erik Darling comes out of the gates with a hot take:
One line I see over and over again — I’ve probably said it too when I was young and needed words to fill space — is that CTEs make queries more readable.
Personally, I don’t think they make queries any more readable than derived tables, but whatever. No one cares what I think, anyway.
I’m going to split the middle of Erik’s take. Yes, using common table expressions by themselves doesn’t make a query easier to read. And yes, a good formatting technique helps a lot in readable code. Once that’s taken care of, I do think that common table expressions can be a bit more readable than their equivalent subqueries, for the reason that they do a better job of separating logically distinct segments of code. In those situations, I can read through each common table expression, getting a feeling for what they’re doing and let them tell a story with a top-to-bottom progression. This technique is most effective when you need several common table expressions for a query. By contrast, when creating subqueries (which I tend not to do much) or derived tables with APPLY (which I do so often, I get the employee discount), the story’s a little more disjointed, as the eyes seem to bounce more frequently between the main query and the subqueries. And don’t get me started on subqueries in the SELECT clause—those are the equivalent of somebody telling a story and saying, “Hang on, now I have to tell this story so that you get what I’m talking about.”
But going back to my agreement, if your code looks terrible, it doesn’t matter what constructs you use—it’s not very human-friendly.