So application developer A writes the CTE -- or the derived table -- then application developer B writes it slightly differently a month later in another application. Modularity? No, maintenance nightmare. There should be a DBA writing views, verifying with business analysts that the views select exactly what is expected, documenting the views, and reviewing the SQL written by the application developers to make sure they use standard views -- instead of developing as if their programming creativity is what it's about.
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."