Shipment delivery delay analysis

Ship the SQL every carrier QBR demands

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%.

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.

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

OTIF Compound Metric

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

Freight Invoice Audits

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

Detention Analysis

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

Multi-Metric Carrier Scorecard

Carrier scorecards, OTIF, and freight cost.

First look at the shipment roster

Meet the carrier network

Lane inventory

Delivered shipment count

Status breakdown

Carrier shipment volume

Total freight spend

On-time delivery rate by carrier

In-full rate by carrier

Average freight cost per mode

Monthly shipment volume trend

Detention hot spots — lanes with high dwell

Top 5 most expensive shipments

Overbilled invoices

Carrier spend with invoice data

Failed shipments with carrier detail

Duplicate tracking events

Carrier OTD vs. target gap

Monthly freight cost trend by carrier mode

Lane-level OTD with carrier names

Perfect score carriers — OTD and in-full combined

Weight-normalized freight cost per mile

Invoice audit: billed vs shipped amounts

Carrier scorecard — four metrics in one query

Tracking event timeline for a shipment

Regional origin performance — cost and OTD

Shipments with accessorial charges — detention deep dive

Rolling 3-month freight cost vs prior period

End-to-end carrier risk index

carrier_id

carrier_name

scac

mode

on_time_target_pct

hq_state

dim_carriers

lane_id

origin_region

dest_region

distance_miles

transit_days_target

dim_lanes

shipment_id

planned_pickup_date

actual_pickup_date

planned_delivery_date

actual_delivery_date

qty_ordered

qty_shipped

weight_lbs

freight_cost

fuel_surcharge

accessorial_cost

dwell_hours_at_origin

status

fact_shipments

invoice_id

invoice_date

linehaul_amt

fuel_surcharge_amt

accessorial_amt

total_billed

contracted_linehaul

fact_freight_invoices

event_id

event_ts

event_type

facility_id

source

fact_tracking_events

Shipment delivery delay analysis

The Brief

You'll practice