Desafios práticos comentados com gabarito para consolidar consultas e insights

Este capítulo é um conjunto de desafios práticos comentados (com gabarito) para consolidar a construção de consultas e a extração de insights. A proposta é simular situações reais de análise: você recebe uma pergunta de negócio, precisa traduzir para SQL, tomar decisões sobre recortes, lidar com detalhes que costumam quebrar relatórios (duplicidade, datas, status, devoluções, cancelamentos, metas), e entregar uma resposta auditável.

Para evitar repetição de conteúdo já visto, os desafios focam menos em “como funciona” cada recurso e mais em: (1) como interpretar a pergunta, (2) como escolher a estratégia de query, (3) como validar o resultado, e (4) quais armadilhas são comuns naquele tipo de pergunta. Sempre que houver mais de um caminho, o comentário explica o porquê da escolha.

Como usar os desafios

• Leia a pergunta e identifique: entidade principal (pedido, cliente, ticket, evento), período, filtros e métrica.
• Defina o grão do resultado: por dia? por mês? por cliente? por produto? Isso evita métricas “infladas”.
• Escreva uma versão mínima: primeiro traga o conjunto certo (linhas corretas), depois agregue e só então calcule taxas.
• Valide com checagens rápidas: contagens, somas, comparação com totais, e amostras.

Mini-base fictícia (referência dos desafios)

Os desafios assumem tabelas comuns em operações. Você não precisa criar as tabelas; use como referência mental dos campos.

• orders(order_id, customer_id, order_date, status, channel, total_amount)
• order_items(order_id, product_id, quantity, unit_price, discount_amount)
• products(product_id, category, product_name, active_flag)
• customers(customer_id, created_at, segment, city)
• payments(payment_id, order_id, paid_at, amount, method, status)
• tickets(ticket_id, customer_id, created_at, first_response_at, resolved_at, queue, status, csat_score)
• shipments(shipment_id, order_id, shipped_at, delivered_at, carrier, freight_cost)
• events(event_id, customer_id, event_time, event_name, session_id)

Desafio 1 — Receita líquida mensal (considerando cancelamentos e devoluções)

Pergunta

Qual a receita líquida por mês no último trimestre, considerando apenas pedidos pagos e não cancelados, e descontando devoluções registradas como itens com quantidade negativa?

Passo a passo prático

• Defina receita líquida: soma de (quantidade * unit_price - discount_amount) em itens, onde pedidos estão pagos e não cancelados.
• Trimestre: recorte por order_date (ou paid_at, dependendo da regra). Aqui a pergunta pede “receita” e “pagos”, então use paid_at para garantir caixa reconhecido.
• Devoluções: quantidade negativa reduz a receita automaticamente.

Gabarito (SQL)

WITH paid_orders AS (  SELECT DISTINCT p.order_id, p.paid_at  FROM payments p  WHERE p.status = 'paid'    AND p.paid_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'    AND p.paid_at <  DATE_TRUNC('quarter', CURRENT_DATE)), valid_orders AS (  SELECT o.order_id, po.paid_at  FROM orders o  JOIN paid_orders po ON po.order_id = o.order_id  WHERE o.status NOT IN ('canceled')) SELECT DATE_TRUNC('month', v.paid_at) AS month,        SUM(oi.quantity * oi.unit_price - COALESCE(oi.discount_amount, 0)) AS net_revenue FROM valid_orders v JOIN order_items oi ON oi.order_id = v.order_id GROUP BY 1 ORDER BY 1;

Comentário e armadilhas

• Pagamento duplicado: se houver mais de um registro em payments por pedido (parcelas, retentativas), o DISTINCT em paid_orders evita multiplicar itens.
• Data de reconhecimento: se o negócio reconhece receita por entrega, troque paid_at por delivered_at (shipments) e ajuste a lógica.
• Desconto nulo: COALESCE evita que a soma vire nula em alguns bancos.

Desafio 2 — Top 10 produtos por margem estimada (com regra de custo)

Pergunta

Liste os 10 produtos com maior margem estimada no mês atual. Margem estimada = receita do item - (frete rateado por pedido) - custo fixo por item (assuma custo fixo de 30% do unit_price). Considere apenas pedidos entregues.

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

Passo a passo prático

• Escolha o grão: margem por produto no mês.
• Foco em entregues: use delivered_at para garantir que o pedido completou.
• Rateio de frete: frete do pedido dividido pela quantidade total de itens do pedido (ou por valor). A pergunta pede “por pedido”, então rateio por quantidade é aceitável.

Gabarito (SQL)

WITH delivered AS (  SELECT s.order_id, s.delivered_at, COALESCE(s.freight_cost,0) AS freight_cost  FROM shipments s  WHERE s.delivered_at >= DATE_TRUNC('month', CURRENT_DATE)    AND s.delivered_at <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'), item_base AS (  SELECT oi.order_id, oi.product_id, oi.quantity, oi.unit_price, COALESCE(oi.discount_amount,0) AS discount_amount  FROM order_items oi), qty_per_order AS (  SELECT order_id, SUM(quantity) AS total_qty  FROM item_base  GROUP BY 1), item_with_freight AS (  SELECT i.order_id, i.product_id, i.quantity, i.unit_price, i.discount_amount,         d.freight_cost / NULLIF(q.total_qty,0) AS freight_per_unit  FROM item_base i  JOIN delivered d ON d.order_id = i.order_id  JOIN qty_per_order q ON q.order_id = i.order_id) SELECT p.product_id, p.product_name,        SUM(i.quantity * (i.unit_price - i.discount_amount)                 - i.quantity * (0.30 * i.unit_price)                 - i.quantity * COALESCE(i.freight_per_unit,0)) AS estimated_margin FROM item_with_freight i JOIN products p ON p.product_id = i.product_id GROUP BY 1,2 ORDER BY estimated_margin DESC LIMIT 10;

Comentário e armadilhas

• Rateio com total_qty = 0: NULLIF evita divisão por zero.
• Devoluções: se quantity negativa existir, ela reduz receita e também “devolve” custo e frete; confirme se essa é a regra desejada.
• Frete por pedido vs por item: se o frete é por pedido, o rateio precisa de uma regra explícita; documente no relatório.

Desafio 3 — Clientes “reativados” (voltaram a comprar após 90 dias)

Pergunta

Quantos clientes foram reativados por mês no último semestre? Definição: cliente que fez um pedido no mês e estava há pelo menos 90 dias sem comprar antes desse pedido.

Passo a passo prático

• Defina compra válida: pedidos não cancelados e pagos (ou entregues). Use uma regra consistente.
• Ordene compras por cliente: para comparar a compra atual com a anterior.
• Reativação: diferença entre datas >= 90 dias.

Gabarito (SQL)

WITH valid_purchases AS (  SELECT o.customer_id, o.order_id, o.order_date  FROM orders o  WHERE o.status NOT IN ('canceled')    AND o.order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months' - INTERVAL '90 days'), purchases_with_prev AS (  SELECT customer_id, order_id, order_date,         LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date  FROM valid_purchases) SELECT DATE_TRUNC('month', order_date) AS month,        COUNT(DISTINCT customer_id) AS reactivated_customers FROM purchases_with_prev WHERE prev_order_date IS NOT NULL   AND order_date - prev_order_date >= INTERVAL '90 days'   AND order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months' GROUP BY 1 ORDER BY 1;

Comentário e armadilhas

• Janela de busca: para medir 90 dias, você precisa trazer compras anteriores ao semestre (por isso o “- 90 days” no recorte inicial). Se você cortar cedo demais, reativações viram “primeira compra” e somem.
• Primeira compra: não conta como reativação (prev_order_date nulo).

Desafio 4 — SLA de primeira resposta por fila (com percentil)

Pergunta

Para cada fila de atendimento, calcule no mês passado: total de tickets, % respondidos em até 2 horas e P90 do tempo de primeira resposta (em minutos). Considere apenas tickets com first_response_at preenchido.

Passo a passo prático

• Tempo de resposta: first_response_at - created_at.
• Base elegível: excluir nulos em first_response_at.
• P90: use função de percentil do seu banco (ex.: PostgreSQL percentile_cont). Se não houver, aproxime com ranking.

Gabarito (SQL - PostgreSQL)

WITH base AS (  SELECT queue,         EXTRACT(EPOCH FROM (first_response_at - created_at))/60.0 AS frt_minutes  FROM tickets  WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'    AND created_at <  DATE_TRUNC('month', CURRENT_DATE)    AND first_response_at IS NOT NULL) SELECT queue,        COUNT(*) AS tickets_responded,        AVG(CASE WHEN frt_minutes <= 120 THEN 1.0 ELSE 0.0 END) AS pct_within_2h,        PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY frt_minutes) AS p90_frt_minutes FROM base GROUP BY 1 ORDER BY tickets_responded DESC;

Comentário e armadilhas

• Tickets sem resposta: a pergunta pede considerar apenas com first_response_at; se quiser medir “backlog”, crie outro indicador separado.
• Unidade: padronize em minutos e deixe explícito no nome da coluna.
• Percentil em outros bancos: em SQL Server há PERCENTILE_CONT; em MySQL pode exigir abordagem alternativa.

Desafio 5 — Taxa de conversão por canal (visita → compra em 7 dias)

Pergunta

Por canal de aquisição (customers.segment ou orders.channel), calcule a taxa de conversão de visitantes para compradores: clientes que tiveram evento “visit” e fizeram o primeiro pedido em até 7 dias após a primeira visita. Período: últimos 30 dias de primeiras visitas.

Passo a passo prático

• Primeira visita por cliente: min(event_time) onde event_name = 'visit'.
• Primeiro pedido por cliente: min(order_date) em pedidos válidos.
• Conversão: primeiro pedido entre [primeira visita, primeira visita + 7 dias].
• Canal: escolha uma fonte consistente. Se usar orders.channel, só existe para quem comprou; para taxa, prefira customers.segment (ou um campo de aquisição no cadastro).

Gabarito (SQL)

WITH first_visit AS (  SELECT customer_id, MIN(event_time) AS first_visit_at  FROM events  WHERE event_name = 'visit'    AND event_time >= CURRENT_DATE - INTERVAL '30 days'  GROUP BY 1), first_order AS (  SELECT customer_id, MIN(order_date) AS first_order_at  FROM orders  WHERE status NOT IN ('canceled')  GROUP BY 1) SELECT c.segment,        COUNT(*) AS visitors,        SUM(CASE WHEN fo.first_order_at IS NOT NULL                  AND fo.first_order_at >= fv.first_visit_at                  AND fo.first_order_at <  fv.first_visit_at + INTERVAL '7 days'                 THEN 1 ELSE 0 END) AS converted_7d,        1.0 * SUM(CASE WHEN fo.first_order_at IS NOT NULL                  AND fo.first_order_at >= fv.first_visit_at                  AND fo.first_order_at <  fv.first_visit_at + INTERVAL '7 days'                 THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS conversion_rate_7d FROM first_visit fv JOIN customers c ON c.customer_id = fv.customer_id LEFT JOIN first_order fo ON fo.customer_id = fv.customer_id GROUP BY 1 ORDER BY visitors DESC;

Comentário e armadilhas

• Viés de canal: se o canal só aparece no pedido, você não consegue calcular taxa corretamente por canal sem atribuição também para não compradores.
• Primeira visita no período: o recorte é sobre a primeira visita nos últimos 30 dias, não sobre qualquer visita.

Desafio 6 — Detecção de anomalia simples: queda de receita dia a dia

Pergunta

Liste os dias dos últimos 45 dias em que a receita caiu mais de 30% em relação ao dia anterior (considerando apenas pedidos pagos). Traga também a variação percentual.

Passo a passo prático

• Agregue por dia: receita diária.
• Compare com dia anterior: usar LAG na série diária.
• Queda > 30%: (hoje - ontem) / ontem <= -0.30.

Gabarito (SQL)

WITH paid AS (  SELECT p.order_id, DATE_TRUNC('day', p.paid_at) AS day  FROM payments p  WHERE p.status = 'paid'    AND p.paid_at >= CURRENT_DATE - INTERVAL '45 days'), daily_revenue AS (  SELECT pa.day,         SUM(oi.quantity * oi.unit_price - COALESCE(oi.discount_amount,0)) AS revenue  FROM paid pa  JOIN order_items oi ON oi.order_id = pa.order_id  GROUP BY 1), with_prev AS (  SELECT day, revenue,         LAG(revenue) OVER (ORDER BY day) AS prev_revenue  FROM daily_revenue) SELECT day, revenue, prev_revenue,        (revenue - prev_revenue) / NULLIF(prev_revenue,0) AS pct_change FROM with_prev WHERE prev_revenue IS NOT NULL   AND (revenue - prev_revenue) / NULLIF(prev_revenue,0) <= -0.30 ORDER BY day;

Comentário e armadilhas

• Dias sem receita: se não existir linha para um dia, a comparação “pula” datas. Para uma série completa, você precisaria de uma tabela calendário. Se o seu negócio exige isso, gere a série e faça LEFT JOIN.
• Ontem = 0: NULLIF evita divisão por zero; nesses casos, trate separadamente (ex.: ontem zero e hoje > 0 é crescimento infinito).

Desafio 7 — Qualidade de dados: pedidos sem entrega e entregas sem pedido

Pergunta

Encontre (a) pedidos pagos há mais de 10 dias que ainda não têm delivered_at e (b) registros de shipments que não possuem order correspondente. Traga contagens e uma amostra de IDs.

Passo a passo prático

• Parte (a): pagos >= 10 dias atrás, sem entrega.
• Parte (b): shipments órfãos.
• Amostra: use LIMIT e ordenação para inspecionar.

Gabarito (SQL)

-- (a) Pagos há mais de 10 dias sem entrega SELECT COUNT(DISTINCT p.order_id) AS paid_no_delivery FROM payments p LEFT JOIN shipments s ON s.order_id = p.order_id WHERE p.status = 'paid'   AND p.paid_at < CURRENT_DATE - INTERVAL '10 days'   AND (s.delivered_at IS NULL);  -- Amostra SELECT DISTINCT p.order_id, p.paid_at FROM payments p LEFT JOIN shipments s ON s.order_id = p.order_id WHERE p.status = 'paid'   AND p.paid_at < CURRENT_DATE - INTERVAL '10 days'   AND s.delivered_at IS NULL ORDER BY p.paid_at ASC LIMIT 20;  -- (b) Shipments sem pedido SELECT COUNT(*) AS orphan_shipments FROM shipments s LEFT JOIN orders o ON o.order_id = s.order_id WHERE o.order_id IS NULL;  -- Amostra SELECT s.shipment_id, s.order_id, s.shipped_at FROM shipments s LEFT JOIN orders o ON o.order_id = s.order_id WHERE o.order_id IS NULL ORDER BY s.shipped_at DESC LIMIT 20;

Comentário e armadilhas

• Pagamentos múltiplos: se houver mais de um payment por pedido, use DISTINCT para não inflar contagem.
• Entrega parcial: se existir mais de um shipment por pedido, a lógica “sem delivered_at” precisa considerar o conjunto (ex.: nenhum shipment entregue). Ajuste com agregação por order_id.

Desafio 8 — Relatório executivo: 5 KPIs em uma única saída

Pergunta

Monte uma saída com 5 KPIs do mês atual: pedidos pagos, receita líquida, ticket médio, % pedidos com desconto, e tempo médio de entrega (em dias) para pedidos entregues no mês.

Passo a passo prático

• Evite misturar bases: pedidos pagos (payments) e entregues (shipments) têm universos diferentes; deixe claro no KPI.
• Uma linha: use subconsultas/CTEs e faça SELECT final com colunas.
• Ticket médio: receita / pedidos pagos (no mesmo universo).

Gabarito (SQL)

WITH paid_orders AS (  SELECT DISTINCT p.order_id  FROM payments p  WHERE p.status = 'paid'    AND p.paid_at >= DATE_TRUNC('month', CURRENT_DATE)    AND p.paid_at <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'), revenue AS (  SELECT SUM(oi.quantity * oi.unit_price - COALESCE(oi.discount_amount,0)) AS net_revenue  FROM paid_orders po  JOIN order_items oi ON oi.order_id = po.order_id), orders_cnt AS (  SELECT COUNT(*) AS paid_orders  FROM paid_orders), discounted AS (  SELECT COUNT(DISTINCT oi.order_id) AS orders_with_discount  FROM paid_orders po  JOIN order_items oi ON oi.order_id = po.order_id  WHERE COALESCE(oi.discount_amount,0) > 0), delivery_time AS (  SELECT AVG(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at))/86400.0) AS avg_delivery_days  FROM shipments s  WHERE s.delivered_at >= DATE_TRUNC('month', CURRENT_DATE)    AND s.delivered_at <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'    AND s.shipped_at IS NOT NULL) SELECT oc.paid_orders,        r.net_revenue,        r.net_revenue / NULLIF(oc.paid_orders,0) AS avg_ticket,        1.0 * d.orders_with_discount / NULLIF(oc.paid_orders,0) AS pct_orders_with_discount,        dt.avg_delivery_days FROM orders_cnt oc CROSS JOIN revenue r CROSS JOIN discounted d CROSS JOIN delivery_time dt;

Comentário e armadilhas

• Universos diferentes: tempo de entrega está no universo “entregues no mês”, enquanto pedidos pagos está no universo “pagos no mês”. Se o executivo exigir consistência, restrinja ambos ao mesmo conjunto (ex.: pedidos entregues e pagos).
• Desconto por item vs por pedido: aqui “pedido com desconto” significa “existe ao menos um item com desconto”. Se a regra for “desconto total do pedido > 0”, ajuste.