>Most CTEs could be written as a subquery, fine, a preference. OTOH CTEs are not overvalued, all CTEs cannot be written as subqueries. However, if you think CTEs in the context of MS SQL Server only, sometimes MS SQL Server doesn't create 'temp' data as it should.

This is submitted as a wihlist item here:
https://feedback.azure.com/forums/908035-sql-server/suggestions/32891374-provide-a-hint-to-force-intermediate-materializati