Podes dar um exemplo de quando são usados CTEs recursivos?

Expressões de Tabela Comum Recursivas (CTE) são usadas para trabalhar com estruturas de dados hierárquicas ou recursivas, por exemplo, para percorrer uma árvore ou um grafo.

Exemplo: obter todos os subordinados de um funcionário na estrutura organizacional.

WITH RECURSIVE Subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE employee_id = 1  -- chefe a partir do qual começamos
    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 recursivamente reúne todos os funcionários subordinados ao chefe com employee_id = 1.