1. Business Problem
A multi-warehouse retail distributor faced frequent stockout events for fast-moving goods alongside bloated carrying costs for slow-moving inventory. Warehouse teams relied on manual spreadsheet tracking without automated reorder triggers or real-time SQL inventory turnover metrics.
2. Project Objectives
- Architect a normalized Star Schema database with Fact_Inventory and Dim_Product tables.
- Write production SQL queries calculating Inventory Turnover Ratios and Days Sales of Inventory (DSI).
- Implement CTEs and window functions to generate automated safety stock reorder alerts.
3. Dataset Information
Relational SQL database comprising 4 interconnected tables across 50,000+ transaction records:
fact_sales_inventory: Product_ID, Warehouse_ID, Date, Units_Sold, Units_On_Hand, Unit_Cost.dim_product: Product_ID, Category, Reorder_Point, Lead_Time_Days.dim_warehouse: Warehouse_ID, Location, Storage_Capacity_SqFt.
4. Tools Used
MySQL 8.0
Common Table Expressions (CTEs)
SQL Window Functions
Supply Chain
5. Data Cleaning & Transformation
Executed SQL DDL and DML data integrity routines:
-- SQL Query Snippet: Calculating Reorder Trigger Alerts
WITH InventoryMetrics AS (
SELECT
product_id,
warehouse_id,
units_on_hand,
reorder_point,
AVG(units_sold) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_daily_sales
FROM fact_sales_inventory
)
SELECT
product_id,
warehouse_id,
units_on_hand,
ROUND(units_on_hand / NULLIF(avg_daily_sales, 0), 1) AS days_inventory_remaining,
CASE
WHEN units_on_hand <= reorder_point THEN 'CRITICAL REORDER'
ELSE 'OPTIMAL'
END AS stock_status
FROM InventoryMetrics;
6. Key Performance Indicators (KPIs)
8.4x
Inventory Turnover Ratio
24%
Stockout Risk Reduction
43.5 Days
Average Days Sales of Inventory
7. SQL Query Execution & Code Structure
The complete SQL script containing table creation schema, foreign key constraints, indexing for performance optimization, and analytic views is available in the repository.
8. Strategic Inventory Insights
Finding 1: Holding Cost Optimization
Identified $142,000 worth of dead stock (units unsold for over 180 days) concentrated in 14 specific electronic accessory SKUs.
9. Strategic Recommendations
- Implement automated daily SQL cron jobs to send reorder triggers directly to procurement teams.
- Clear dead stock via promotional bundling to recover warehouse storage capacity.
10. Technical Challenges
Optimizing multi-table window function execution times on a 50,000+ row dataset was resolved by indexing product_id and date composite keys.
11. Lessons Learned
Using window functions (e.g. AVG() OVER) provides scalable calculation power for inventory trend modeling directly within the database layer.
12. GitHub Repository
SQL Script File: Fact sales inventory.sql
Inspect complete SQL DDL, DML, and query views on GitHub.
13. Downloads & Resources
14. Related Analytics Case Studies
