Projeto guiado: otimizando um schema com CTE, window function e transação

A lição 8 deixou o schema de e-commerce (usuarios, produtos, pedidos, itens_pedido) rodando com JOIN e agregação simples. Este projeto pega esse mesmo schema e aplica três coisas do intermediário: um relatório com CTE + window function, um checkout que precisa ser uma transação, e um índice criado a partir do que o EXPLAIN realmente mostrou — não de achismo.

1. Adicionando controle de estoque ao schema

ALTER TABLE produtos
  ADD COLUMN estoque INTEGER NOT NULL DEFAULT 0 CHECK (estoque >= 0);
-- CHECK garante que o estoque nunca fique negativo — nem por
-- um bug de aplicação, nem por uma migration futura mal escrita

2. Relatório: top 3 produtos mais vendidos, por categoria

Isso combina duas lições: uma CTE (9) que calcula o total vendido por produto, e uma window function (10) que ranqueia dentro de cada categoria sem colapsar as linhas.

WITH vendas_por_produto AS (
  SELECT
    produtos.id,
    produtos.nome,
    produtos.categoria,
    SUM(itens_pedido.quantidade) AS total_vendido
  FROM produtos
  JOIN itens_pedido ON itens_pedido.produto_id = produtos.id
  GROUP BY produtos.id, produtos.nome, produtos.categoria
),
ranking AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY categoria
      ORDER BY total_vendido DESC
    ) AS posicao
  FROM vendas_por_produto
)
SELECT nome, categoria, total_vendido, posicao
FROM ranking
WHERE posicao <= 3
ORDER BY categoria, posicao;

A primeira CTE resolve "quanto cada produto vendeu" — um passo isolado e nomeado. A segunda usa esse resultado como entrada pra ranquear dentro de cada categoria, sem precisar reescrever a agregação. Tentar fazer isso numa consulta só, com subqueries aninhadas, seria bem mais difícil de ler.

3. Checkout — por que precisa ser uma transação

Fechar um pedido não é um comando só: precisa criar o pedido, criar os itens do pedido, e debitar o estoque de cada produto vendido. Se o estoque for debitado sem o item ser registrado (ou vice-versa), o pedido fica num estado que nunca deveria existir — o mesmo problema da transferência bancária da lição 11.

BEGIN;

INSERT INTO pedidos (usuario_id) VALUES (1) RETURNING id;
-- suponha que retornou id = 501

INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario)
VALUES (501, 3, 2, 250.00);

UPDATE produtos SET estoque = estoque - 2 WHERE id = 3;
-- se o CHECK (estoque >= 0) for violado aqui (estoque insuficiente),
-- a transação inteira é abortada — nem o pedido, nem o item, nem
-- o débito de estoque ficam gravados

COMMIT;

Se o UPDATE falhasse por violar o CHECK de estoque, o comando certo seria ROLLBACK — desfazendo o INSERT do pedido e do item também, em vez de deixar um pedido "fantasma" sem estoque debitado.

4. Um índice guiado por EXPLAIN, não por achismo

EXPLAIN ANALYZE
SELECT produto_id, SUM(quantidade)
FROM itens_pedido
GROUP BY produto_id;

O Seq Scan on itens_pedido mostra que a consulta varre a tabela de itens inteira — se essa consulta roda toda vez que o relatório do passo 2 é aberto, vale investigar um índice.

CREATE INDEX idx_itens_pedido_produto_id ON itens_pedido (produto_id);

EXPLAIN ANALYZE
SELECT produto_id, SUM(quantidade)
FROM itens_pedido
GROUP BY produto_id;
-- compare o "Execution Time" antes e depois — só cria valor se
-- o plano realmente mudar pra usar o índice e o tempo cair de verdade