When a subquery makes you repeat yourself

Question

Accepted Answer

The main readability and reuse benefit of CTEs: once defined, a CTE can be referenced by name anywhere in the query that follows - including multiple times. A derived table (subquery in FROM) must be duplicated every time you need it. CTEs do not guarantee caching or faster execution - the optimizer may inline them the same way it inlines subqueries.