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
strftimee calcular rankings, acumulados e variações com window functions - Deduplicar registros com
ROW_NUMBER()e montar um funil comCOUNT(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:
WITHabre 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
totaiscomo 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).
| Formato | Resultado para 2026-10-05 | Uso típico |
|---|---|---|
'%Y-%m' | 2026-10 | Agrupar por mês |
'%Y' | 2026 | Agrupar por ano |
'%W' | 40 | Número da semana no ano |
'%w' | 1 | Dia 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
OVERque transforma a função numa função de janela. Sem ele,SUMagruparia 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.
| Função | O que faz | Exemplo com 100, 100, 90 |
|---|---|---|
ROW_NUMBER() | Numera 1, 2, 3… sem repetir | 1, 2, 3 |
RANK() | Empates dividem a posição e pulam a seguinte | 1, 1, 3 |
DENSE_RANK() | Empates dividem a posição, sem pular | 1, 1, 2 |
SUM(x) OVER (ORDER BY ...) | Soma acumulada até a linha atual | 100, 200, 290 (ordenando por data) |
LAG(x) | Valor da linha anterior na janela | NULL, 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)comGROUP BY cliente_idSUM(total)semGROUP BYnemOVERORDER BY cliente_ide 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.
FROMeJOIN: monta as linhas de origemWHERE: filtra as linhasGROUP BY: forma os gruposHAVING: 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 WHENcria 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 comCOUNT(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.
- Escreva, no topo do arquivo, a granularidade de cada tabela que você vai usar
- Calcule o total por mês e o acumulado do ano com
SUM() OVER - Monte um ranking dos 3 maiores clientes ou produtos em cada categoria com
RANK() - Junte duas tabelas de granularidades diferentes e prove, comparando os totais, que não houve dupla contagem
- Remova duplicados de uma tabela com
ROW_NUMBER()e explique qual versão você manteve e por quê