How to usar CTEs para consultas SQL mais legiveis

CTEs (Common Table Expressions) tornam consultas SQL mais legiveis ao dividir logicas complexas em blocos nomeados. E como criar variaveis temporarias dentro da query.

A sintaxe basica:

WITH vendas_recentes AS (
    SELECT * FROM vendas WHERE data >= '2025-01-01'
)
SELECT cliente_id, SUM(valor) as total
FROM vendas_recentes
GROUP BY cliente_id
ORDER BY total DESC;

O WITH define um bloco temporario chamado vendas_recentes. A query principal usa esse bloco como se fosse uma tabela.

CTEs sao uteis para reutilizar a mesma subconsulta em varios lugares:

WITH gastos_por_cliente AS (
    SELECT cliente_id, SUM(valor) as total_gasto
    FROM vendas
    GROUP BY cliente_id
)
SELECT 
    c.nome,
    g.total_gasto,
    CASE 
        WHEN g.total_gasto > 1000 THEN 'VIP'
        ELSE 'normal'
    END as categoria
FROM clientes c
JOIN gastos_por_cliente g ON c.id = g.cliente_id
ORDER BY g.total_gasto DESC;

Sem CTE, voce teria que repetir o SUM(valor) ou criar uma view.

CTEs recursivas permitem trabalhar com dados hierarquicos como arvores de categorias:

WITH RECURSIVE subcategorias AS (
    SELECT id, nome, parent_id, 1 as nivel
    FROM categorias
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.nome, c.parent_id, s.nivel + 1
    FROM categorias c
    JOIN subcategorias s ON c.parent_id = s.id
)
SELECT * FROM subcategorias ORDER BY nivel, nome;

A parte antes do UNION ALL e o caso base (categorias raiz). A parte depois e a recursao que busca os filhos. O nivel controla a profundidade.

Para calcular o total de vendas com crescimento mes a mes:

WITH vendas_por_mes AS (
    SELECT 
        DATE_TRUNC('month', data) as mes,
        SUM(valor) as total_mes
    FROM vendas
    GROUP BY DATE_TRUNC('month', data)
),
com_diferenca AS (
    SELECT 
        mes,
        total_mes,
        LAG(total_mes) OVER (ORDER BY mes) as mes_anterior,
        total_mes - LAG(total_mes) OVER (ORDER BY mes) as crescimento
    FROM vendas_por_mes
)
SELECT 
    mes,
    total_mes,
    COALESCE(crescimento, 0) as crescimento
FROM com_diferenca
ORDER BY mes;

Multiplas CTEs sao separadas por virgula. Cada uma pode referenciar as anteriores.

Para encontrar o caminho entre dois usuarios em uma rede social:

WITH RECURSIVE conexoes AS (
    SELECT usuario1, usuario2, array[usuario1, usuario2] as caminho
    FROM amizades
    WHERE usuario1 = 1

    UNION ALL

    SELECT a.usuario1, a.usuario2, c.caminho || a.usuario2
    FROM amizades a
    JOIN conexoes c ON a.usuario1 = c.usuario2
    WHERE NOT a.usuario2 = ANY(c.caminho)
)
SELECT * FROM conexoes WHERE usuario2 = 10;

CTEs nao sao sempre a opcao mais rapida. O banco pode otimizar subconsultas simples melhor que CTEs. Mas para legibilidade, CTEs ganham sempre.

That's all for now.
Thanks for reading!