The trade-off is modularity. During development working with CTEs paves the way for establishing views later, possibly highlight recurring requirements or gaps in the schema, and the logical separation of general purpose sets (ie functions) from the specifics of a particular query is valuable.
Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.
Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.