[eyelidlessness]

Be careful with blanket CTE recommendations. They tend to deoptimize with writes in really surprising ways. If you’re just reading data, they’re great.

[hobs]

I would still say they are not great as most engines just... write your sql out, instead of saving/memoizing anything.

[eyelidlessness]

All of my experience here is with Postgres, where (in recent versions) read-only CTEs are basically temporary views with all the built in optimizations that come from that.