SQL analítico

Granularidade, CTEs, CASE WHEN, datas, window functions, deduplicação e funis: o SQL que responde perguntas de negócio sem dupla contagem.

Cerca de 16 min · Revisado em

Ao final, você vai conseguir

  • Identificar a granularidade de uma consulta e evitar dupla contagem em JOINs
  • Organizar consultas em etapas com CTEs e classificar linhas com CASE WHEN
  • Agrupar por mês com strftime e calcular rankings, acumulados e variações com window functions
  • Deduplicar registros com ROW_NUMBER() e montar um funil com COUNT(DISTINCT ...)

Saber SELECT, WHERE e GROUP BY já responde muita coisa. Mas as perguntas que chegam no trabalho são de outro tipo: qual cliente mais gastou em cada cidade, quanto o faturamento cresceu de um mês para o outro, quantas pessoas desistiram entre o carrinho e o pagamento. Nesta aula você aprende as ferramentas que transformam SQL em análise de verdade, e o cuidado que evita o erro mais caro da área: somar a mesma coisa duas vezes.

Antes de somar: o que é uma linha?

Granularidade é o que uma linha representa. Na tabela pedidos do banco desta aula, cada linha é um pedido de delivery. Na tabela itens, cada linha é um produto dentro de um pedido. Um pedido com três produtos tem uma linha em pedidos e três em itens.

Quando você faz JOIN de pedidos com itens, o resultado fica na granularidade mais fina: uma linha por item. As colunas de pedido, como o frete, se repetem em cada item. Somar o frete depois desse JOIN conta o mesmo frete várias vezes. Esse erro se chama dupla contagem.

O frete que dobrou

A consulta abaixo diz que o frete total dos pedidos entregues é 91, mas o financeiro jura que é 53. Rode, entenda por que a conta inflou e corrija para devolver o valor certo.

SELECT SUM(p.frete) AS frete_total
FROM pedidos p
JOIN itens i ON i.pedido_id = p.id
WHERE p.status = 'entregue';
  • O frete total dos entregues é 53

CTEs: dar nome aos passos

Para não misturar granularidades, a saída é calcular em etapas: primeiro o total de cada pedido, depois a média desses totais. Dá para fazer isso com uma consulta dentro da outra, mas fica difícil de ler. A forma moderna é a CTE (Common Table Expression, ou expressão de tabela comum).

Uma CTE é um resultado temporário com nome, usado pela consulta final.

WITH totais AS (
  SELECT p.id, p.cliente_id, SUM(i.quantidade * i.preco) AS total
  FROM pedidos p
  JOIN itens i ON i.pedido_id = p.id
  GROUP BY p.id, p.cliente_id
)
SELECT AVG(total) AS ticket_medio
FROM totais;
  • WITH nome AS: WITH abre a CTE e dá um nome ao resultado. Esse nome só existe durante esta consulta, como uma tabela de rascunho.
  • O passo 1: Uma consulta comum que volta a granularidade para uma linha por pedido: os itens são somados dentro de cada pedido.
  • A consulta final: Usa totais como se fosse uma tabela. Agora a média é por pedido, como a pergunta pede. Você pode encadear várias CTEs separadas por vírgula.

Ticket médio por cidade

A consulta calcula a média por item, não por pedido. Reescreva com uma CTE: primeiro o total de cada pedido entregue, depois a média desses totais por cidade, com as colunas cidade e ticket_medio.

-- Errado: isto é a média por ITEM, não por pedido
SELECT c.cidade, AVG(i.quantidade * i.preco) AS ticket_medio
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
JOIN itens i ON i.pedido_id = p.id
WHERE p.status = 'entregue'
GROUP BY c.cidade;
  • Uma linha por cidade (3 cidades)
  • O ticket médio de Recife é 40,80
  • O ticket médio de Salvador é 36,33
  • A consulta usa uma CTE (WITH)
Na consulta errada, Recife tinha ticket médio de cerca de 29. Por que o valor ficou menor que o certo?

Porque a média foi feita sobre 7 itens, e não sobre 5 pedidos. O mesmo total, 204 reais, dividido por mais linhas dá um número menor. A soma estava certa; o que estava errado era o que contava como uma linha.

CASE WHEN: classificar dentro da consulta

CASE WHEN é o “se… então…” do SQL. Ele cria uma coluna nova a partir de regras, testadas de cima para baixo: a primeira condição verdadeira decide o valor. Se nenhuma bater, vale o ELSE, ou NULL se não houver ELSE.

Complete a consulta que classifica clientes por região.

SELECT nome,
  ____
    WHEN cidade IN ('Recife', 'Salvador') ____ 'Nordeste'
    WHEN cidade = 'Belém' THEN 'Norte'
    ____ 'Outra'
  ____ AS regiao
FROM clientes;

Pedidos por faixa de valor

Complete o CASE: total abaixo de 30 é pequeno (já feito), de 30 até 50 é médio, acima de 50 é grande. O resultado deve ter as colunas faixa e pedidos.

WITH totais AS (
  SELECT p.id AS pedido_id, p.cliente_id, p.data, SUM(i.quantidade * i.preco) AS total
  FROM pedidos p
  JOIN itens i ON i.pedido_id = p.id
  WHERE p.status = 'entregue'
  GROUP BY p.id, p.cliente_id, p.data
)
SELECT
  CASE
    WHEN total < 30 THEN 'pequeno'
    -- complete: até 50 é 'médio'; acima disso, 'grande'
  END AS faixa,
  COUNT(*) AS pedidos
FROM totais
GROUP BY faixa;
  • Há 5 pedidos na faixa médio
  • Há 2 pedidos na faixa grande
  • Nenhum pedido ficou sem faixa (NULL)

Num CASE, a primeira regra é WHEN total <= 50 THEN 'médio' e a segunda é WHEN total < 30 THEN 'pequeno'. O que acontece com um pedido de 20 reais?

  • Vira médio, porque a primeira condição verdadeira vence
  • Vira pequeno, porque é a regra mais específica
  • Dá erro, porque as duas condições são verdadeiras ao mesmo tempo

Datas: agrupar por mês e semana

No SQLite, datas são guardadas como texto no formato AAAA-MM-DD. Esse formato tem uma vantagem: ordenar o texto é o mesmo que ordenar as datas. Para recortar pedaços de uma data, use strftime(formato, data).

Formatos mais usados do strftime
FormatoResultado para 2026-10-05Uso típico
'%Y-%m'2026-10Agrupar por mês
'%Y'2026Agrupar por ano
'%W'40Número da semana no ano
'%w'1Dia da semana (0 é domingo)

Faturamento por mês

A consulta agrupa por dia. Mude para agrupar por mês, com as colunas mes (no formato 2026-08) e faturamento, ordenada do mês mais antigo para o mais novo.

SELECT p.data, SUM(i.quantidade * i.preco) AS faturamento
FROM pedidos p
JOIN itens i ON i.pedido_id = p.id
WHERE p.status = 'entregue'
GROUP BY p.data
ORDER BY p.data;
  • Uma linha por mês (3 meses)
  • Setembro de 2026 faturou 136
  • O primeiro mês é 2026-08

Window functions: comparar sem perder as linhas

Uma window function lida por partes.

RANK() OVER (PARTITION BY cidade ORDER BY gasto DESC)
  • A função: O que calcular. Pode ser de ranking (ROW_NUMBER, RANK), de vizinhança (LAG, LEAD) ou uma agregação comum (SUM, AVG, COUNT).
  • OVER: É o OVER que transforma a função numa função de janela. Sem ele, SUM agruparia as linhas; com ele, cada linha continua no resultado.
  • PARTITION BY: Divide as linhas em grupos independentes, como campeonatos separados. O ranking recomeça em cada cidade. É opcional: sem ele, a janela é a tabela toda.
  • ORDER BY da janela: A ordem dentro de cada grupo. Para ranking, decide quem é o primeiro. Para SUM, faz a soma ir acumulando linha a linha.
As window functions que mais aparecem
FunçãoO que fazExemplo com 100, 100, 90
ROW_NUMBER()Numera 1, 2, 3… sem repetir1, 2, 3
RANK()Empates dividem a posição e pulam a seguinte1, 1, 3
DENSE_RANK()Empates dividem a posição, sem pular1, 1, 2
SUM(x) OVER (ORDER BY ...)Soma acumulada até a linha atual100, 200, 290 (ordenando por data)
LAG(x)Valor da linha anterior na janelaNULL, 100, 100

Ranking de clientes em cada cidade

Adicione a coluna posicao: o ranking de cada cliente pelo gasto, do maior para o menor, recomeçando em cada cidade. Use RANK() OVER (PARTITION BY ... ORDER BY ...).

WITH gastos AS (
  SELECT c.nome, c.cidade, SUM(i.quantidade * i.preco) AS gasto
  FROM clientes c
  JOIN pedidos p ON p.cliente_id = c.id
  JOIN itens i ON i.pedido_id = p.id
  WHERE p.status = 'entregue'
  GROUP BY c.id, c.nome, c.cidade
)
SELECT nome, cidade, gasto
FROM gastos
ORDER BY cidade, gasto DESC;
  • Todos os 5 clientes continuam no resultado
  • Bruno é o 1º de Recife
  • Ana é a 2ª de Recife
  • Carla é a 1ª de Salvador (o ranking recomeça por cidade)

Acumulado e variação mês a mês

A partir da CTE mensal, adicione acumulado (soma do faturamento até aquele mês, com SUM(...) OVER (ORDER BY mes)) e variacao (faturamento menos o do mês anterior, com LAG).

WITH mensal AS (
  SELECT strftime('%Y-%m', p.data) AS mes, SUM(i.quantidade * i.preco) AS faturamento
  FROM pedidos p
  JOIN itens i ON i.pedido_id = p.id
  WHERE p.status = 'entregue'
  GROUP BY mes
)
SELECT mes, faturamento
FROM mensal
ORDER BY mes;
  • O acumulado de outubro é 348
  • Setembro cresceu 55 em relação a agosto
  • Agosto não tem mês anterior, então a variação é NULL

Você quer listar cada pedido e, ao lado, o total gasto pelo cliente em todos os pedidos dele. Qual caminho resolve?

  • SUM(total) OVER (PARTITION BY cliente_id)
  • SUM(total) com GROUP BY cliente_id
  • SUM(total) sem GROUP BY nem OVER
  • ORDER BY cliente_id e somar de cabeça

Deduplicar, montar funis e coortes

Na aula de SQL básico você viu a ordem em que as cláusulas são escritas. O banco as executa em outra ordem, e entender essa ordem explica um erro muito comum com window functions.

Coloque as etapas na ordem em que o banco realmente as executa.

  • FROM e JOIN: monta as linhas de origem
  • WHERE: filtra as linhas
  • GROUP BY: forma os grupos
  • HAVING: filtra os grupos
  • Window functions e colunas do SELECT
  • ORDER BY: ordena o resultado

Isso é exatamente o que você precisa para deduplicar. Quando uma carga roda duas vezes ou um sistema reenvia cadastros atualizados, a tabela fica com várias versões da mesma pessoa. A receita: numere as linhas de cada chave com ROW_NUMBER(), da mais recente para a mais antiga, e fique só com a número 1.

Um cadastro por e-mail

A tabela cadastros_brutos tem versões repetidas. Devolva uma linha por e-mail, a mais recente (atualizado_em), com as colunas email, nome e cidade. O DISTINCT não resolve: descubra por quê.

SELECT DISTINCT email, nome, cidade
FROM cadastros_brutos;
  • Uma linha por e-mail (4 pessoas)
  • Carla aparece com a cidade mais recente, Feira de Santana
  • Ana aparece com o nome atualizado, Ana Souza

Um funil mede quantas pessoas passam por cada etapa de um processo: visitou a loja, colocou no carrinho, pagou. A pergunta é sobre pessoas, não sobre eventos, então quem visitou duas vezes conta uma vez só. O truque é combinar COUNT(DISTINCT ...) com CASE WHEN.

O funil da loja

Devolva uma única linha com visitaram, colocaram_no_carrinho e pagaram (pessoas distintas em cada etapa) e conversao: a porcentagem de quem visitou e pagou, arredondada com uma casa (ROUND(100.0 * pagaram / visitaram, 1)).

SELECT etapa, COUNT(*) AS total
FROM eventos
GROUP BY etapa;
  • 6 pessoas visitaram (quem visitou duas vezes conta uma)
  • 4 colocaram no carrinho e 2 pagaram
  • A conversão é 33,3%

Uma coorte é um grupo de pessoas que começou na mesma época, como “quem se cadastrou em agosto”. A análise de coorte acompanha cada grupo ao longo do tempo: quantos de agosto ainda compravam em outubro? Rode a consulta abaixo e leia o resultado linha a linha.

Explore: clientes ativos por coorte de cadastro

Não há objetivo aqui: rode, leia e tente mudar a coorte para semanas com %W.

SELECT strftime('%Y-%m', c.cadastro) AS coorte,
  strftime('%Y-%m', p.data) AS mes_do_pedido,
  COUNT(DISTINCT c.id) AS clientes_ativos
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE p.status = 'entregue'
GROUP BY coorte, mes_do_pedido
ORDER BY coorte, mes_do_pedido;

Fechamento

  • Granularidade é o que uma linha representa; somar uma coluna depois de um JOIN que multiplica linhas gera dupla contagem.
  • CTEs (WITH) dividem a consulta em passos com nome, como calcular o total por pedido antes da média por cidade.
  • CASE WHEN cria classificações dentro da consulta, testando as regras de cima para baixo.
  • Window functions (ROW_NUMBER, RANK, SUM OVER, LAG) calculam rankings, acumulados e variações sem juntar as linhas.
  • Para deduplicar, numere as versões com ROW_NUMBER() numa CTE e fique com a primeira; para funis, conte pessoas com COUNT(DISTINCT CASE WHEN ...).

Perguntas de negócio num banco de verdade

Instale o PostgreSQL ou o DuckDB e carregue um conjunto de dados de vendas, de delivery ou um dado aberto com datas. Responda às perguntas abaixo com consultas salvas num arquivo .sql versionado no Git.

  1. Escreva, no topo do arquivo, a granularidade de cada tabela que você vai usar
  2. Calcule o total por mês e o acumulado do ano com SUM() OVER
  3. Monte um ranking dos 3 maiores clientes ou produtos em cada categoria com RANK()
  4. Junte duas tabelas de granularidades diferentes e prove, comparando os totais, que não houve dupla contagem
  5. Remova duplicados de uma tabela com ROW_NUMBER() e explique qual versão você manteve e por quê

Referências

Cursos relacionados