Also for those who didn't know, and are just getting started with them:
They're incredibly handy for a variety of reasons, but can also have unexpected performance impacts. For example, here are functionally equivalent queries written with a CTE vs an inlined/derived table:
WITH cte_foo AS
(SELECT * FROM bar LIMIT 1000000)
SELECT * FROM cte_foo LIMIT 1
and
SELECT * FROM
(SELECT * FROM bar LIMIT 1000000)
as inlined_foo LIMIT 1
Depending on the database you're using, those two could have wildly different performance due to a concept called an optimization fence[1]. In Postgres versions 11 and below, the CTE would have truly returned/materialized 1 million rows,
then the outer query would execute and ultimately return 1 row for the resultset. Whereas the second version would have been optimized such that the outer
LIMIT 1 would have been pushed into the subquery and not materialized those extraneous 999,999 rows to begin with.
As mentioned in [1], Postgres 12 (and 13) have started to tackle that optimization fence within Postgres. But it's still a concern/concept to be aware of, since many databases that support CTEs have varying levels of optimization fences, and you'll want to be sure you understand what optimization/performance impacts exist for your particular database before you go down the CTE path.
[1] https://auto1.tech/postgres12-a-precious-release/