Supplier health capstone (multi-CTE scorecard)

Ship the SQL ops analysts run every morning

An inbound supply-chain star schema: 25 SKUs across 5 categories, 8 active suppliers (one ghost), 4 warehouses, 79 POs spanning Nov 2025 – Apr 2026 (mix of received / in_transit / cancelled), 85 weekly inventory snapshots with planted duplicates, and 40 demand rows for forecast-accuracy missions. Planted data-quality issues: trailing whitespace on supplier name, casing drift on category, duplicate sku_code, ghost supplier row, duplicate snapshot rows.

Inbound purchase orders, supplier on-time delivery, inventory health, days-of-supply alerts, demand-forecast accuracy, SCD2 supplier-history reconstruction, and capstone multi-CTE scorecards — the full 30-mission operations & supply-chain analyst SQL curriculum.

On-time delivery, lead time, and chronic-late-shipment patterns across 8 suppliers and a ghost row.

Real Supplier Scorecards

Latest-snapshot dedup, days-of-supply alerts, stock-out risk — the queries warehouse leads run every morning.

Inventory Health Queries

MAPE forecast accuracy, the S&OP-review metric every demand planner ships weekly.

Demand vs Plan

Trailing whitespace on supplier names, casing drift on SKU categories, duplicate snapshot rows, ghost suppliers — the data-quality bugs you actually fight.

Planted Dirty Data

Inventory, lead times, supplier scorecards.

How many suppliers do we work with?

Show me the warehouses

How many SKUs do we stock?

List our SKU categories

Show the 5 most recent purchase orders

Total units ordered per category last quarter

On-time delivery rate per supplier

Top 5 SKUs by purchase value

Stock-out risk: SKUs with under 7 days of supply

Late shipments this quarter

Average lead time per supplier

Latest inventory on hand per warehouse

Forecast accuracy by SKU last week

Lead-time consistency per supplier

Overstocked SKUs (over 15 days of supply)

Top 3 SKUs per category by purchase value

Supplier scorecard (lead time + on-time + cancellation)

Demand week-over-week change

Stockout root cause: SKUs with no incoming PO

Inventory turnover ratio (last week)

Point-in-time supplier on-time target (SCD2 reconstruction)

Lead-time trend per supplier (monthly average)

ABC analysis: classify SKUs by spend share

Forecast accuracy decomposition (bias + precision per SKU)

Reorder-point breach: SKUs below their ROP

Multi-warehouse rebalancing plan

Demand stability per SKU (coefficient of variation)

Supplier concentration risk per category (Herfindahl index)

Operations KPI capstone (board scoreboard)

sku_id

sku_code

sku_name

unit_cost

reorder_point

dim_skus

supplier_id

supplier_name

country

lead_time_target_days

on_time_target_pct

is_active

dim_suppliers

warehouse_id

warehouse_name

region

dim_warehouses

po_id

po_number

order_date

expected_arrival

actual_arrival

qty_ordered

qty_received

status

fact_purchase_orders

snapshot_id

snapshot_date

on_hand_qty

days_of_supply

fact_inventory_snapshots

demand_id

week_ending

units_sold

forecast_units

fact_demand

change_id

changed_field

old_value

new_value

changed_at

fact_supplier_changes

Supplier health capstone (multi-CTE scorecard)

The Brief

You'll practice