¿Cuándo usarías una CTE en lugar de una subconsulta?

Usa una CTE cuando importa la legibilidad o cuando el mismo resultado intermedio se referencia más de una vez, ya que da nombre a un paso en vez de anidarlo. Las subconsultas están bien para un filtro pequeño en línea. El rendimiento es más o menos equivalente en la mayoría de motores modernos, aunque algunos materializan las CTE y otros las incorporan al plan, así que una CTE no es automáticamente más rápida ni más lenta que la consulta anidada equivalente.

Sobre todo por legibilidad y reutilización. Si una consulta tiene cuatro pasos lógicos, cuatro CTE con nombre se leen como un párrafo y quien la revise puede seguir el razonamiento, mientras que tres niveles de subconsultas anidadas obligan a empezar por el medio e ir hacia fuera. La otra razón real es referenciar el mismo conjunto intermedio dos veces sin repetirlo. Lo que no afirmaría es que las CTE son más rápidas. Ese mito está por todas partes. Según el motor y la versión, una CTE puede incorporarse al plan o materializarse una vez, y cualquiera de las dos puede ser la opción más rápida según cuántas veces se referencie y cómo de selectiva sea. En Postgres antes de la versión 12 las CTE eran una barrera de optimización, lo que a veces las hacía dramáticamente más lentas, así que reviso el plan cuando el rendimiento importa de verdad. Donde una CTE es insustituible es en la recursión: recorrer una jerarquía de responsables o un árbol de categorías es directo con una CTE recursiva y genuinamente incómodo sin ella.