Open POs count

Ship the SQL every supplier QBR demands

A procurement star schema: 12 suppliers (3 name-variant aliases for the same entity), 8 spend categories, 14 contracts, 95 POs with 22% off-contract (null contract_id), 88 receipts, 82 invoices. Planted data-quality issues: 3 after-the-fact POs where invoice precedes PO date; 5 PPV exceedances where unit_price > contracted_price; preferred supplier with worst lead-time performance.

PO cycle time analysis, purchase price variance, maverick spend detection, three-way match exceptions, and supplier risk scorecards — the full procurement & sourcing analyst SQL curriculum across 30 missions.

Trace the full req → PO → ASN → receipt → invoice cycle and measure cycle time at every stage.

PO Lifecycle Analysis

Compare actual vs contracted unit price to surface PPV exceedances by category — the metric that drives finance escalations.

Purchase Price Variance

Find POs with no contract_id — off-contract spend that bypasses negotiated pricing.

Maverick Spend Detection

Join PO + receipt + invoice to find quantity and price mismatches that stall AP automation.

Three-Way Match Exceptions

PO cycle time, PPV, and supplier risk.

First look at suppliers

Category catalog

Active contract count

Top 10 invoices by amount

Spend by category

Supplier PO volume

On-time delivery per supplier

Average lead time

ATF detection

Top 10 suppliers by spend

PO cycle time

Preferred vs non-preferred share

Invoice payment timing

Purchase price variance

3-way match

Lead-time CV by supplier

Contract compliance rate

Spend under management %

Single-source category risk

Supplier scorecard

PPV walk by month

Maverick spend share

Apex supplier dedup

Apex consolidated spend

Category x spend-type cube

Category HHI concentration

Dual-source coverage

Supplier risk score

Procurement capstone

supplier_id

supplier_name

country

tier

category_focus

diversity_certified

payment_terms_days

is_preferred

dim_suppliers

category_id

category_name

category_manager

spend_type

dim_categories

contract_id

start_date

end_date

contracted_unit_price

discount_threshold_qty

dim_contracts

po_id

req_date

po_date

ack_date

asn_date

gr_date

invoice_date

qty_ordered

qty_received

unit_price

contracted_price

status

fact_purchase_orders

receipt_id

receipt_date

qty_rejected

lot_number

fact_receipts

invoice_id

invoice_amount

po_amount

payment_date

fact_invoices

Column	Type	Key
po_id	INT	PK
supplier_id	INT	FK → dim_suppliers
category_id	INT	FK → dim_categories
contract_id	INT	FK → dim_contracts
req_date	TEXT
po_date	TEXT
ack_date	TEXT
asn_date	TEXT
gr_date	TEXT
invoice_date	TEXT
qty_ordered	INT
qty_received	INT
unit_price	REAL
contracted_price	REAL
status	TEXT

Open POs count

The Brief

You'll practice

Tables & columns available

Hints (3)