> when a nested query is refactored into a common table expression (CTE), this kind of change doesn’t have any functional impact on either a query or its outcome
This isn’t quite true, at least in Postgres. It won’t affect the outcome, but it can affect the query plan.
For this use case, it’s intentional, since only the outcome is important to us. That’s kind of the draw of a declarative language, you ask what you want and don’t have too much control over how that’s done (the engine should optimize that away).
> when a nested query is refactored into a common table expression (CTE), this kind of change doesn’t have any functional impact on either a query or its outcome
This isn’t quite true, at least in Postgres. It won’t affect the outcome, but it can affect the query plan.