CTEs really tripped me up when I started using them professionally. My mental model was that they result in reusable objects in memory and thus could be used to improve performance as well as composition.

After discovering the truth, it was interesting to find out that almost everyone I knew who wasn't a snr db engineer shared the same incorrect assumption.