People reading this post, please be wary.
CTEs will not always materialize (store in memory) the intermediate results.
For example, if you join the results of a CTE, you may Infact execute that CTE for each row in the joined table.
Also, for SQL Server, attempts to force materialization may not always work.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.