Quando você usaria uma CTE em vez de uma subquery?

Use uma CTE quando a legibilidade importa ou quando o mesmo resultado intermediário é referenciado mais de uma vez, já que ela dá nome a uma etapa em vez de aninhá-la. Subqueries servem bem para um filtro pequeno e pontual. A performance é praticamente equivalente na maioria dos motores modernos, embora alguns materializem CTEs e outros as incorporem ao plano, então uma CTE não é automaticamente mais rápida nem mais lenta que a query aninhada equivalente.

Na maioria das vezes por legibilidade e reuso. Se uma query tem quatro etapas lógicas, quatro CTEs nomeadas se leem como um parágrafo e quem revisa consegue acompanhar o raciocínio, enquanto três níveis de subqueries aninhadas significam começar pelo meio e ir abrindo para fora. O outro motivo real é referenciar o mesmo conjunto intermediário duas vezes sem repeti-lo. O que eu não afirmaria é que CTEs são mais rápidas. Esse mito está em todo lugar. Dependendo do motor e da versão, uma CTE pode ser incorporada ao plano ou materializada uma vez, e qualquer uma das duas pode ser a opção mais rápida conforme quantas vezes ela é referenciada e quão seletiva ela é. No Postgres anterior à versão 12, CTEs eram uma barreira de otimização, o que às vezes as deixava dramaticamente mais lentas, então eu olho o plano quando performance realmente importa. Onde a CTE é insubstituível é na recursão: percorrer uma hierarquia de gestores ou uma árvore de categorias é direto com uma CTE recursiva e genuinamente desconfortável sem uma.