Tool: MySQL Workbench

Objective: Analyze historical pizza-order data to identify customer behavior patterns, best-selling pizzas, revenue trends, and overall product performance.

Process:

• Extracted and analyzed supply chain data using advanced SQL queries
• Performed data cleaning and transformation to ensure data quality
• Created complex joins to analyze relationships between suppliers, manufacturing, logistics, and sales
• Developed calculated metrics for lead times, defect rates, cost analysis, and performance KPIs
• Structured multi-dimensional analysis across transportation, quality control, inventory, and customer segments
• Implemented data validation and error debugging throughout the analysis process

Result:

• Transportation Efficiency: Air freight delivered shortest lead times; road transport caused significant delays
• Supplier Performance: Supplier 1 achieved lowest defect rates (top performer), while Supplier 5 had highest defect rates (needs improvement)
• Cost vs. Speed Trade-offs: Lower-cost shipping options consistently resulted in longer delivery delays
• Inventory Challenges: Haircare and Skincare products experienced frequent stockouts due to extended restocking cycles
• Carrier Analysis:
  • Carrier A: Most expensive but fastest delivery
  • Carrier B: Better on-time delivery rates at lower cost
• Customer Demographics:
  • Female customers: Preferred Skincare products
  • Male customers: Favored Haircare products
  • Non-binary customers: Balanced preferences across categories
• Regional Performance: Kolkata showed highest profit margins; Bangalore had high sales volume but lower profitability
• Quality vs. Revenue: Skincare generated highest revenue but also had elevated defect rates, creating potential return risks

Recommendations:

1. Optimize Supplier Strategy → Strengthen partnership with Supplier 1; implement stricter QC measures for Supplier 5
2. Transportation Mode Selection → Use air freight for urgent shipments; road transport for non-critical deliveries
3. Inventory Management → Maintain buffer stock for Haircare/Skincare; diversify supplier base to prevent stockouts
4. Carrier Optimization → Deploy Carrier A for time-sensitive orders; use Carrier B for bulk shipments
5. Regional Pricing Strategy → Adjust pricing in Bangalore to improve profit margins while maintaining sales volume
6. Quality Improvement → Focus on Skincare product quality enhancement to reduce defect-related returns
7. Demographic Targeting → Customize marketing campaigns based on gender preferences for each product category

Key Skills Developed:

• Advanced SQL querying and data analysis
• Supply chain optimization techniques
• Multi-dimensional business problem-solving
• Data-driven decision making and insights generation

Download SQL File:

Supply Chain Tefem Weekly Challenge Week 4 (SQL).sql