¿Puedes dar un ejemplo de cuándo se utilizan CTE recursivos?

Las expresiones de tabla comunes recursivas (CTE) se utilizan para trabajar con estructuras de datos jerárquicas o recursivas, por ejemplo, para recorrer un árbol o un grafo.

Ejemplo: obtener todos los subordinados de un empleado en la estructura organizacional.

WITH RECURSIVE Subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE employee_id = 1  -- jefe desde el cual comenzamos
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

Esta consulta recopila recursivamente a todos los empleados subordinados al jefe con employee_id = 1.