Logistics & Transportation Analytics

Carrier scorecards, OTIF, and freight cost.
The SQL logistics analysts run before every carrier QBR.

Carrier OTIF, freight invoice audits, detention hot spots, cost-per-unit benchmarking, and multi-metric carrier scorecards — the full logistics & transportation analyst SQL curriculum across 30 missions.

See All Missions

30 missions · 13 free + 17 Pro · Starter → Master

How It Works

1

Read the briefing

A Slack message from your manager

2

Explore the schema

5 tables in a star schema

3

Write your query

Full SQL editor with autocomplete

4

Get expert feedback

Graduated hints, not just pass/fail

Why This Path

OTIF Compound Metric

Build the on-time AND in-full formula at PO-line grain — the exact logic Walmart enforces with a 3% COGS chargeback.

Freight Invoice Audits

Find overbilled invoices, accessorial overcharges, and contract rate violations across carriers and lanes.

Detention Analysis

Identify facilities where dwell time exceeds 2 hours on more than a third of shipments — the hidden cost every logistics manager wants to cut.

Multi-Metric Carrier Scorecard

Combine OTD, fill rate, cost per unit, and dwell into a ranked scorecard using multi-CTE queries.

The Missions

Each mission is a real request from someone at the company. Difficulty increases as you go.

Starter5 missions
1
First look at the shipment rosterFrom: Logistics Analyst
Meet the carrier networkFrom: Logistics Analyst
Lane inventoryFrom: Network Planning Manager
Delivered shipment countFrom: Logistics Analyst
Status breakdownFrom: Logistics Analyst
Easy4 missions
Carrier shipment volumeFrom: Network Planning Manager
Total freight spendFrom: Freight Finance Lead
Average freight cost per modeFrom: Freight Finance Lead
Tracking event timeline for a shipmentProFrom: Network Planning Manager
Medium11 missions
On-time delivery rate by carrierFrom: Logistics Analyst
In-full rate by carrierFrom: Freight Finance Lead
Monthly shipment volume trendFrom: Logistics Analyst
Detention hot spots — lanes with high dwellFrom: Network Planning Manager
Top 5 most expensive shipmentsFrom: Freight Finance Lead
Overbilled invoicesProFrom: Freight Finance Lead
Carrier spend with invoice dataProFrom: Freight Finance Lead
Failed shipments with carrier detailProFrom: Logistics Analyst
Duplicate tracking eventsProFrom: Network Planning Manager
Regional origin performance — cost and OTDProFrom: Network Planning Manager
Shipments with accessorial charges — detention deep diveProFrom: Freight Finance Lead
Hard4 missions
Carrier OTD vs. target gapProFrom: Logistics Analyst
Monthly freight cost trend by carrier modeProFrom: Freight Finance Lead
Lane-level OTD with carrier namesProFrom: Network Planning Manager
Perfect score carriers — OTD and in-full combinedProFrom: Logistics Analyst
Expert3 missions
Weight-normalized freight cost per mileProFrom: Freight Finance Lead
Invoice audit: billed vs shipped amountsProFrom: Freight Finance Lead
Carrier scorecard — four metrics in one queryProFrom: Logistics Analyst
Master3 missions
Shipment delivery delay analysisProFrom: Logistics Analyst
Rolling 3-month freight cost vs prior periodProFrom: Freight Finance Lead
End-to-end carrier risk indexProFrom: Logistics Analyst

The Database

A logistics & transportation star schema: 8 carriers (one "late-but-complete" trap carrier), 16 lanes across 4 regions, 120 shipments with planted OTIF failures, 110 freight invoices with 4 overbilled loads, and 420 tracking events with duplicate DELIVERED scans. Planted data-quality issues: carrier MISS has 97% in-full but only 71% on-time; two facilities have dwell > 2 hrs on >35% of loads; 4 invoices exceed contracted linehaul by >10%.

dim_carriers (8)dim_lanes (16)fact_shipments (120)fact_freight_invoices (110)fact_tracking_events (420)

●dimension tables  ● fact tables

Ship the SQL every carrier QBR demands

OTIF, chargeback exposure, detention hot spots — the queries that decide which carriers keep the contract.

Looking for something different?

|