Subqueries para transformar perguntas complexas em etapas verificáveis

Subqueries (subconsultas) são consultas SQL “dentro” de outras consultas. A ideia central é transformar uma pergunta complexa em etapas menores, cada uma com um resultado verificável. Em vez de tentar escrever uma única consulta longa e difícil de depurar, você cria blocos: primeiro calcula um conjunto intermediário (por exemplo, “clientes elegíveis”), depois usa esse conjunto para filtrar, comparar, ranquear ou enriquecer a consulta final.

Na prática, subqueries ajudam quando você precisa: (1) filtrar com base em um cálculo agregado (ex.: “clientes com gasto acima da média”), (2) comparar uma linha com um conjunto (ex.: “produtos que nunca foram vendidos”), (3) criar tabelas temporárias lógicas para organizar o raciocínio, (4) evitar duplicidades e resultados inflados ao isolar etapas, (5) aplicar regras de negócio em camadas (ex.: “primeiro defina a coorte, depois calcule a métrica”).

Onde uma subquery pode aparecer

Você pode usar subqueries em diferentes partes do SQL. Cada posição tem implicações de legibilidade e desempenho, mas todas seguem o mesmo princípio: produzir um resultado intermediário que a consulta externa consome.

• No FROM: a subquery vira uma “tabela derivada” (derived table). Útil para organizar etapas e reutilizar colunas calculadas.
• No WHERE: para filtrar com IN/EXISTS ou comparar com um valor agregado.
• No SELECT: subquery escalar que retorna um único valor por linha (ex.: “média global” ao lado de cada linha).
• Com CTE (WITH): tecnicamente não é “subquery aninhada” no mesmo sentido, mas é a forma mais legível de estruturar etapas verificáveis. Em muitos bancos, o otimizador trata CTE e subquery de forma semelhante.

Tipos comuns de subqueries e como pensar nelas

1) Subquery escalar (retorna 1 valor)

É usada quando você precisa de um número único para comparar ou exibir. Exemplo típico: comparar o total de um cliente com a média de todos os clientes.

SELECT c.customer_id, c.customer_name, s.total_spent, (SELECT AVG(total_spent) FROM customer_spend) AS avg_spent_global FROM customer_spend s JOIN customers c ON c.customer_id = s.customer_id;

Regra de ouro: subquery escalar deve retornar exatamente uma linha. Se retornar mais de uma, você terá erro (ou comportamento inesperado, dependendo do banco).

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

2) Subquery de conjunto (retorna várias linhas/colunas)

É usada com IN, EXISTS, ou como tabela derivada no FROM. Exemplo: filtrar pedidos que pertencem a clientes “VIP” calculados em uma etapa anterior.

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount FROM orders o WHERE o.customer_id IN (SELECT customer_id FROM vip_customers);

3) Subquery correlacionada (depende da linha externa)

Ela “enxerga” colunas da consulta externa. É poderosa, mas pode ser mais custosa se mal usada, porque pode ser avaliada repetidamente.

SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > 500);

Note que a subquery usa c.customer_id, que vem da consulta externa.

Passo a passo: transformando uma pergunta complexa em etapas verificáveis

Vamos trabalhar com um cenário fictício, mas realista, de análise do dia a dia. Suponha estas tabelas:

• customers(customer_id, customer_name, signup_date, segment)
• orders(order_id, customer_id, order_date, total_amount, status)
• order_items(order_id, product_id, quantity, unit_price)
• products(product_id, product_name, category)

Pergunta de negócio: “Quais clientes estão entre os 10% que mais gastaram nos últimos 90 dias, mas tiveram queda de gasto em relação aos 90 dias anteriores?”

Essa pergunta mistura: janela temporal, agregação por cliente, comparação entre períodos e ranking/percentil. Em vez de tentar resolver tudo de uma vez, vamos quebrar em etapas.

Etapa 1: definir os dois períodos e calcular gasto por cliente em cada período

Primeiro, crie duas subqueries (ou CTEs) com o gasto por cliente em cada janela. Use apenas pedidos concluídos/pagos (ajuste o status conforme seu contexto).

WITH spend_last_90 AS (  SELECT o.customer_id, SUM(o.total_amount) AS spent_last_90  FROM orders o  WHERE o.status = 'PAID'    AND o.order_date >= CURRENT_DATE - INTERVAL '90 day'  GROUP BY o.customer_id), spend_prev_90 AS (  SELECT o.customer_id, SUM(o.total_amount) AS spent_prev_90  FROM orders o  WHERE o.status = 'PAID'    AND o.order_date < CURRENT_DATE - INTERVAL '90 day'    AND o.order_date >= CURRENT_DATE - INTERVAL '180 day'  GROUP BY o.customer_id)

Como verificar: rode cada CTE isoladamente (transformando-a em uma consulta simples) e confira se faz sentido: quantidade de clientes, valores nulos (clientes sem compras no período não aparecem), e se as datas estão corretas.

Etapa 2: juntar as duas visões e calcular a variação

Agora, una os gastos por cliente. Aqui, uma decisão importante: você quer incluir clientes que compraram em apenas um dos períodos? Para “queda”, faz sentido exigir que exista gasto nos dois períodos. Você pode usar INNER JOIN para isso. Se quiser incluir clientes com gasto zero em um período, use LEFT JOIN e trate ausência como zero.

, spend_compare AS (  SELECT l.customer_id,         l.spent_last_90,         p.spent_prev_90,         (l.spent_last_90 - p.spent_prev_90) AS delta_spent  FROM spend_last_90 l  JOIN spend_prev_90 p    ON p.customer_id = l.customer_id)

Como verificar: confira se delta_spent é negativo para quedas e positivo para crescimento. Verifique também se não há duplicidades (deve haver uma linha por cliente).

Etapa 3: determinar o top 10% por gasto no período recente

Existem várias formas de calcular “top 10%”. Uma abordagem comum é usar funções de janela (quando disponíveis) para calcular percentil/rank. Como o foco aqui é subqueries, vamos usar uma etapa que calcula o ranking e depois filtra.

, ranked_customers AS (  SELECT customer_id, spent_last_90, spent_prev_90, delta_spent,         NTILE(10) OVER (ORDER BY spent_last_90 DESC) AS decile  FROM spend_compare)

Como verificar: inspecione a distribuição de decile e confirme que decile = 1 representa os maiores gastos (por causa do ORDER BY DESC).

Etapa 4: consulta final com critérios de queda

Agora fica simples: pegue apenas decil 1 (top 10%) e delta negativo.

SELECT c.customer_id, c.customer_name, r.spent_last_90, r.spent_prev_90, r.delta_spent FROM ranked_customers r JOIN customers c ON c.customer_id = r.customer_id WHERE r.decile = 1   AND r.delta_spent < 0 ORDER BY r.spent_last_90 DESC;

Observe como a pergunta complexa virou etapas verificáveis: (1) gasto por período, (2) comparação, (3) ranking, (4) filtro final. Cada etapa pode ser testada e validada separadamente.

Subquery no FROM (tabela derivada) para “pré-agregar” e evitar confusão

Um uso muito comum é “pré-agregar” antes de juntar com outra tabela. Isso evita erros de lógica e facilita auditoria do resultado intermediário.

Pergunta: “Quais categorias tiveram ticket médio acima do ticket médio geral no último mês?”

Você pode fazer em duas camadas: (1) calcular ticket médio por categoria; (2) comparar com o ticket médio geral (subquery escalar).

SELECT x.category, x.avg_ticket_category FROM (  SELECT p.category, AVG(o.total_amount) AS avg_ticket_category  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p ON p.product_id = oi.product_id  WHERE o.status = 'PAID'    AND o.order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'    AND o.order_date <  DATE_TRUNC('month', CURRENT_DATE)  GROUP BY p.category) x WHERE x.avg_ticket_category > (  SELECT AVG(o2.total_amount)  FROM orders o2  WHERE o2.status = 'PAID'    AND o2.order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'    AND o2.order_date <  DATE_TRUNC('month', CURRENT_DATE));

Como verificar: rode apenas a subquery do FROM para ver as categorias e seus tickets. Depois rode a subquery escalar para ver o ticket médio geral do mês. Por fim, valide se o filtro faz sentido.

IN vs EXISTS: escolhendo a subquery certa para “pertence ao conjunto”

Ambas expressam “filtrar linhas com base em um conjunto”, mas têm diferenças práticas.

IN (subquery retorna lista)

Bom quando a subquery retorna uma lista simples e você quer legibilidade. Exemplo: “pedidos de clientes do segmento ‘Enterprise’”.

SELECT o.order_id, o.customer_id, o.total_amount FROM orders o WHERE o.customer_id IN (  SELECT c.customer_id  FROM customers c  WHERE c.segment = 'Enterprise');

EXISTS (subquery retorna existência)

Bom quando você quer apenas saber se existe pelo menos um registro relacionado, especialmente com condições adicionais. Exemplo: “clientes que compraram na categoria ‘Acessórios’ no último trimestre”.

SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS (  SELECT 1  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p ON p.product_id = oi.product_id  WHERE o.customer_id = c.customer_id    AND o.status = 'PAID'    AND o.order_date >= CURRENT_DATE - INTERVAL '90 day'    AND p.category = 'Acessórios');

Dica de verificação: pegue um customer_id retornado e rode a subquery interna substituindo o.customer_id = c.customer_id por um valor fixo para confirmar que existe ao menos uma compra na categoria.

NOT EXISTS para “nunca aconteceu” (e por que é mais seguro que NOT IN)

Pergunta: “Quais produtos nunca foram vendidos?”

Uma forma robusta é usar NOT EXISTS. Isso evita armadilhas quando a subquery pode retornar valores nulos (o que costuma quebrar a lógica de NOT IN em muitos bancos).

SELECT p.product_id, p.product_name, p.category FROM products p WHERE NOT EXISTS (  SELECT 1  FROM order_items oi  WHERE oi.product_id = p.product_id);

Como verificar: escolha um produto retornado e procure manualmente no order_items se há registros. Se houver, a lógica está errada (ou você precisa filtrar por status de pedido, por exemplo).

Subquery correlacionada para “último evento por entidade”

Pergunta: “Para cada cliente, qual foi a data do último pedido pago e o valor desse pedido?”

Há várias maneiras de resolver (incluindo funções de janela). Com subquery correlacionada, você pode buscar o último pedido por cliente. Uma abordagem é: (1) encontrar a data máxima por cliente; (2) buscar o pedido correspondente. Dependendo do banco, pode haver mais de um pedido na mesma data; então, você pode precisar de critério de desempate (por exemplo, maior order_id).

SELECT c.customer_id, c.customer_name, o.order_id, o.order_date, o.total_amount FROM customers c JOIN orders o   ON o.customer_id = c.customer_id WHERE o.status = 'PAID'   AND o.order_date = (     SELECT MAX(o2.order_date)     FROM orders o2     WHERE o2.customer_id = c.customer_id       AND o2.status = 'PAID'   );

Como verificar: para um cliente específico, rode a subquery interna e confira se a data máxima bate com o pedido retornado. Se aparecerem múltiplas linhas por cliente, é sinal de empate na data; adicione um desempate com outra subquery (por exemplo, max(order_id) dentro da data máxima) ou use janela.

Padrão “subquery para filtrar por agregado” sem misturar níveis

Um erro comum em consultas do dia a dia é tentar filtrar por um agregado e, ao mesmo tempo, selecionar colunas detalhadas sem separar níveis. Subqueries ajudam a manter cada nível de granularidade claro.

Pergunta: “Liste os pedidos (detalhe) de clientes cujo gasto total no ano é maior que 10.000.”

Faça em duas etapas: (1) encontre clientes elegíveis (agregado por cliente); (2) traga os pedidos desses clientes (detalhe por pedido).

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount FROM orders o WHERE o.status = 'PAID'   AND o.order_date >= DATE_TRUNC('year', CURRENT_DATE)   AND o.customer_id IN (     SELECT o2.customer_id     FROM orders o2     WHERE o2.status = 'PAID'       AND o2.order_date >= DATE_TRUNC('year', CURRENT_DATE)     GROUP BY o2.customer_id     HAVING SUM(o2.total_amount) > 10000   ) ORDER BY o.customer_id, o.order_date;

Como verificar: rode a subquery do IN para ver a lista de clientes e seus totais (você pode temporariamente adicionar o SUM no SELECT para auditoria). Depois confira se todos os pedidos retornados pertencem a clientes dessa lista.

Checklist prático para subqueries “verificáveis”

• Nomeie as etapas com CTE (WITH) quando possível: facilita leitura e teste.
• Valide cardinalidade: cada etapa deve produzir a granularidade esperada (por cliente, por pedido, por produto). Se a etapa deveria ter 1 linha por cliente, teste com COUNT(*) vs COUNT(DISTINCT customer_id).
• Teste com amostras: escolha 2 ou 3 IDs e execute as subqueries internas com filtros fixos para confirmar a lógica.
• Evite “SELECT *” nas etapas intermediárias: selecione apenas o necessário para reduzir ruído e risco de ambiguidade.
• Cuidado com subquery escalar: garanta que retorna 1 linha (use agregação, ou limite/critério claro).
• Prefira EXISTS para checagem de existência e NOT EXISTS para “ausência”, especialmente quando há chance de nulos.
• Documente a intenção com nomes de colunas calculadas (ex.: spent_last_90, delta_spent) para tornar a auditoria mais direta.

Exercício guiado: construir uma subquery em camadas para um relatório operacional

Pergunta: “Quais clientes fizeram pelo menos 3 pedidos pagos nos últimos 60 dias e, entre esses, quais tiveram valor médio por pedido abaixo da média do grupo?”

Passo 1: clientes com pelo menos 3 pedidos pagos nos últimos 60 dias.

WITH eligible_customers AS (  SELECT o.customer_id  FROM orders o  WHERE o.status = 'PAID'    AND o.order_date >= CURRENT_DATE - INTERVAL '60 day'  GROUP BY o.customer_id  HAVING COUNT(*) >= 3)

Passo 2: calcular o ticket médio por cliente dentro do mesmo período, apenas para elegíveis.

, avg_ticket_by_customer AS (  SELECT o.customer_id, AVG(o.total_amount) AS avg_ticket  FROM orders o  WHERE o.status = 'PAID'    AND o.order_date >= CURRENT_DATE - INTERVAL '60 day'    AND o.customer_id IN (SELECT customer_id FROM eligible_customers)  GROUP BY o.customer_id)

Passo 3: calcular a média do grupo (apenas elegíveis) e comparar.

SELECT c.customer_id, c.customer_name, a.avg_ticket FROM avg_ticket_by_customer a JOIN customers c ON c.customer_id = a.customer_id WHERE a.avg_ticket < (  SELECT AVG(a2.avg_ticket)  FROM avg_ticket_by_customer a2) ORDER BY a.avg_ticket ASC;

Como verificar: (1) rode eligible_customers e veja se a contagem faz sentido; (2) rode avg_ticket_by_customer e confira se há uma linha por cliente; (3) rode a subquery escalar da média do grupo e compare manualmente com alguns clientes.