Supply Chain & Inventory Optimization Analysis (MySQL)

A portfolio project analyzing inventory stockout risk, supplier OTIF (On-Time In-Full) reliability, and dead stock tying up working capital — built on a normalized MySQL schema, a Python-generated relational dataset with deliberately engineered supply chain quirks, and three business-driven SQL queries using CTEs and window functions.

Tech Stack

• MySQL 8.0+
• Common Table Expressions (CTEs), SUM() OVER () window function
• Conditional aggregation (SUM(CASE WHEN ...))
• Python (faker, pandas) for relational mock data generation

Repository Structure

File	Purpose
01_schema.sql	DDL — creates suppliers, products, inventory_levels (product × warehouse grain), purchase_orders, and sales_orders, with PK/FK constraints, CHECK constraints, and indexes
02_seed_data.sql	DML — 20 suppliers, 150 products, 250 inventory records, 2,424 purchase orders, and 2,774 sales orders (5,448 total transactional records), generated via Python Faker
03_analysis_queries.sql	Query A (stockout risk alert), Query B (supplier OTIF), Query C (inventory turnover & dead stock matrix)
data_generator.py	Python data generator (Faker + pandas) — produces the full relational dataset with supplier-tier-driven OTIF variance and deliberately engineered dead stock

data_generator.py also drops a CSV copy of the same dataset into csv_exports/ (suppliers.csv, products.csv, inventory_levels.csv, purchase_orders.csv, sales_orders.csv), for MySQL Workbench's Table Data Import Wizard.

How to Run

mysql -u your_username -p < 01_schema.sql
mysql -u your_username -p < 02_seed_data.sql
mysql -u your_username -p < 03_analysis_queries.sql

To regenerate 02_seed_data.sql with a fresh dataset:

pip install -r requirements.txt
python data_generator.py

The script prints three realism self-checks after generating: OTIF score by supplier reliability tier, a dead-stock verification (confirming zero trailing-90-day sales for tagged dead-stock products), and a stockout risk count — so you can confirm the engineered quirks held before loading the data.

Data Design Notes

Suppliers only fulfill orders in their own category. Each of the 8 product categories (Electronics Components, Raw Materials, Machinery Parts, etc.) has 2-3 dedicated suppliers, and every purchase order draws from that matching pool — verified at the database level with zero category mismatches across all 2,424 purchase orders.

OTIF variance is real, not decorative. Every supplier is assigned a hidden reliability tier (Excellent / Good / Average / Poor) that independently drives its probability of being late, delivering short, or having an order cancelled outright — lead time and reliability are kept as separate dimensions, since a supplier can be slow-but-dependable or fast-but-flaky in real life. This is what gives Query B a genuine spread to rank, rather than every supplier clustering near 95%.

Purchase orders that aren't due yet are correctly left Open, with no actual_delivery_date — these are excluded from OTIF math entirely rather than being guessed at, since their outcome genuinely isn't known yet.

Dead stock is engineered, not incidental. A subset of products is assigned a "Dead" demand tier: they get sparse historical sales only in the 91-540 day window and are mathematically guaranteed zero sales in the trailing 90 days — while still carrying elevated on-hand inventory, since nobody stops holding stock the moment it stops moving. Non-dead products can also occasionally show zero recent sales by chance (this is realistic — Query C's dead-stock definition should catch genuine dead stock wherever it appears, not just the products deliberately designed to be dead).

A meaningful chunk of products are deliberately understocked. About 28% of active (non-dead) products are pushed below their reorder_level + safety_stock threshold during generation, so Query A's alert has real, varied-severity hits to rank rather than an empty result set.

Key Business Insights (from the generated dataset)

Scale: 150 products across 8 categories, sourced from 20 suppliers, with 2,424 purchase orders and 2,774 sales transactions spanning the last ~15 months.

Stockout risk (Query A): 40 of 150 products (27%) are currently below their reorder threshold, led by a Heavy-Duty Microcontroller Unit short by 134 units and an Industrial-Grade Capacitor Pack short by 110 — both flagged "Critical" or "High" priority, giving procurement a clear, severity-ranked buy queue rather than a flat list.

Supplier OTIF (Query B): Performance spans an enormous range — from James Group (Textiles) at just 25.3% OTIF with a ~5-day average delay, up to Galloway-Wyatt (Industrial Chemicals) at 94.8% OTIF with deliveries arriving nearly a day early on average. Three of the four lowest-OTIF suppliers cluster below 35%, exactly the kind of finding a sourcing team would use to open a supplier renegotiation or diversify away from a single point of failure.

Dead stock (Query C): 32 products (21% of the catalog) are currently classified as Dead Stock — zero sales in 90 days despite carrying positive inventory — with another 72 (48%) flagged Slow-Moving. The single largest dead-capital offender, a Replacement Gear Assembly, alone accounts for just over 1% of the company's entire inventory holding cost with zero recent sales to justify it. Combined, Dead Stock items represent real, quantified working capital that could be liquidated or discontinued rather than a vague "some of this probably isn't selling" hunch.

Why This Project Matters

Query C in particular demonstrates a pattern used in real inventory analytics work: it doesn't stop at "this product hasn't sold" — it uses a grand-total SUM() OVER () window function to express each dead item's holding cost as a percentage of the company's entire inventory investment, turning a binary flag into a dollar-ranked, board-ready liquidation priority list.