CTEs para organizar raciocínio analítico e aumentar a legibilidade

Por que usar CTEs no dia a dia de análise

CTE (Common Table Expression) é um recurso do SQL que permite “nomear” uma consulta intermediária e reutilizá-la dentro de uma consulta maior. Na prática, uma CTE funciona como uma tabela temporária definida no início do comando, usando a cláusula WITH. O objetivo principal não é “fazer algo que não dá para fazer sem CTE”, mas organizar o raciocínio analítico em etapas, aumentar a legibilidade e reduzir a chance de erros quando a consulta cresce.

Em relatórios e análises do dia a dia, é comum você precisar combinar várias transformações: filtrar um período, normalizar categorias, calcular métricas por cliente, identificar a primeira compra, classificar clientes em faixas, comparar com um período anterior, e assim por diante. Sem uma estrutura clara, a consulta vira um bloco único difícil de revisar. Com CTEs, você separa cada etapa em um “bloco nomeado”, o que facilita validar resultados parciais e ajustar regras de negócio sem quebrar o restante.

CTE vs. subquery: o que muda na prática

Uma subquery (consulta aninhada) também permite criar etapas, mas costuma ficar “escondida” dentro de outra consulta. CTEs deixam essas etapas explícitas e nomeadas. Isso melhora a leitura e a manutenção, especialmente quando você precisa reutilizar o mesmo conjunto intermediário mais de uma vez.

• Legibilidade: CTEs tornam o fluxo de transformação mais claro, com nomes que descrevem a intenção (ex.: vendas_base, clientes_ativos, metricas_mes).
• Depuração: você consegue testar cada CTE isoladamente (copiando o bloco e fazendo um SELECT *), validando se a etapa está correta.
• Reuso: a mesma CTE pode ser referenciada várias vezes na consulta final, evitando duplicar lógica.
• Manutenção: mudanças de regra (ex.: “cliente ativo = comprou nos últimos 90 dias”) ficam centralizadas em um bloco.

Sintaxe essencial

A estrutura básica é:

WITH nome_cte AS (  SELECT ... ), outra_cte AS (  SELECT ... ) SELECT ... FROM nome_cte;

Pontos importantes:

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

• Você pode definir uma ou várias CTEs, separadas por vírgula.
• A consulta “principal” vem depois das CTEs.
• Uma CTE pode depender de outra CTE definida antes.
• Os nomes devem ser descritivos e consistentes (padrão de nomenclatura ajuda muito).

Princípios de design para CTEs legíveis

1) Uma CTE por intenção (não por capricho)

Evite criar CTEs demais sem necessidade. Cada CTE deve representar uma etapa com propósito claro: “base filtrada”, “enriquecimento”, “agregação”, “classificação”, “resultado final”. Se uma CTE só renomeia colunas sem agregar valor, talvez seja melhor incorporar na etapa anterior.

2) Nomeie como se fosse documentação

Prefira nomes que indiquem o que a CTE contém. Exemplos:

• pedidos_periodo (pedidos filtrados por período)
• itens_validos (itens excluindo cancelados/devolvidos)
• receita_por_cliente_mes (métrica agregada)
• clientes_segmentados (resultado de uma regra de segmentação)

3) Padronize colunas e evite SELECT *

Em CTEs, SELECT * pode parecer prático, mas costuma atrapalhar manutenção: colunas extras entram sem você perceber, nomes colidem em joins e o leitor não entende o que é relevante. Liste colunas explicitamente, e já padronize nomes (ex.: data_pedido, valor_liquido, status_pedido).

4) Coloque filtros o mais cedo possível (quando fizer sentido)

Filtrar cedo reduz volume de dados nas etapas seguintes e diminui risco de duplicidades e cálculos desnecessários. Exemplo: se o relatório é mensal, filtre o período na CTE de base. Se a regra de “status válido” é global, aplique também cedo.

5) Separe “cálculo” de “apresentação”

Uma boa prática é deixar as CTEs cuidarem do cálculo (métricas, flags, classificações) e a consulta final cuidar da apresentação (ordenação, seleção final de colunas, formatação simples). Isso evita misturar regras de negócio com detalhes de layout do relatório.

Passo a passo prático: relatório de receita e retenção por coorte mensal

Vamos montar um exemplo completo e realista usando tabelas fictícias comuns em análises:

• pedidos: pedido_id, cliente_id, data_pedido, status, valor_total, desconto, frete
• clientes: cliente_id, data_cadastro, canal_aquisicao

Objetivo: criar uma visão mensal com:

• coorte de aquisição (mês da primeira compra)
• mês de atividade (mês do pedido)
• número de clientes ativos no mês
• receita líquida no mês
• retenção: clientes ativos no mês / clientes da coorte

Esse tipo de análise costuma ficar confuso em um único bloco. Com CTEs, você organiza o raciocínio em etapas verificáveis.

Etapa 1: definir a base de pedidos válidos e calcular valor líquido

Primeiro, criamos uma CTE com pedidos “válidos” e já calculamos uma métrica consistente de valor líquido. A regra exata depende do negócio; aqui vamos supor: valor líquido = valor_total - desconto + frete, e status válido exclui cancelados.

WITH pedidos_validos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    p.status,    (p.valor_total - p.desconto + p.frete) AS valor_liquido  FROM pedidos p  WHERE p.status IN ('pago', 'enviado', 'entregue') ), ...

Como validar essa etapa: rode apenas o SELECT da CTE e confira se valor_liquido faz sentido e se os status filtrados estão corretos.

Etapa 2: identificar a primeira compra de cada cliente (coorte)

Agora precisamos do mês da primeira compra. Uma forma robusta é calcular a menor data de pedido por cliente e depois truncar para mês. Em muitos bancos você pode usar DATE_TRUNC('month', ...); se a função variar no seu SQL, adapte para o equivalente.

WITH pedidos_validos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    (p.valor_total - p.desconto + p.frete) AS valor_liquido  FROM pedidos p  WHERE p.status IN ('pago', 'enviado', 'entregue') ), primeira_compra AS (  SELECT    pv.cliente_id,    MIN(pv.data_pedido) AS data_primeira_compra  FROM pedidos_validos pv  GROUP BY pv.cliente_id ), coortes AS (  SELECT    pc.cliente_id,    DATE_TRUNC('month', pc.data_primeira_compra) AS mes_coorte  FROM primeira_compra pc ), ...

Repare como a CTE coortes fica simples porque a etapa anterior já fez o trabalho pesado. Isso melhora a leitura e reduz a chance de errar a regra de coorte em várias partes do código.

Etapa 3: criar a atividade mensal (cliente x mês)

Para retenção, queremos saber em quais meses cada cliente teve atividade (pelo menos um pedido válido). Então criamos uma CTE com o mês do pedido e agregamos por cliente e mês. Além de contar pedidos, podemos somar receita líquida.

WITH pedidos_validos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    (p.valor_total - p.desconto + p.frete) AS valor_liquido  FROM pedidos p  WHERE p.status IN ('pago', 'enviado', 'entregue') ), primeira_compra AS (  SELECT    pv.cliente_id,    MIN(pv.data_pedido) AS data_primeira_compra  FROM pedidos_validos pv  GROUP BY pv.cliente_id ), coortes AS (  SELECT    pc.cliente_id,    DATE_TRUNC('month', pc.data_primeira_compra) AS mes_coorte  FROM primeira_compra pc ), atividade_mensal AS (  SELECT    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) AS mes_atividade,    COUNT(DISTINCT pv.pedido_id) AS qtd_pedidos,    SUM(pv.valor_liquido) AS receita_liquida  FROM pedidos_validos pv  GROUP BY    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) ), ...

Note que a CTE atividade_mensal já entrega um grão bem definido: uma linha por cliente por mês. Esse “grão” é crucial para evitar resultados inflados quando você juntar com outras tabelas.

Etapa 4: juntar coorte com atividade e calcular métricas por coorte e mês

Agora juntamos coortes com atividade_mensal para saber, para cada coorte, quantos clientes ficaram ativos em cada mês e qual foi a receita gerada.

WITH pedidos_validos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    (p.valor_total - p.desconto + p.frete) AS valor_liquido  FROM pedidos p  WHERE p.status IN ('pago', 'enviado', 'entregue') ), primeira_compra AS (  SELECT    pv.cliente_id,    MIN(pv.data_pedido) AS data_primeira_compra  FROM pedidos_validos pv  GROUP BY pv.cliente_id ), coortes AS (  SELECT    pc.cliente_id,    DATE_TRUNC('month', pc.data_primeira_compra) AS mes_coorte  FROM primeira_compra pc ), atividade_mensal AS (  SELECT    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) AS mes_atividade,    COUNT(DISTINCT pv.pedido_id) AS qtd_pedidos,    SUM(pv.valor_liquido) AS receita_liquida  FROM pedidos_validos pv  GROUP BY    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) ), coorte_atividade AS (  SELECT    c.mes_coorte,    am.mes_atividade,    am.cliente_id,    am.receita_liquida  FROM coortes c  JOIN atividade_mensal am    ON am.cliente_id = c.cliente_id ), metricas_coorte_mes AS (  SELECT    mes_coorte,    mes_atividade,    COUNT(DISTINCT cliente_id) AS clientes_ativos,    SUM(receita_liquida) AS receita_liquida_total  FROM coorte_atividade  GROUP BY    mes_coorte,    mes_atividade ), ...

Até aqui, já temos um relatório útil. Mas ainda falta a taxa de retenção, que exige o tamanho da coorte (quantos clientes entraram em cada coorte).

Etapa 5: calcular o tamanho da coorte e derivar retenção

Vamos criar uma CTE para o tamanho da coorte e depois juntar com as métricas mensais.

WITH pedidos_validos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    (p.valor_total - p.desconto + p.frete) AS valor_liquido  FROM pedidos p  WHERE p.status IN ('pago', 'enviado', 'entregue') ), primeira_compra AS (  SELECT    pv.cliente_id,    MIN(pv.data_pedido) AS data_primeira_compra  FROM pedidos_validos pv  GROUP BY pv.cliente_id ), coortes AS (  SELECT    pc.cliente_id,    DATE_TRUNC('month', pc.data_primeira_compra) AS mes_coorte  FROM primeira_compra pc ), atividade_mensal AS (  SELECT    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) AS mes_atividade,    SUM(pv.valor_liquido) AS receita_liquida  FROM pedidos_validos pv  GROUP BY    pv.cliente_id,    DATE_TRUNC('month', pv.data_pedido) ), coorte_atividade AS (  SELECT    c.mes_coorte,    am.mes_atividade,    am.cliente_id,    am.receita_liquida  FROM coortes c  JOIN atividade_mensal am    ON am.cliente_id = c.cliente_id ), metricas_coorte_mes AS (  SELECT    mes_coorte,    mes_atividade,    COUNT(DISTINCT cliente_id) AS clientes_ativos,    SUM(receita_liquida) AS receita_liquida_total  FROM coorte_atividade  GROUP BY    mes_coorte,    mes_atividade ), tamanho_coorte AS (  SELECT    mes_coorte,    COUNT(DISTINCT cliente_id) AS clientes_coorte  FROM coortes  GROUP BY mes_coorte ) SELECT  m.mes_coorte,  m.mes_atividade,  t.clientes_coorte,  m.clientes_ativos,  m.receita_liquida_total,  (m.clientes_ativos * 1.0) / NULLIF(t.clientes_coorte, 0) AS taxa_retencao FROM metricas_coorte_mes m JOIN tamanho_coorte t   ON t.mes_coorte = m.mes_coorte ORDER BY  m.mes_coorte,  m.mes_atividade;

O ganho de legibilidade aqui é grande: cada CTE responde a uma pergunta específica. Se a taxa de retenção estiver estranha, você sabe onde olhar: coorte, atividade mensal, ou a junção entre elas.

Padrões úteis de CTE para relatórios recorrentes

Padrão 1: CTE “base” + CTE “dimensões” + CTE “métricas”

Um padrão comum em relatórios é:

• base: filtra período/status e padroniza colunas
• dimensões: traz atributos (ex.: canal, região, categoria) e cria chaves/flags
• métricas: agrega no grão desejado

Exemplo (estrutura):

WITH base AS (  SELECT ... FROM ... WHERE ... ), dimensoes AS (  SELECT ... FROM base JOIN ... ), metricas AS (  SELECT ... FROM dimensoes GROUP BY ... ) SELECT ... FROM metricas;

Esse padrão ajuda a manter consistência entre relatórios: a “base” tende a ser reutilizável e auditável.

Padrão 2: CTE para “regras de negócio” (flags) antes de agregar

Quando você precisa classificar registros (ex.: “pedido elegível”, “cliente reativado”, “assinante”), crie uma CTE que adiciona colunas booleanas/flags e só depois agregue. Isso evita duplicar CASE WHEN em várias métricas e facilita revisar a regra.

WITH pedidos_validos AS (  SELECT    pedido_id, cliente_id, data_pedido, valor_liquido  FROM pedidos  WHERE status IN ('pago','enviado','entregue') ), regras AS (  SELECT    pv.*,    CASE WHEN pv.valor_liquido >= 200 THEN 1 ELSE 0 END AS pedido_alto_valor  FROM pedidos_validos pv ), metricas AS (  SELECT    DATE_TRUNC('month', data_pedido) AS mes,    COUNT(DISTINCT pedido_id) AS pedidos,    SUM(valor_liquido) AS receita,    SUM(pedido_alto_valor) AS pedidos_alto_valor  FROM regras  GROUP BY DATE_TRUNC('month', data_pedido) ) SELECT * FROM metricas;

Padrão 3: CTE para “deduplicação” antes de juntar e agregar

Em bases reais, é comum ter tabelas de eventos, itens, ou histórico com múltiplas linhas por entidade. Um padrão seguro é criar uma CTE que reduz para o grão correto (por exemplo, “último status por pedido”, “um registro por cliente”) e só depois fazer joins e agregações.

Exemplo: suponha uma tabela status_pedido_hist com vários status por pedido. Você pode criar uma CTE para pegar o status mais recente e evitar multiplicar linhas ao juntar com pedidos.

WITH status_recente AS (  SELECT    sph.pedido_id,    MAX(sph.data_status) AS data_status_recente  FROM status_pedido_hist sph  GROUP BY sph.pedido_id ), status_final AS (  SELECT    sph.pedido_id,    sph.status  FROM status_pedido_hist sph  JOIN status_recente sr    ON sr.pedido_id = sph.pedido_id   AND sr.data_status_recente = sph.data_status ), pedidos_enriquecidos AS (  SELECT    p.pedido_id,    p.cliente_id,    p.data_pedido,    sf.status  FROM pedidos p  LEFT JOIN status_final sf    ON sf.pedido_id = p.pedido_id ) SELECT * FROM pedidos_enriquecidos;

Mesmo que você não use exatamente esse modelo, a ideia é: “corrigir o grão” em uma CTE antes de combinar com outras tabelas.

CTEs recursivas: quando fazem sentido em análises

Além das CTEs “normais”, existe a CTE recursiva, usada para problemas em que um resultado depende dele mesmo em iterações: hierarquias (ex.: estrutura organizacional), trilhas de relacionamento, ou geração de sequências (dependendo do banco). Nem todo banco suporta da mesma forma, mas o conceito é parecido: você define um caso base e uma parte recursiva que se repete até não haver mais linhas.

Exemplo conceitual (hierarquia de gestores): tabela funcionarios com funcionario_id e gestor_id. Você pode listar a cadeia de gestão a partir de um funcionário.

WITH RECURSIVE cadeia AS (  SELECT    f.funcionario_id,    f.gestor_id,    0 AS nivel  FROM funcionarios f  WHERE f.funcionario_id = 1001  UNION ALL  SELECT    f.funcionario_id,    f.gestor_id,    c.nivel + 1 AS nivel  FROM funcionarios f  JOIN cadeia c    ON f.funcionario_id = c.gestor_id ) SELECT * FROM cadeia;

Em análises do dia a dia, CTE recursiva é menos frequente do que CTE “normal”, mas é útil quando você precisa navegar estruturas hierárquicas sem depender de processamento fora do banco.

Checklist de qualidade para consultas com CTE

Validação incremental

• Teste cada CTE com um SELECT simples para conferir o grão (quantas linhas por entidade) e a sanidade das métricas.
• Confirme se a CTE de base está filtrando corretamente período/status e se não está excluindo casos importantes.
• Verifique se as chaves usadas em joins entre CTEs mantêm o grão esperado.

Legibilidade e manutenção

• Use nomes consistentes: base_..., dim_..., fato_..., metricas_..., final.
• Evite repetir expressões longas; calcule uma vez em uma CTE e reutilize.
• Deixe a consulta final curta: idealmente, ela só junta CTEs finais e seleciona colunas.

Armadilhas comuns

• CTE gigante: se uma CTE tem 200 linhas e mistura tudo (filtros, joins, agregações, classificação), você perde o benefício. Quebre em etapas.
• CTE com grão indefinido: se você não sabe se está em “pedido”, “item”, “cliente” ou “cliente-mês”, fica fácil errar joins e inflar métricas. Documente o grão no nome ou em comentários (quando permitido) e mantenha consistência.
• Reuso sem intenção: reutilizar uma CTE em vários lugares é bom, mas se ela vira “depósito de tudo”, volta a ficar difícil de entender. Prefira CTEs pequenas e específicas.
• Dependências confusas: uma CTE que depende de outra que depende de outra pode virar uma cadeia difícil. Organize na ordem lógica do raciocínio: base → enriquecimento → agregação → resultado.