Projeto: Relatórios da Loja

Toda segunda-feira, a Marta Lustosa, gerente da Loja Aurora, faz as mesmas perguntas: quanto a loja vendeu, em que mês vendeu mais, quanto gasta um cliente por compra, quem são os melhores clientes, que produtos estão encalhados e que categoria puxa as vendas. Até hoje a Ana respondia somando na calculadora. Neste projeto você escreve esses relatórios em SQL, um por versão, sobre os 90 pedidos reais da Aurora. No caminho, você vai cair (de propósito) nas duas armadilhas que mais enganam quem faz relatório: o frete contado várias vezes e a média calculada sobre a coisa errada.

Todas as caixas desta página rodam sobre a Aurora completa, que o editor carrega a cada Rodar. Nenhum número do texto foi digitado à mão: são os resultados reais desses dados. Os conceitos têm aula própria; os mais usados aqui são GROUP BY, INNER JOIN, Funções de Data e CTE (WITH).

A ideia: de onde sai cada número

Um relatório é uma pergunta de negócio traduzida em SQL. Antes de escrever, pergunte: onde está esse número no banco? Na Aurora, o dinheiro de uma venda está espalhado em duas tabelas:

Neste projeto, faturamento é o valor dos produtos vendidos (o frete vai para a transportadora), e total do pedido é o que o cliente pagou, frete incluído. Combinar isso antes evita a pior briga de relatório: dois números "certos" que não batem porque cada um mediu uma coisa.

Versão 1: o valor de cada pedido

Todo relatório desta página nasce desta consulta. Ela junta cada pedido com os seus itens e soma, pedido por pedido:

SELECT p.id, p.data,
       SUM(i.quantidade * i.preco_unitario)           AS produtos,
       p.frete,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id
ORDER BY p.id
LIMIT 5;

JOIN itens_pedido i ON i.pedido_id = p.id

Cada pedido encontra os seus itens. O pedido 1 tem 3 itens, então vira 3 linhas antes do agrupamento (aula INNER JOIN).

GROUP BY p.id

Junta de volta as linhas de cada pedido numa só (aula GROUP BY).

SUM(i.quantidade * i.preco_unitario)

Soma os subtotais dos itens do grupo: o valor dos produtos.

p.frete

O frete fica fora do SUM: dentro do grupo ele é igual nas 3 linhas, porque o grupo é um pedido só (p.id é a chave primária). Por isso é seguro citá-lo direto. Se ele entrasse no SUM, seria somado 3 vezes, como você vai ver em Quando dá errado.

LIMIT 5

Só para a tela não encher. Sem ele, saem os 90 pedidos.

O pedido 1 fecha em 40.84 (30.94 em produtos e 9.9 de frete), o mesmo número que você conferiu no projeto a Loja Aurora Completa. O pedido 4 teve frete grátis: o total é igual aos produtos.

SELECT p.id, p.data,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id
ORDER BY p.id
LIMIT 5;

SELECT p.id, c.nome AS cliente, p.data,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.entregue_em IS NULL
GROUP BY p.id
ORDER BY p.id;

São os pedidos 86 a 90. O maior é o da Juliana Paes Barbosa (pedido 88, 649.47), com três caixas de som.

Versão 2: faturamento por ano e por mês

A pergunta mais clássica: quanto a loja vendeu em cada ano? A data do pedido está guardada como texto AAAA-MM-DD, e a função strftime recorta o pedaço que interessa (aula Funções de Data):

SELECT strftime('%Y', p.data)                   AS ano,
       COUNT(DISTINCT p.id)                     AS pedidos,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY ano;

strftime('%Y', p.data)

De '2024-01-05', tira só o ano: '2024'. Com '%Y-%m', tira ano e mês: '2024-01'.

GROUP BY ano

Agrupa pelo apelido da coluna. O SQLite aceita; outros bancos pedem a expressão inteira de novo (veja "Nos outros bancos", no fim).

COUNT(DISTINCT p.id)

Conta os pedidos diferentes. Um COUNT(*) aqui contaria as linhas do JOIN, ou seja, os itens: 220 em vez de 90 (aula COUNT()).

ROUND(…, 2)

Duas casas decimais, como dinheiro (aula Funções Numéricas).

Em 2024 foram 32 pedidos e 8280.8 em produtos; em 2025, os mesmos 32 pedidos renderam bem menos, 5464.52. E 2026 ainda não acabou: os dados vão até outubro. Comparar um ano inteiro com dez meses é injusto; o relatório por mês resolve:

SELECT strftime('%Y-%m', p.data)               AS mes,
       COUNT(DISTINCT p.id)                     AS pedidos,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.data >= '2026-01-01'
GROUP BY mes;

Outubro de 2026, com 1042.71, é o melhor mês do ano até agora, e agosto (97.19) o pior. O WHERE p.data >= '2026-01-01' funciona com texto porque o formato AAAA-MM-DD se ordena igual a uma data: esse é o grande motivo para guardar datas assim.

SELECT strftime('%Y', p.data) AS ano,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY ano;

SELECT strftime('%Y-%m', p.data) AS mes,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE strftime('%Y', p.data) = '2025'
GROUP BY mes
ORDER BY faturamento DESC;

Janeiro de 2025 lidera, com 865.78, e abril de 2025 fecha a lista com 25.13: dois pedidos pequenos, só de papelaria (caderno, canetas, lápis e marca-texto). Há outras formas de filtrar o ano, e todas cumprem a missão:

Versão 3: o ticket médio

O ticket médio é quanto um cliente gasta, em média, por compra: o total cobrado dividido pelo número de pedidos. A tentação é fazer um AVG direto no JOIN. Veja o que sai:

SELECT ROUND(AVG(i.quantidade * i.preco_unitario), 2) AS ticket_errado
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id;

84.26 é a média dos itens, não dos pedidos: o AVG calculou sobre as 220 linhas do JOIN. Um pedido tem 2 ou 3 itens, então a média por pedido tem de ser bem maior. Para acertar, primeiro calcule o total de cada pedido (a versão 1) e só depois tire a média desses 90 totais. O WITH dá um nome à consulta de dentro, como uma tabela provisória (aula CTE (WITH)):

WITH totais AS (
    SELECT p.id, p.data,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT COUNT(*)              AS pedidos,
       ROUND(SUM(total), 2)  AS faturado_com_frete,
       ROUND(AVG(total), 2)  AS ticket_medio,
       MIN(total)            AS menor,
       MAX(total)            AS maior
FROM totais;

WITH totais AS ( … )

A consulta da versão 1, sem LIMIT, ganha o nome totais: 90 linhas, uma por pedido.

FROM totais

A consulta de fora lê totais como se fosse uma tabela.

AVG(total)

Agora a média é sobre os 90 pedidos: 220.46. O mesmo resultado sai de SUM(total) / COUNT(*) (19841.55 / 90).

MIN e MAX

O pedido mais barato custou 13.25 e o mais caro, 660.0 (o pedido 3, com três fones de ouvido).

WITH totais AS (
    SELECT p.id, p.data,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT COUNT(*) AS pedidos, ROUND(AVG(total), 2) AS ticket_medio
FROM totais;

WITH totais AS (
    SELECT p.id, p.data,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT strftime('%Y', data) AS ano,
       COUNT(*)             AS pedidos,
       ROUND(AVG(total), 2) AS ticket_medio
FROM totais
GROUP BY ano;

O ticket caiu de 273.31 em 2024 para 185.3 em 2025 e voltou um pouco, para 198.7, em 2026. Como o número de pedidos por ano ficou parecido, é o valor de cada compra que explica a queda do faturamento de 2025, e não a falta de clientes. É o tipo de conclusão que só aparece quando o número está certo.

Versão 4: os melhores clientes

Quem são os clientes que mais gastaram na loja, somando todos os pedidos? Mesma base, agora agrupada por cliente:

WITH totais AS (
    SELECT p.id, p.cliente_id,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT c.nome,
       COUNT(*)             AS pedidos,
       ROUND(SUM(t.total), 2) AS gasto
FROM totais t
JOIN clientes c ON c.id = t.cliente_id
GROUP BY c.id
ORDER BY gasto DESC
LIMIT 5;

A Conceição Prado lidera, com 1398.03 em 4 pedidos. Repare na Ana Carvalho Lima, em segundo: com só 3 pedidos, ela gastou mais que a Juliana, que fez 4. O ORDER BY gasto DESC põe o maior em cima (aula ORDER BY) e o LIMIT 5 corta o "top 5" (aula LIMIT e OFFSET).

WITH totais AS (
    SELECT p.id, p.cliente_id,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT c.nome, COUNT(*) AS pedidos, ROUND(SUM(t.total), 2) AS vendido
FROM totais t
JOIN clientes c ON c.id = t.cliente_id
GROUP BY c.id
ORDER BY vendido DESC
LIMIT 3;

WITH totais AS (
    SELECT p.id, p.funcionario_id,
           SUM(i.quantidade * i.preco_unitario) + p.frete AS total
    FROM pedidos p
    JOIN itens_pedido i ON i.pedido_id = p.id
    GROUP BY p.id
)
SELECT f.nome, COUNT(*) AS pedidos, ROUND(SUM(t.total), 2) AS vendido
FROM totais t
JOIN funcionarios f ON f.id = t.funcionario_id
GROUP BY f.id
ORDER BY vendido DESC
LIMIT 3;

Todos os seis vendedores atenderam exatamente 15 pedidos; o que separa o Igor Mendes (3812.21) da Sofia Rocha, a última, é o valor de cada venda. E o Davi Nunes, o estoquista, não aparece: ele não atende pedidos.

Versão 5: o que ficou parado

Tão importante quanto o que vendeu é o que não vendeu: produto encalhado é dinheiro parado na prateleira. Um produto nunca vendido é um produto que não aparece em nenhuma linha de itens_pedido. O LEFT JOIN mantém todos os produtos e deixa NULL onde não achou par (aula LEFT JOIN):

SELECT p.id, p.nome, p.estoque,
       p.estoque * p.preco AS valor_parado
FROM produtos p
LEFT JOIN itens_pedido i ON i.produto_id = p.id
WHERE i.produto_id IS NULL;

LEFT JOIN itens_pedido i

Todo produto aparece, mesmo sem item. Os que nunca venderam vêm com as colunas de i todas NULL.

WHERE i.produto_id IS NULL

Fica só com esses: os que não acharam par.

p.estoque * p.preco

Quanto dinheiro está parado em cada um.

São três produtos de jardinagem: o Vaso de Cerâmica Médio, a Tesoura de Poda e as Luvas de Jardinagem. A mesma pergunta pode ser escrita de três jeitos, e todos dão os mesmos 3 produtos:

-- forma 2: NOT IN com subconsulta
SELECT id, nome FROM produtos
WHERE id NOT IN (SELECT produto_id FROM itens_pedido);

-- forma 3: NOT EXISTS
SELECT id, nome FROM produtos p
WHERE NOT EXISTS (SELECT 1 FROM itens_pedido i WHERE i.produto_id = p.id);

SELECT p.id, p.nome
FROM produtos p
LEFT JOIN itens_pedido i ON i.produto_id = p.id
WHERE i.produto_id IS NULL;

SELECT c.id, c.nome
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.id IS NULL;

São os clientes 27 a 30: o Paulo Sérgio Andrade, a Tainá Soares, o Elias Furtado e a Rosângela Dias. Cadastrados, com email, e sem nenhuma compra: um bom público para uma campanha de "primeira compra".

Versão 6: o ranking das categorias

Que categoria puxa as vendas da Aurora? Para isso, cada item precisa achar o seu produto e, pelo produto, a sua categoria: dois JOINs em fila.

SELECT c.nome AS categoria,
       SUM(i.quantidade)                              AS unidades,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento,
       RANK() OVER (ORDER BY SUM(i.quantidade * i.preco_unitario) DESC) AS posicao
FROM itens_pedido i
JOIN produtos p   ON p.id = i.produto_id
JOIN categorias c ON c.id = p.categoria_id
GROUP BY c.id
ORDER BY posicao;

itens_pedido → produtos → categorias

O item sabe o produto; o produto sabe a categoria. Os dois JOINs seguem as chaves estrangeiras que você criou no projeto a Loja Aurora Completa.

RANK() OVER (ORDER BY … DESC)

Uma função de janela: depois de agrupar, numera as linhas na ordem pedida. 1 para a maior venda, 2 para a seguinte… (aula ROW_NUMBER, RANK e DENSE_RANK).

ORDER BY posicao

O RANK calcula a posição, mas não ordena a saída; quem ordena é o ORDER BY.

Eletrônicos lidera com folga: 5999.05, quase um terço dos 18537.4 de faturamento total. E repare na Papelaria: é a segunda em unidades (60), mas a sétima em dinheiro, porque vende lápis de 1.15 e canetas de 2.79. "O que mais vende" depende de você medir em unidades ou em reais. Jardim fica em último, e agora você sabe por quê: três dos seus cinco produtos nunca venderam.

Com produtos, o ranking fica mais interessante, porque aparecem empates. Veja os mais vendidos em unidades:

SELECT pr.nome,
       SUM(i.quantidade) AS unidades,
       RANK() OVER (ORDER BY SUM(i.quantidade) DESC) AS posicao
FROM itens_pedido i
JOIN produtos pr ON pr.id = i.produto_id
GROUP BY pr.id
ORDER BY posicao
LIMIT 7;

Três produtos empatam em 12 unidades e todos ganham a posição 2; o seguinte pula para a 5. É assim que o RANK trata empates, como numa corrida com dois segundos lugares. E veja o problema do LIMIT: o Conjunto de Copos e o Estojo Duplo empatam na posição 5, e um LIMIT 5 cortaria um deles sem critério nenhum.

SELECT pr.nome,
       SUM(i.quantidade) AS unidades,
       RANK() OVER (ORDER BY SUM(i.quantidade) DESC) AS posicao
FROM itens_pedido i
JOIN produtos pr ON pr.id = i.produto_id
GROUP BY pr.id
ORDER BY posicao
LIMIT 5;

WITH ranking AS (
    SELECT pr.nome,
           SUM(i.quantidade) AS unidades,
           RANK() OVER (ORDER BY SUM(i.quantidade) DESC) AS posicao
    FROM itens_pedido i
    JOIN produtos pr ON pr.id = i.produto_id
    GROUP BY pr.id
)
SELECT nome, unidades, posicao
FROM ranking
WHERE posicao <= 5
ORDER BY posicao;

Seis produtos no "top 5": é o certo, porque dois empataram no quinto lugar. Se a Marta quisesse posições sem buraco (1, 2, 2, 2, 3…), a função seria DENSE_RANK(); se quisesse exatamente 5 linhas desempatando por algum critério, ROW_NUMBER() com um segundo item no ORDER BY.

Versão 7: o painel, com uma VIEW

Você escreveu o mesmo WITH totais quatro vezes. Num banco de verdade, essa consulta-base vira uma VIEW: uma consulta guardada com nome, que se usa como tabela (aula Views). Escreve-se uma vez e todo relatório aproveita:

CREATE VIEW totais_pedido AS
SELECT p.id, p.cliente_id, p.funcionario_id, p.data, p.frete,
       SUM(i.quantidade * i.preco_unitario)           AS produtos,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id;

SELECT * FROM totais_pedido WHERE id <= 3;

A view não copia dados: cada vez que você lê totais_pedido, o SQLite roda a consulta de novo. Se amanhã entrar um pedido, ele já aparece. No editor, ela dura só até o fim do Rodar; num arquivo, ela fica guardada para sempre, como uma tabela.

CREATE VIEW totais_pedido AS
SELECT p.id, p.cliente_id, p.funcionario_id, p.data, p.frete,
       SUM(i.quantidade * i.preco_unitario)           AS produtos,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id;

-- o painel: uma linha por ano
SELECT * FROM totais_pedido LIMIT 3;

CREATE VIEW totais_pedido AS
SELECT p.id, p.cliente_id, p.funcionario_id, p.data, p.frete,
       SUM(i.quantidade * i.preco_unitario)           AS produtos,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id;

-- o painel: uma linha por ano
SELECT strftime('%Y', data)    AS ano,
       COUNT(*)                AS pedidos,
       ROUND(SUM(produtos), 2) AS faturamento,
       ROUND(SUM(frete), 2)    AS frete,
       ROUND(AVG(total), 2)    AS ticket_medio
FROM totais_pedido
GROUP BY ano;

Repare que aqui o frete pode ser somado sem medo: a view tem uma linha por pedido, então cada frete aparece uma vez só. Esse é o próximo assunto.

Quando dá errado

Relatório errado raramente dá mensagem de erro. Ele dá um número, com cara de certo. Os casos abaixo rodam sem reclamar, e é exatamente isso que os torna perigosos.

SELECT SUM(i.quantidade * i.preco_unitario + p.frete) AS total_errado
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id;

SELECT (SELECT SUM(quantidade * preco_unitario) FROM itens_pedido)
     + (SELECT SUM(frete) FROM pedidos) AS total_certo;

SELECT c.nome, SUM(i.quantidade * i.preco_unitario) AS gasto
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN itens_pedido i ON i.pedido_id = p.id;

SELECT p.id, SUM(i.quantidade * i.preco_unitario) AS produtos
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE SUM(i.quantidade * i.preco_unitario) > 500
GROUP BY p.id;

SELECT strftime('%Y-%m', '2024-01-05') AS formato_iso,
       strftime('%Y-%m', '05/01/2024') AS formato_br;

-- PostgreSQL
SELECT to_char(data, 'YYYY-MM') AS mes, ... GROUP BY 1;
-- MySQL / MariaDB
SELECT DATE_FORMAT(data, '%Y-%m') AS mes, ... GROUP BY mes;
-- SQL Server
SELECT FORMAT(data, 'yyyy-MM') AS mes, ... GROUP BY FORMAT(data, 'yyyy-MM');

Referência rápida

Exercícios

Desafio: o melhor mês de todos

SELECT strftime('%Y', p.data) AS ano,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY ano
ORDER BY faturamento DESC;

SELECT strftime('%Y-%m', p.data) AS mes,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY mes
ORDER BY faturamento DESC
LIMIT 3;

Fevereiro de 2024, com 1244.61. Nenhum mês de 2025 chega perto.

Resumo

• Antes de escrever um relatório, combine o que cada número significa: aqui, faturamento = produtos e total = produtos + frete.
• A base de quase tudo é o valor de cada pedido: JOIN itens_pedido + SUM(quantidade * preco_unitario) + GROUP BY p.id. Guarde-a numa VIEW ou num WITH.
• Depois de um JOIN de "um para muitos", a coluna do "um" se repete: SUM(frete) e COUNT(*) ficam inflados. Use COUNT(DISTINCT …) e some o frete numa consulta com uma linha por pedido.
• Média "por pedido" exige uma linha por pedido primeiro: o ticket médio é 220.46, não os 84.26 da média dos itens.
• strftime('%Y-%m', data) agrupa por mês; funciona porque as datas estão em AAAA-MM-DD.
• LEFT JOIN … IS NULL ou NOT EXISTS acham o que nunca aconteceu; RANK() OVER faz rankings com empates, filtrados por fora com WITH.