CTE em SQL: a cláusula WITH explicada com queries reais

WITH pedidos_recentes AS (
    SELECT order_id, customer_id, amount, ordered_at
    FROM orders
    WHERE ordered_at >= current_date - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) AS pedidos, SUM(amount) AS receita
FROM pedidos_recentes
GROUP BY customer_id;

pedidos_recentes existe só enquanto esse comando roda. Nada é criado em disco, sem limpeza depois, sem permissão para conceder.

É aqui que a CTE se paga — cada etapa tem nome e pode usar a anterior.

WITH pagos AS (
    SELECT * FROM orders WHERE status = 'paid'
),
por_cliente AS (
    SELECT customer_id,
           SUM(amount)      AS receita,
           COUNT(*)         AS pedidos,
           MIN(ordered_at)  AS primeiro_pedido
    FROM pagos
    GROUP BY customer_id
),
classificado AS (
    SELECT *,
           NTILE(4) OVER (ORDER BY receita DESC) AS quartil
    FROM por_cliente
)
SELECT c.name, r.receita, r.pedidos, r.quartil
FROM classificado r
JOIN customers c ON c.id = r.customer_id
WHERE r.quartil = 1
ORDER BY r.receita DESC;

Tente reescrever isso com subqueries aninhadas e você entende por que analytics engineers escrevem quase tudo assim — é exatamente a estrutura de um modelo dbt.

Um organograma com a profundidade de cada pessoa abaixo do CEO:

WITH RECURSIVE org AS (
    -- âncora: o topo da árvore
    SELECT id, name, manager_id, 1 AS nivel
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- membro recursivo: quem reporta a alguém já encontrado
    SELECT e.id, e.name, e.manager_id, o.nivel + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
    WHERE o.nivel < 20          -- sempre limite a recursão
)
SELECT nivel, name FROM org ORDER BY nivel, name;

O limite nivel < 20 não é opcional em produção. Um ciclo nos dados (A gerencia B, B gerencia A) transforma a CTE recursiva numa query que nunca termina.

Mesmo padrão, outro uso — gerar um calendário sem buracos para relatórios:

WITH RECURSIVE datas AS (
    SELECT DATE '2026-01-01' AS d
    UNION ALL
    SELECT d + 1 FROM datas WHERE d < DATE '2026-12-31'
)
SELECT d FROM datas;

Engines modernos fazem inline da CTE, então o plano costuma ser igual ao da subquery equivalente. A armadilha é referenciar uma CTE caríssima várias vezes: alguns engines reavaliam a cada uso. Nesse caso, materialize de propósito.

-- PostgreSQL: força uma avaliação e reusa o resultado
WITH pesada AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS receita
    FROM orders GROUP BY customer_id
)
SELECT * FROM pesada WHERE receita > 1000
UNION ALL
SELECT * FROM pesada WHERE receita < 10;

Confirme sempre com EXPLAIN ANALYZE em vez de adivinhar — esse hábito é o que separa quem é sênior de quem "ouviu falar que CTE é lenta".

"CTE é um resultado temporário nomeado com escopo de um comando. Uso para quebrar uma transformação em etapas legíveis e para expressar recursão sobre hierarquias. Normalmente é inlined, então não trato como otimização — se uma CTE pesada é referenciada várias vezes, materializo de propósito e valido no EXPLAIN." Junte isso com funções de janela e você resolve a maioria das rodadas de SQL.