Projeto aplicado com logística: prazos, atrasos, custos e eficiência operacional

Neste projeto aplicado, você vai construir um conjunto de análises SQL voltadas para logística, com foco em prazos, atrasos, custos e eficiência operacional. A ideia é sair de indicadores isolados e montar uma visão operacional que responda perguntas como: onde estamos atrasando, por quê, quanto custa, quais rotas/transportadoras são mais eficientes e como priorizar ações.

O objetivo é produzir consultas que possam alimentar relatórios recorrentes (diários/semanais) e também apoiar investigações pontuais (ex.: “por que o CD X piorou na última semana?”). Ao longo do capítulo, você verá um passo a passo prático com um modelo de dados típico e queries que você pode adaptar ao seu contexto.

Modelo de dados do projeto (visão logística)

Considere um cenário de e-commerce/distribuição com pedidos, expedição e entregas. Um modelo mínimo para análises do dia a dia pode ser:

• shipments: 1 linha por remessa/entrega (shipment_id, order_id, customer_id, origin_dc_id, carrier_id, service_level, shipped_at, delivered_at, promised_delivery_date, status, distance_km, weight_kg, volume_m3)
• shipment_events: eventos de rastreio (shipment_id, event_time, event_type, event_location, event_reason_code)
• shipment_costs: custos (shipment_id, cost_type, amount, currency, billed_at)
• carriers: transportadoras (carrier_id, carrier_name, carrier_type)
• distribution_centers: CDs (dc_id, dc_name, region)
• routes (opcional): rota padrão (origin_dc_id, destination_region, planned_transit_days)

Você não precisa ter todas as tabelas para aplicar as ideias. O essencial para prazos e atrasos é ter shipped_at, delivered_at e uma referência de promessa (ex.: promised_delivery_date ou SLA em dias). Para custos, basta um fato de custos por remessa.

Conceitos-chave: prazo, atraso, lead time e eficiência

Prazo prometido vs. prazo realizado

Em logística, “prazo” costuma aparecer em duas formas:

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

• Prazo prometido: data/tempo que foi comunicado ao cliente (ex.: entregar até 2026-01-10).
• Prazo realizado: quando a entrega de fato ocorreu (ex.: entregue em 2026-01-12).

O atraso é a diferença entre o realizado e o prometido, geralmente em dias (ou horas). Uma definição prática:

• On-time: entregue até a data prometida (incluindo o próprio dia).
• Atrasado: entregue após a data prometida.
• Pendente: ainda não entregue (não dá para medir atraso final, mas dá para medir risco).

Lead time e suas etapas

Para eficiência operacional, é útil separar o tempo total em etapas:

• Tempo de expedição: do pedido até shipped_at (depende de picking/packing/CD).
• Tempo de transporte: de shipped_at até delivered_at (depende de transportadora/rota).

Neste capítulo, vamos focar no recorte de remessas (shipments). Se você tiver o timestamp do pedido, pode estender para o tempo de expedição.

Eficiência operacional (custo x desempenho)

Eficiência não é só “entregar rápido”. Em geral, você quer equilibrar:

• Qualidade: taxa de entregas no prazo, baixo índice de ocorrências.
• Velocidade: transit time mediano/percentis.
• Custo: custo por remessa, custo por kg, custo por km, custo por entrega no prazo.

Uma análise madura compara transportadoras/serviços/rotas em um mesmo patamar (ex.: mesma região e faixa de peso), para evitar conclusões injustas.

Passo a passo prático: construir a base analítica de entregas

Passo 1: criar uma “tabela base” com prazos e flags

Comece criando uma visão (ou CTE) com os campos essenciais e alguns derivados: dias em trânsito, atraso em dias, flag de on-time e status simplificado. Isso vira a base para quase todas as perguntas.

WITH base_shipments AS (  SELECT     s.shipment_id,    s.order_id,    s.origin_dc_id,    s.carrier_id,    s.service_level,    s.shipped_at,    s.delivered_at,    s.promised_delivery_date,    s.status,    s.distance_km,    s.weight_kg,    CASE       WHEN s.delivered_at IS NOT NULL THEN DATE(s.delivered_at)       ELSE NULL     END AS delivered_date,    CASE       WHEN s.shipped_at IS NOT NULL THEN DATE(s.shipped_at)       ELSE NULL     END AS shipped_date,    CASE       WHEN s.delivered_at IS NOT NULL AND s.shipped_at IS NOT NULL THEN DATEDIFF(day, s.shipped_at, s.delivered_at)       ELSE NULL     END AS transit_days,    CASE       WHEN s.delivered_at IS NOT NULL AND s.promised_delivery_date IS NOT NULL THEN DATEDIFF(day, s.promised_delivery_date, DATE(s.delivered_at))       ELSE NULL     END AS delay_days,    CASE       WHEN s.delivered_at IS NULL THEN 'PENDING'       WHEN s.promised_delivery_date IS NULL THEN 'NO_PROMISE'       WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 'ON_TIME'       ELSE 'LATE'     END AS delivery_outcome  FROM shipments s  WHERE s.shipped_at IS NOT NULL) SELECT * FROM base_shipments;

Como usar: adapte o DATEDIFF ao seu banco (alguns usam DATE_DIFF ou DATEDIFF('day', ...)). O importante é padronizar a lógica em um lugar só.

Passo 2: medir nível de serviço (OTD) por período e recortes

OTD (On-Time Delivery) é a taxa de entregas no prazo. Um relatório básico por semana e transportadora:

WITH base AS (  SELECT     s.carrier_id,    DATE(s.delivered_at) AS delivered_date,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 1      ELSE 0    END AS is_on_time  FROM shipments s  WHERE s.delivered_at IS NOT NULL) SELECT   carrier_id,  DATE_TRUNC('week', delivered_date) AS week_start,  COUNT(*) AS delivered_shipments,  SUM(is_on_time) AS on_time_shipments,  1.0 * SUM(is_on_time) / COUNT(*) AS otd_rate FROM base WHERE is_on_time IS NOT NULL GROUP BY carrier_id, DATE_TRUNC('week', delivered_date) ORDER BY week_start, carrier_id;

Interpretação prática: se a taxa cai, você precisa saber se o problema é concentrado em uma região, em um CD, em um serviço (econômico vs expresso) ou em uma faixa de peso/distância.

Passo 3: quantificar atraso (não só “atrasou ou não”)

Além da taxa, a severidade do atraso importa. Você pode olhar média/mediana e percentis (se disponíveis) ou faixas de atraso.

WITH base AS (  SELECT     carrier_id,    origin_dc_id,    CASE      WHEN delivered_at IS NULL OR promised_delivery_date IS NULL THEN NULL      ELSE DATEDIFF(day, promised_delivery_date, DATE(delivered_at))    END AS delay_days  FROM shipments  WHERE delivered_at IS NOT NULL) SELECT   carrier_id,  origin_dc_id,  COUNT(*) AS delivered_shipments,  SUM(CASE WHEN delay_days > 0 THEN 1 ELSE 0 END) AS late_shipments,  AVG(CASE WHEN delay_days > 0 THEN delay_days * 1.0 ELSE NULL END) AS avg_delay_days_when_late,  SUM(CASE WHEN delay_days BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS late_1_2d,  SUM(CASE WHEN delay_days BETWEEN 3 AND 5 THEN 1 ELSE 0 END) AS late_3_5d,  SUM(CASE WHEN delay_days >= 6 THEN 1 ELSE 0 END) AS late_6d_plus FROM base WHERE delay_days IS NOT NULL GROUP BY carrier_id, origin_dc_id ORDER BY late_shipments DESC;

Uso operacional: uma transportadora pode ter OTD parecido com outra, mas atrasar “pouco” (1–2 dias) enquanto a outra concentra atrasos longos (6+). Isso muda a priorização de ações e renegociação.

Passo a passo prático: custos logísticos e custo da ineficiência

Passo 4: consolidar custos por remessa

Custos podem vir em múltiplas linhas (frete base, adicional, reentrega, seguro, devolução). Primeiro, consolide por shipment.

WITH cost_per_shipment AS (  SELECT     shipment_id,    SUM(CASE WHEN cost_type = 'FREIGHT' THEN amount ELSE 0 END) AS freight_cost,    SUM(CASE WHEN cost_type = 'SURCHARGE' THEN amount ELSE 0 END) AS surcharge_cost,    SUM(CASE WHEN cost_type = 'REDELIVERY' THEN amount ELSE 0 END) AS redelivery_cost,    SUM(CASE WHEN cost_type = 'INSURANCE' THEN amount ELSE 0 END) AS insurance_cost,    SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id) SELECT * FROM cost_per_shipment;

Boa prática: mantenha também o detalhamento por tipo para explicar variações (ex.: aumento de reentrega pode indicar falha de primeira tentativa).

Passo 5: custo por entrega e custo por unidade (kg, km)

Agora, junte custos com atributos operacionais (peso, distância, serviço). O foco é comparar “maçãs com maçãs”.

WITH cost AS (  SELECT shipment_id, SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id), base AS (  SELECT     s.shipment_id,    s.carrier_id,    s.service_level,    s.origin_dc_id,    s.distance_km,    s.weight_kg,    s.delivered_at,    s.promised_delivery_date,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 1      ELSE 0    END AS is_on_time  FROM shipments s) SELECT   b.carrier_id,  b.service_level,  COUNT(*) AS shipments,  AVG(c.total_cost * 1.0) AS avg_cost_per_shipment,  AVG(CASE WHEN b.weight_kg > 0 THEN c.total_cost * 1.0 / b.weight_kg ELSE NULL END) AS avg_cost_per_kg,  AVG(CASE WHEN b.distance_km > 0 THEN c.total_cost * 1.0 / b.distance_km ELSE NULL END) AS avg_cost_per_km,  1.0 * SUM(b.is_on_time) / NULLIF(COUNT(b.is_on_time), 0) AS otd_rate  FROM base b  JOIN cost c ON c.shipment_id = b.shipment_id GROUP BY b.carrier_id, b.service_level ORDER BY avg_cost_per_shipment DESC;

Leitura gerencial: você consegue enxergar trade-offs: um serviço mais caro pode ter OTD muito superior. A decisão pode ser segmentar por região/peso (ex.: expresso só para rotas críticas).

Passo 6: custo do atraso (estimativa operacional)

Nem sempre existe um “custo do atraso” explícito. Você pode estimar com regras de negócio, por exemplo:

• R$ 8 por dia de atraso por remessa (custo de atendimento, compensações, churn estimado).
• Ou custo fixo por atraso + variável por dia.

Exemplo com custo estimado:

WITH base AS (  SELECT     s.shipment_id,    s.carrier_id,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      ELSE DATEDIFF(day, s.promised_delivery_date, DATE(s.delivered_at))    END AS delay_days  FROM shipments s  WHERE s.delivered_at IS NOT NULL), cost AS (  SELECT shipment_id, SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id) SELECT   b.carrier_id,  COUNT(*) AS delivered_shipments,  SUM(CASE WHEN b.delay_days > 0 THEN 1 ELSE 0 END) AS late_shipments,  SUM(CASE WHEN b.delay_days > 0 THEN b.delay_days ELSE 0 END) AS total_late_days,  SUM(c.total_cost) AS total_freight_cost,  SUM(c.total_cost) + SUM(CASE WHEN b.delay_days > 0 THEN b.delay_days * 8 ELSE 0 END) AS total_cost_including_delay_estimate FROM base b JOIN cost c ON c.shipment_id = b.shipment_id WHERE b.delay_days IS NOT NULL GROUP BY b.carrier_id ORDER BY total_cost_including_delay_estimate DESC;

Por que isso ajuda: às vezes uma transportadora “barata” sai cara quando você coloca o impacto do atraso na conta. Mesmo sendo uma estimativa, ela orienta priorização e testes.

Passo a passo prático: eficiência operacional e gargalos por eventos

Passo 7: identificar principais motivos de atraso (eventos de rastreio)

Quando existe tabela de eventos, você pode mapear “motivos” (ex.: endereço incorreto, destinatário ausente, restrição de área, falha de roteirização). Uma abordagem prática é pegar o último evento antes da entrega (ou o evento de exceção mais recente) e agregá-lo.

WITH delivered AS (  SELECT shipment_id, delivered_at  FROM shipments  WHERE delivered_at IS NOT NULL), last_event AS (  SELECT     e.shipment_id,    e.event_type,    e.event_reason_code,    e.event_time,    ROW_NUMBER() OVER (PARTITION BY e.shipment_id ORDER BY e.event_time DESC) AS rn  FROM shipment_events e  JOIN delivered d ON d.shipment_id = e.shipment_id  WHERE e.event_time <= d.delivered_at), last_event_per_shipment AS (  SELECT shipment_id, event_type, event_reason_code  FROM last_event  WHERE rn = 1), base AS (  SELECT     s.shipment_id,    s.carrier_id,    s.origin_dc_id,    CASE      WHEN s.promised_delivery_date IS NULL THEN NULL      ELSE DATEDIFF(day, s.promised_delivery_date, DATE(s.delivered_at))    END AS delay_days  FROM shipments s  WHERE s.delivered_at IS NOT NULL) SELECT   b.carrier_id,  b.origin_dc_id,  le.event_type,  le.event_reason_code,  COUNT(*) AS shipments,  SUM(CASE WHEN b.delay_days > 0 THEN 1 ELSE 0 END) AS late_shipments FROM base b LEFT JOIN last_event_per_shipment le ON le.shipment_id = b.shipment_id WHERE b.delay_days IS NOT NULL GROUP BY b.carrier_id, b.origin_dc_id, le.event_type, le.event_reason_code ORDER BY late_shipments DESC;

Como transformar em ação: se “DESTINATARIO_AUSENTE” concentra atrasos, talvez o problema seja janela de entrega/roteiro; se “ENDERECO_INCORRETO” cresce, pode ser validação de cadastro; se “ATRASO_TRANSFERENCIA” aparece, pode ser gargalo em hub.

Passo 8: primeira tentativa de entrega e reentrega

Um indicador operacional importante é a taxa de sucesso na primeira tentativa. Se você tiver eventos de “OUT_FOR_DELIVERY” e “DELIVERY_ATTEMPT_FAILED”, dá para medir quantas tentativas ocorreram.

WITH attempts AS (  SELECT     shipment_id,    SUM(CASE WHEN event_type = 'DELIVERY_ATTEMPT' THEN 1 ELSE 0 END) AS attempt_count,    SUM(CASE WHEN event_type = 'DELIVERY_ATTEMPT_FAILED' THEN 1 ELSE 0 END) AS failed_attempts  FROM shipment_events  GROUP BY shipment_id), base AS (  SELECT     s.shipment_id,    s.carrier_id,    s.delivered_at,    s.promised_delivery_date,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 1      ELSE 0    END AS is_on_time  FROM shipments s  WHERE s.delivered_at IS NOT NULL) SELECT   b.carrier_id,  COUNT(*) AS delivered_shipments,  AVG(a.attempt_count * 1.0) AS avg_attempts,  SUM(CASE WHEN a.failed_attempts > 0 THEN 1 ELSE 0 END) AS shipments_with_failed_attempt,  1.0 * SUM(CASE WHEN a.failed_attempts > 0 THEN 1 ELSE 0 END) / COUNT(*) AS failed_attempt_rate,  1.0 * SUM(b.is_on_time) / COUNT(*) AS otd_rate FROM base b LEFT JOIN attempts a ON a.shipment_id = b.shipment_id GROUP BY b.carrier_id ORDER BY failed_attempt_rate DESC;

Leitura prática: alta taxa de tentativa falha costuma elevar custos (reentrega) e piorar prazo. Isso conecta eficiência operacional diretamente a custo.

Passo a passo prático: segmentação operacional para decisões

Passo 9: criar faixas (peso, distância) para comparações justas

Comparar custo e prazo sem segmentar pode punir quem atende rotas longas/pesadas. Uma técnica é criar buckets e analisar dentro deles.

WITH base AS (  SELECT     s.shipment_id,    s.carrier_id,    s.distance_km,    s.weight_kg,    s.delivered_at,    s.promised_delivery_date,    CASE      WHEN s.distance_km < 50 THEN '0-49km'      WHEN s.distance_km < 200 THEN '50-199km'      WHEN s.distance_km < 500 THEN '200-499km'      ELSE '500km+'    END AS distance_bucket,    CASE      WHEN s.weight_kg < 1 THEN '0-0.99kg'      WHEN s.weight_kg < 5 THEN '1-4.99kg'      WHEN s.weight_kg < 15 THEN '5-14.99kg'      ELSE '15kg+'    END AS weight_bucket,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 1      ELSE 0    END AS is_on_time  FROM shipments s  WHERE s.delivered_at IS NOT NULL), cost AS (  SELECT shipment_id, SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id) SELECT   b.carrier_id,  b.distance_bucket,  b.weight_bucket,  COUNT(*) AS shipments,  AVG(c.total_cost * 1.0) AS avg_cost,  1.0 * SUM(b.is_on_time) / NULLIF(COUNT(b.is_on_time), 0) AS otd_rate FROM base b JOIN cost c ON c.shipment_id = b.shipment_id WHERE b.is_on_time IS NOT NULL GROUP BY b.carrier_id, b.distance_bucket, b.weight_bucket ORDER BY b.distance_bucket, b.weight_bucket, avg_cost DESC;

Decisão típica: escolher a melhor transportadora por faixa de distância/peso, em vez de uma única vencedora global.

Passo 10: ranking de rotas/CDs com “score” simples de eficiência

Para priorização, você pode criar um score simples combinando custo e desempenho. Exemplo: penalizar atraso e custo alto. O score abaixo é ilustrativo (ajuste pesos conforme sua realidade).

WITH cost AS (  SELECT shipment_id, SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id), base AS (  SELECT     s.shipment_id,    s.origin_dc_id,    s.carrier_id,    s.distance_km,    s.weight_kg,    CASE      WHEN s.delivered_at IS NULL OR s.promised_delivery_date IS NULL THEN NULL      ELSE DATEDIFF(day, s.promised_delivery_date, DATE(s.delivered_at))    END AS delay_days  FROM shipments s  WHERE s.delivered_at IS NOT NULL) SELECT   b.origin_dc_id,  b.carrier_id,  COUNT(*) AS shipments,  AVG(c.total_cost * 1.0) AS avg_cost,  AVG(CASE WHEN b.delay_days IS NOT NULL THEN b.delay_days * 1.0 ELSE NULL END) AS avg_delay_days,  AVG( (c.total_cost * 1.0) + (CASE WHEN b.delay_days > 0 THEN b.delay_days * 10 ELSE 0 END) ) AS efficiency_score FROM base b JOIN cost c ON c.shipment_id = b.shipment_id WHERE b.delay_days IS NOT NULL GROUP BY b.origin_dc_id, b.carrier_id ORDER BY efficiency_score DESC;

Como usar no dia a dia: filtre para combinações com volume relevante (ex.: shipments >= 200) e investigue as piores. O score não substitui análise, mas acelera triagem.

Checklist de perguntas de negócio que suas queries devem responder

• Prazos: qual a taxa de entregas no prazo por semana, por transportadora, por CD e por serviço?
• Atrasos: onde estão os atrasos mais severos (3–5 dias, 6+ dias) e como isso evolui?
• Custo: qual o custo médio por remessa e por kg/km por transportadora/serviço, segmentado por distância/peso?
• Custo da ineficiência: quanto do custo total está associado a reentregas, adicionais e estimativa de atraso?
• Gargalos: quais motivos/eventos aparecem com mais frequência em remessas atrasadas?
• Eficiência comparável: dentro de uma mesma faixa de distância/peso/região, quem entrega melhor com menor custo?

Exercício guiado: montar um relatório operacional semanal (estrutura)

Uma forma prática de operacionalizar é gerar uma tabela final com as principais métricas por semana, CD e transportadora. Abaixo está um esqueleto que combina entregas, atraso e custo. Adapte campos e granularidade.

WITH cost AS (  SELECT shipment_id, SUM(amount) AS total_cost  FROM shipment_costs  GROUP BY shipment_id), base AS (  SELECT     s.shipment_id,    s.origin_dc_id,    s.carrier_id,    DATE_TRUNC('week', DATE(s.delivered_at)) AS week_start,    CASE      WHEN s.promised_delivery_date IS NULL THEN NULL      WHEN DATE(s.delivered_at) <= s.promised_delivery_date THEN 1      ELSE 0    END AS is_on_time,    CASE      WHEN s.promised_delivery_date IS NULL THEN NULL      ELSE DATEDIFF(day, s.promised_delivery_date, DATE(s.delivered_at))    END AS delay_days  FROM shipments s  WHERE s.delivered_at IS NOT NULL) SELECT   b.week_start,  b.origin_dc_id,  b.carrier_id,  COUNT(*) AS delivered_shipments,  1.0 * SUM(b.is_on_time) / NULLIF(COUNT(b.is_on_time), 0) AS otd_rate,  SUM(CASE WHEN b.delay_days > 0 THEN 1 ELSE 0 END) AS late_shipments,  AVG(CASE WHEN b.delay_days > 0 THEN b.delay_days * 1.0 ELSE NULL END) AS avg_delay_when_late,  SUM(c.total_cost) AS total_cost,  AVG(c.total_cost * 1.0) AS avg_cost_per_shipment FROM base b JOIN cost c ON c.shipment_id = b.shipment_id WHERE b.is_on_time IS NOT NULL AND b.delay_days IS NOT NULL GROUP BY b.week_start, b.origin_dc_id, b.carrier_id ORDER BY b.week_start DESC, total_cost DESC;

Como transformar em rotina: este resultado pode ser materializado em uma tabela de métricas semanais, ou usado diretamente por um dashboard. O valor está em manter a mesma definição de OTD, atraso e custo ao longo do tempo, para comparações consistentes.