Very much RDBMS dependent in this respect, and I appreciate that this article and the responses focus on SQL Server.
In Oracle a CTE can be persisted by the query optimiser, and the decision is likely to be influenced by its expected size. The data is written to a temporary tablespace. Of course it is also unindexed.
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.