SQL for Supply Chain: Supplier OTIF, Fill Rate and Days of Cover

<p>Pavana Distributors, an FMCG distributor in Pune that supplies 900 kirana stores, is heading into its quarterly supplier reviews weeks before Diwali. Sanjay Patil, the purchase manager, wants numbers instead of impressions: which suppliers deliver what was ordered, which deliver it on time and in full, how often the shelves ran empty, which products will run out before a new order could arrive, and which stock is sitting still. Meera Kulkarni, the supply-chain analyst, answers from the company's own purchasing and stock database.</p><p>Across five lessons you build the answers with her, in an SQL lab in your browser: quarter lines picked by promised date with cancelled orders left out, receipts totalled per line so split deliveries don't multiply the order, LEFT JOIN so undelivered lines still count, accepted rather than received units, a unit fill rate capped at what was ordered, late deliveries and OTIF judged by date when the receipt has a time, stock-outs from a daily snapshot table, days of cover from a sales table with no rows for days without a sale, reorder alerts against supplier lead times, and a slow-mover list that keeps the product that never sold. Every lesson you write the query yourself first, then hand the same job to your AI (ChatGPT, Claude, Copilot or Gemini) and learn exactly where it goes wrong. In the capstone your AI writes the supplier review pack from one brief, and you prove every number before anyone takes it into a supplier meeting.</p>

Tools: SQL (MySQL), JOINs and join cardinality, CTEs and LEFT JOIN with COALESCE, Conditional aggregation, Dates, timestamps and DATEDIFF, Supply chain KPIs: fill rate, OTIF, days of cover, AI assistants for SQL