Top 10 invoices by amount

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

Open POs count

Active contract count

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
invoice_id	INT	PK
po_id	INT	FK → fact_purchase_orders
supplier_id	INT	FK → dim_suppliers
invoice_date	TEXT
invoice_amount	REAL
po_amount	REAL
payment_date	TEXT

Top 10 invoices by amount

The Brief

You'll practice

Tables & columns available

Hints (3)