SQL para Análise de Dados no Dia a Dia: Consultas, Relatórios e Insights com Dados Reais

Novo curso

26 páginas

Todos os cursos > Informática ( TI ) > Banco de dados ::

Dashboards via queries: top N, tabelas de apoio e indicadores executivos

Capítulo 15

Tempo estimado de leitura: 0 minutos

+ Exercício

Dashboards são, na prática, um conjunto de visões consistentes e repetíveis sobre o negócio. Quando você constrói dashboards “via queries”, você está definindo consultas SQL como a fonte de verdade para cada cartão (KPI), tabela e gráfico. Isso traz vantagens importantes: rastreabilidade (você sabe exatamente como o número foi calculado), reprodutibilidade (o mesmo SQL roda todo dia) e governança (versões e revisões do cálculo ficam explícitas).

Neste capítulo, o foco é transformar necessidades comuns de dashboards em padrões de consulta: (1) top N para listas e rankings, (2) tabelas de apoio (lookup/dimensões auxiliares) para padronizar indicadores e categorias, e (3) indicadores executivos (KPIs) com definições claras, comparáveis e com “guardrails” para evitar leituras erradas.

1) O que significa “dashboard via queries”

Em um dashboard típico você tem componentes como: cartões de indicadores (ex.: Receita do mês, Margem, Pedidos), tabelas (ex.: Top 10 produtos), e gráficos (ex.: Receita por semana). Cada componente pode ser alimentado por uma query específica ou por uma “tabela de fatos agregada” que você gera com SQL e depois reaproveita.

Do ponto de vista de SQL, existem dois estilos comuns:

Query por visual: cada visualização tem sua própria consulta, com filtros e agregações específicas. É simples de começar, mas pode gerar divergências se cada query “reinventar” regras.

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
Camada semântica via tabelas/CTEs de apoio: você cria uma base padronizada (por exemplo, uma visão de vendas já com regras de cancelamento, devolução, status válidos) e, a partir dela, deriva KPIs e rankings. Isso reduz inconsistências e facilita manutenção.

O objetivo aqui é adotar padrões que funcionem bem para o dia a dia: queries legíveis, com parâmetros de período, com dimensões consistentes e com métricas comparáveis.

2) Top N em dashboards: padrões que evitam surpresas

Listas “Top N” aparecem em quase todo dashboard: top 10 produtos, top 5 vendedores, top 20 clientes, top 10 cidades. O desafio não é apenas ordenar e limitar; é garantir que o ranking seja consistente com o filtro do dashboard, que empates sejam tratados de forma previsível e que o “Outros” (o restante) possa ser calculado quando necessário.

2.1) Top N simples com critério bem definido

Para um dashboard, o top N precisa deixar explícito: (a) qual métrica ranqueia, (b) qual período e filtros aplicam, (c) qual dimensão define o “item” (produto, cliente, etc.). Um padrão comum é: agregar, ordenar e limitar.

SELECT  p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2025-02-01'
  AND o.status = 'PAID'
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 10;

Boas práticas para dashboards:

Ordene pelo alias da métrica (ex.: ORDER BY revenue DESC) para reduzir risco de trocar colunas.
Padronize filtros de status (ex.: considerar apenas pedidos pagos) para que todos os visuais “falem a mesma língua”.
Use intervalo semiaberto de datas (>= início e < fim) para evitar problemas com horário e inclusões duplicadas.

2.2) Top N com desempate determinístico

Em dashboards, empates são comuns (dois produtos com a mesma receita). Se você usar apenas ORDER BY revenue DESC, a ordem entre empatados pode variar entre execuções, o que causa “piscadas” no dashboard (itens trocando de posição sem mudança real).

Inclua um segundo critério estável de desempate, como o nome ou o ID:

SELECT  p.product_id, p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2025-02-01'
  AND o.status = 'PAID'
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC, p.product_id ASC
LIMIT 10;

Esse padrão é simples e resolve a instabilidade visual.

2.3) Top N por categoria (top N “dentro de cada grupo”)

Dashboards frequentemente pedem “Top 5 produtos por categoria” ou “Top 3 clientes por região”. Isso exige ranquear dentro de cada grupo. A ideia é: agregar por grupo e item, calcular posição dentro do grupo e filtrar as primeiras posições.

WITH revenue_by_cat AS (
  SELECT
    c.category_name,
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2025-02-01'
    AND o.status = 'PAID'
  GROUP BY c.category_name, p.product_id, p.product_name
)
SELECT *
FROM (
  SELECT
    category_name,
    product_id,
    product_name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category_name
      ORDER BY revenue DESC, product_id ASC
    ) AS rn
  FROM revenue_by_cat
) t
WHERE rn <= 5
ORDER BY category_name, rn;

Observações úteis para dashboards:

Use ROW_NUMBER quando você quer exatamente N linhas por grupo. Se quiser incluir empates, use RANK e esteja preparado para retornar mais de N linhas.
Ordene a saída final para facilitar leitura e para o dashboard não depender de ordenação implícita.

2.4) Top N + “Outros” (agrupando o restante)

Em gráficos de barras e pizzas, é comum exibir os top N e somar o resto em “Outros”. Isso evita poluição visual e mantém o foco. O padrão é: calcular o ranking, rotular top N e agrupar.

WITH revenue_by_product AS (
  SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2025-02-01'
    AND o.status = 'PAID'
  GROUP BY p.product_id, p.product_name
), ranked AS (
  SELECT
    product_id,
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC, product_id ASC) AS rn
  FROM revenue_by_product
)
SELECT
  CASE WHEN rn <= 10 THEN product_name ELSE 'Outros' END AS bucket,
  SUM(revenue) AS revenue
FROM ranked
GROUP BY CASE WHEN rn <= 10 THEN product_name ELSE 'Outros' END
ORDER BY revenue DESC;

Se o seu dashboard precisa sempre mostrar “Outros” mesmo quando há poucos itens, você pode tratar isso na camada de visualização; em SQL, normalmente “Outros” só aparece quando existe algo fora do top N.

3) Tabelas de apoio: padronização e governança de indicadores

Tabelas de apoio (lookup tables) são pequenas tabelas que ajudam a padronizar regras e rótulos. Em dashboards, elas são valiosas para: (a) mapear códigos para nomes, (b) agrupar categorias, (c) definir metas e faixas, (d) controlar quais métricas existem e como devem ser exibidas.

Sem tabelas de apoio, é comum ver CASE WHEN repetido em várias queries, com pequenas diferenças. Isso gera divergência de números e manutenção difícil.

3.1) Apoio para classificação e agrupamento (mapeamento de dimensões)

Imagine que o campo channel vem com valores variados: 'ads', 'paid_social', 'facebook_ads', 'organic', 'seo', etc. Para um dashboard executivo, você quer grupos consistentes: “Pago”, “Orgânico”, “Parcerias”. Em vez de repetir regras, crie uma tabela de apoio:

-- Exemplo de tabela de apoio
-- channel_map(raw_channel, channel_group)
-- ('ads', 'Pago')
-- ('paid_social', 'Pago')
-- ('facebook_ads', 'Pago')
-- ('organic', 'Orgânico')
-- ('seo', 'Orgânico')

Então, nas queries:

SELECT
  cm.channel_group,
  COUNT(DISTINCT o.order_id) AS orders,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
LEFT JOIN channel_map cm ON cm.raw_channel = o.channel
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2025-02-01'
  AND o.status = 'PAID'
GROUP BY cm.channel_group
ORDER BY revenue DESC;

Por que LEFT JOIN? Para não perder pedidos com canal desconhecido. Em dashboards, perder linhas silenciosamente é perigoso. Você pode ainda tratar nulos como “Não mapeado”:

SELECT
  COALESCE(cm.channel_group, 'Não mapeado') AS channel_group,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
LEFT JOIN channel_map cm ON cm.raw_channel = o.channel
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2025-02-01'
  AND o.status = 'PAID'
GROUP BY COALESCE(cm.channel_group, 'Não mapeado');

3.2) Apoio para metas e faixas (targets e semáforos)

Indicadores executivos quase sempre pedem contexto: “está bom ou ruim?”. Uma forma robusta é ter uma tabela de metas por período e/ou por unidade (empresa, região, time). Exemplo de tabela:

-- kpi_targets(kpi_name, period_start, period_end, target_value)
-- ('revenue', '2025-01-01', '2025-02-01', 500000)
-- ('orders',  '2025-01-01', '2025-02-01', 12000)

Você calcula o KPI e junta com a meta do período:

WITH kpi AS (
  SELECT
    DATE '2025-01-01' AS period_start,
    DATE '2025-02-01' AS period_end,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2025-02-01'
    AND o.status = 'PAID'
)
SELECT
  k.revenue,
  t.target_value AS revenue_target,
  (k.revenue - t.target_value) AS diff_to_target,
  CASE
    WHEN k.revenue >= t.target_value THEN 'Acima da meta'
    ELSE 'Abaixo da meta'
  END AS status
FROM kpi k
LEFT JOIN kpi_targets t
  ON t.kpi_name = 'revenue'
 AND t.period_start = k.period_start
 AND t.period_end = k.period_end;

Esse padrão permite que o dashboard mostre o número, a meta, o delta e um rótulo de status sem “hardcode” na query.

3.3) Apoio para catálogo de métricas (definições e consistência)

Em ambientes com muitos dashboards, vale ter um “catálogo” de métricas: nome, descrição, unidade, fórmula, fonte, periodicidade. Nem sempre isso vira uma tabela usada diretamente na query, mas pode ser uma tabela de apoio para governança e para o time saber o que cada KPI significa.

Quando você precisa que o dashboard liste KPIs dinamicamente (por exemplo, uma tabela com vários indicadores), uma abordagem é produzir uma query que retorna linhas no formato: kpi_name, kpi_value, period. Isso facilita a construção de painéis com “cards” repetíveis.

WITH base AS (
  SELECT
    DATE '2025-01-01' AS period_start,
    DATE '2025-02-01' AS period_end,
    o.order_id,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2025-02-01'
    AND o.status = 'PAID'
), agg AS (
  SELECT
    period_start,
    period_end,
    COUNT(DISTINCT order_id) AS orders,
    SUM(item_revenue) AS revenue,
    SUM(item_revenue) / NULLIF(COUNT(DISTINCT order_id), 0) AS avg_ticket
  FROM base
  GROUP BY period_start, period_end
)
SELECT 'revenue' AS kpi_name, revenue AS kpi_value, period_start, period_end FROM agg
UNION ALL
SELECT 'orders'  AS kpi_name, orders  AS kpi_value, period_start, period_end FROM agg
UNION ALL
SELECT 'avg_ticket' AS kpi_name, avg_ticket AS kpi_value, period_start, period_end FROM agg;

Esse formato “alto” (uma linha por KPI) é prático para dashboards que renderizam cards a partir de uma mesma fonte.

4) Indicadores executivos: definição, comparabilidade e guardrails

Indicadores executivos (KPIs) são métricas que precisam ser: (a) fáceis de interpretar, (b) comparáveis ao longo do tempo, (c) consistentes entre áreas. Em SQL, isso significa padronizar filtros, períodos, moedas/unidades e evitar distorções por duplicidade ou por mudanças de escopo.

4.1) Checklist de um KPI “pronto para dashboard”

Definição operacional: quais registros entram e quais saem (ex.: pedidos pagos, exclui cancelados).
Período: MTD (month-to-date), mês fechado, últimos 7 dias, etc. O KPI deve deixar isso explícito.
Dimensão de corte: empresa, região, canal, produto. O KPI deve responder bem a filtros do dashboard.
Tratamento de divisões: usar NULLIF para evitar divisão por zero e não quebrar o painel.
Consistência de moeda/unidade: não misturar centavos com reais, ou quantidades com valores.

4.2) KPIs com comparação (atual vs período anterior) em uma única query

Um dashboard executivo geralmente quer o número atual e a variação contra um período de referência (mês anterior, semana anterior, mesmo mês do ano anterior). Um padrão útil é calcular os dois períodos lado a lado e depois derivar delta e percentual.

WITH params AS (
  SELECT
    DATE '2025-01-01' AS cur_start,
    DATE '2025-02-01' AS cur_end,
    DATE '2024-12-01' AS prev_start,
    DATE '2025-01-01' AS prev_end
), revenue_by_period AS (
  SELECT
    CASE
      WHEN o.order_date >= p.cur_start AND o.order_date < p.cur_end THEN 'current'
      WHEN o.order_date >= p.prev_start AND o.order_date < p.prev_end THEN 'previous'
    END AS period_label,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM params p
  JOIN orders o
    ON (o.order_date >= p.cur_start AND o.order_date < p.cur_end)
    OR (o.order_date >= p.prev_start AND o.order_date < p.prev_end)
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'PAID'
  GROUP BY 1
), pivot AS (
  SELECT
    SUM(CASE WHEN period_label = 'current' THEN revenue END) AS revenue_current,
    SUM(CASE WHEN period_label = 'previous' THEN revenue END) AS revenue_previous
  FROM revenue_by_period
)
SELECT
  revenue_current,
  revenue_previous,
  (revenue_current - revenue_previous) AS delta_abs,
  (revenue_current - revenue_previous) / NULLIF(revenue_previous, 0) AS delta_pct
FROM pivot;

Esse padrão é útil para cards que exibem “R$ X” e “+Y% vs mês anterior”.

4.3) Indicadores com segmentação executiva (ex.: por região e com total)

Executivos costumam querer o total e o detalhamento por uma dimensão-chave, no mesmo visual (ou em visuais alinhados). Um padrão é produzir uma saída que inclua linhas por região e uma linha “Total”.

WITH base AS (
  SELECT
    r.region_name,
    o.order_id,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN regions r ON r.region_id = o.region_id
  WHERE o.order_date >= DATE '2025-01-01'
    AND o.order_date <  DATE '2025-02-01'
    AND o.status = 'PAID'
)
SELECT
  region_name,
  SUM(item_revenue) AS revenue,
  COUNT(DISTINCT order_id) AS orders
FROM base
GROUP BY region_name
UNION ALL
SELECT
  'Total' AS region_name,
  SUM(item_revenue) AS revenue,
  COUNT(DISTINCT order_id) AS orders
FROM base
ORDER BY revenue DESC;

Se o seu banco suportar GROUPING SETS, você pode fazer total e detalhamento de forma mais elegante, mas o padrão com UNION ALL é amplamente compatível e fácil de entender.

5) Passo a passo prático: montando um “pacote” de queries para um dashboard

A seguir, um roteiro prático para montar as queries de um dashboard executivo típico de vendas, com top N e tabelas de apoio. A ideia é você sair com um conjunto de consultas que podem alimentar: (1) cards de KPI, (2) tabela top N, (3) gráfico por grupo, (4) comparativo com meta.

Passo 1: Defina parâmetros de período e filtros padrão

Escolha um período (por exemplo, mês fechado) e padronize o filtro de status. Mesmo que seu dashboard permita filtros dinâmicos, ter um “esqueleto” com parâmetros ajuda a evitar divergências.

WITH params AS (
  SELECT
    DATE '2025-01-01' AS start_date,
    DATE '2025-02-01' AS end_date
)
SELECT * FROM params;

Passo 2: Crie uma base única (fato) para reaproveitar

Monte uma CTE base com as colunas necessárias para a maioria dos visuais: data, região, canal (mapeado), produto, receita por item, pedido. Essa base vira o “alicerce” do dashboard.

WITH params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-02-01' AS end_date
), base AS (
  SELECT
    o.order_date,
    o.order_id,
    r.region_name,
    COALESCE(cm.channel_group, 'Não mapeado') AS channel_group,
    p.product_id,
    p.product_name,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM params pr
  JOIN orders o
    ON o.order_date >= pr.start_date AND o.order_date < pr.end_date
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p ON p.product_id = oi.product_id
  JOIN regions r ON r.region_id = o.region_id
  LEFT JOIN channel_map cm ON cm.raw_channel = o.channel
  WHERE o.status = 'PAID'
)
SELECT * FROM base;

Mesmo que você não materialize isso como tabela/visão, estruturar assim facilita manter consistência.

Passo 3: Gere os KPIs executivos (cards)

Com a base pronta, KPIs ficam diretos. Exemplo: receita, pedidos e ticket médio.

WITH params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-02-01' AS end_date
), base AS (
  SELECT
    o.order_id,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM params pr
  JOIN orders o
    ON o.order_date >= pr.start_date AND o.order_date < pr.end_date
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'PAID'
), agg AS (
  SELECT
    SUM(item_revenue) AS revenue,
    COUNT(DISTINCT order_id) AS orders,
    SUM(item_revenue) / NULLIF(COUNT(DISTINCT order_id), 0) AS avg_ticket
  FROM base
)
SELECT * FROM agg;

Passo 4: Conecte KPIs com metas (tabela de apoio)

Agora junte com kpi_targets para trazer contexto.

WITH params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-02-01' AS end_date
), kpi AS (
  SELECT
    pr.start_date AS period_start,
    pr.end_date AS period_end,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM params pr
  JOIN orders o
    ON o.order_date >= pr.start_date AND o.order_date < pr.end_date
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'PAID'
  GROUP BY pr.start_date, pr.end_date
)
SELECT
  k.revenue,
  t.target_value AS target,
  (k.revenue - t.target_value) AS diff_to_target,
  (k.revenue - t.target_value) / NULLIF(t.target_value, 0) AS diff_pct
FROM kpi k
LEFT JOIN kpi_targets t
  ON t.kpi_name = 'revenue'
 AND t.period_start = k.period_start
 AND t.period_end = k.period_end;

Passo 5: Monte o Top N (tabela do dashboard) com desempate

Use a mesma base e agregue por produto. Isso garante que o top N está alinhado com os KPIs.

WITH params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-02-01' AS end_date
), base AS (
  SELECT
    p.product_id,
    p.product_name,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM params pr
  JOIN orders o
    ON o.order_date >= pr.start_date AND o.order_date < pr.end_date
  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'
), revenue_by_product AS (
  SELECT
    product_id,
    product_name,
    SUM(item_revenue) AS revenue
  FROM base
  GROUP BY product_id, product_name
)
SELECT
  product_id,
  product_name,
  revenue
FROM revenue_by_product
ORDER BY revenue DESC, product_id ASC
LIMIT 10;

Passo 6: Crie um indicador “executivo” por grupo (ex.: canal) para gráfico

Para um gráfico de barras por canal, use a tabela de apoio de canal e agregue.

WITH params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-02-01' AS end_date
), base AS (
  SELECT
    COALESCE(cm.channel_group, 'Não mapeado') AS channel_group,
    (oi.quantity * oi.unit_price) AS item_revenue
  FROM params pr
  JOIN orders o
    ON o.order_date >= pr.start_date AND o.order_date < pr.end_date
  JOIN order_items oi ON oi.order_id = o.order_id
  LEFT JOIN channel_map cm ON cm.raw_channel = o.channel
  WHERE o.status = 'PAID'
)
SELECT
  channel_group,
  SUM(item_revenue) AS revenue
FROM base
GROUP BY channel_group
ORDER BY revenue DESC;

Se o dashboard permitir filtro por canal, essa query já está pronta para responder de forma consistente.

6) Armadilhas comuns em dashboards baseados em SQL (e como prevenir)

6.1) Divergência de regras entre visuais

Um card de Receita filtra status = 'PAID', mas a tabela top N esquece esse filtro. O dashboard passa a mostrar números que “não batem”. Prevenção: centralize a base (CTE/visão) e derive tudo dela.

6.2) Instabilidade por empates no ranking

Itens trocam de posição sem mudança real. Prevenção: sempre adicione desempate estável no ORDER BY do ranking.

6.3) “Não mapeado” invisível

Quando você usa INNER JOIN com tabela de apoio, valores não mapeados somem. Prevenção: LEFT JOIN + COALESCE para manter visibilidade e permitir correção do mapeamento.

6.4) KPIs quebrando por divisão por zero

Ticket médio, conversão e outras razões podem estourar. Prevenção: NULLIF(denominador, 0) e, se necessário, tratar nulos na camada de visualização.

6.5) Top N inconsistente com filtros do dashboard

Se o dashboard filtra por região, mas o top N não respeita o filtro, o usuário vê uma lista “global” enquanto o KPI é regional. Prevenção: garantir que a query do top N use os mesmos parâmetros/filtros (ou a mesma base) do restante do painel.

Agora responda o exercício sobre o conteúdo:

Em um dashboard que exibe Top N produtos, qual prática ajuda a evitar que itens empatados troquem de posição entre execuções sem mudança real nos dados?

Você acertou! Parabéns, agora siga para a próxima página

Você errou! Tente novamente.

Empates no ranking podem gerar instabilidade visual quando só se ordena pela métrica. Ao incluir um segundo critério determinístico (ex.: ID), a ordem entre empatados fica previsível e o Top N não pisca no dashboard.

Próximo capitúlo

Coortes simples e retenção básica com SQL orientado a produto e relacionamento