Supplier Performance Scorecard: OTIF Rate Analysis

Business Context

PrimePath Distribution Co. is a regional wholesale distributor supplying grocery and household goods to over 340 retail store locations across four states. The company sources inventory from 25 active suppliers and processes approximately 4,800 purchase orders per year. In a recent logistics review, the operations director raised a red flag: despite on-time delivery metrics looking acceptable at a surface level, several high-volume suppliers were consistently shipping incorrect quantities — either short-shipping or over-delivering against the agreed PO quantities. The combined effect of late deliveries and quantity mismatches had caused 17 stockout events in the past quarter alone, each triggering emergency spot-buy orders at a premium. The company needs a standardized OTIF (On Time In Full) scorecard to objectively evaluate every supplier's delivery performance over the last 12 months.

The SQL Challenge

OTIF is a supply chain KPI that marks a delivery as compliant only when it satisfies both conditions simultaneously: it arrives on or before the agreed delivery date (On Time), and the received quantity is within an acceptable tolerance of the ordered quantity (In Full — typically ±5%). A delivery that is on time but short-shipped is not OTIF-compliant. The query will require a JOIN between the purchase orders table and the suppliers master table, SQL aggregations (COUNT, SUM) to tally total deliveries per supplier, and conditional aggregation using CASE WHEN inside aggregate functions to count only the deliveries that satisfy both OTIF conditions. The final OTIF rate is expressed as a percentage of compliant deliveries over total deliveries.

Your Role and Deliverable

You are the data analyst at PrimePath Distribution Co. The operations director needs a supplier-level OTIF scorecard covering all closed purchase orders from the last 12 months. The final output should show each supplier's name, category, total PO count, number of OTIF-compliant deliveries, the OTIF percentage, and a performance tier label (Excellent, Acceptable, At Risk, Critical) — sorted by OTIF percentage ascending so the worst performers appear first and require immediate attention.