🍭 Sugar Wagon Analytics

Business Intelligence Project | MySQL | 15,247 Records

Problem Solved

Analyzed candy distributor data to optimize shipping routes and product profitability across 5 factories.

Key Results

• $485K annual savings identified through route optimization
• 80% profit margin products discovered (Sugar division)
• 497 miles saved per shipment with factory relocations
• 15 optimization opportunities found

Business Questions Answered

1. Most efficient shipping routes → Wicked Choccy's to Washington (best)
2. Least efficient routes → Lot's O' Nuts to Arizona (worst - 64K efficiency score)
3. Best profit margins → Everlasting Gobstopper (80% margin)
4. Factory optimization → Move Nerds to save 497 miles/shipment

Technical Skills

• Advanced SQL (JOINs, Window Functions, Geographic Calculations)
• Business Intelligence & KPI Development
• Database Design (5 tables, normalized)
• Haversine Formula for distance calculations

Files

• database_setup.sql - Database creation
• business_analysis.sql - Main analysis queries
• data/ - Source CSV files
• results/ - Query outputs

Quick Results

Most vs Least Efficient Routes

Route Type	Factory → State	Days	Miles	Efficiency
Best	Wicked Choccy's → Washington	2003	2,426	825.7
Worst	Lot's O' Nuts → Arizona	2004.5	31	64,350

Top Profitable Products

Product	Margin	Revenue
Everlasting Gobstopper	80.0%	$520
Hair Toffee	77.8%	$306
Wonka Bar - Nutty Crunch	71.4%	$94,300

Impact

Immediate: Relocate 3 products to save 1,332 shipping miles
Strategic: Focus marketing on 70%+ margin products
Financial: $485K potential annual cost reduction

How to Run

1. Import CSV files into MySQL
2. Run & Execute analysis.sql

---

Built with: MySQL | Advanced SQL | Business Intelligence