Comment by fifilura

Maybe I misunderstood, and if I did it makes for an interesting discussion, but your explanation is mainly about what CTEs can be used for not what they can not be used for.

In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.