Project Name: Analyze Data in a Model Car Database with MySQL Workbench
Project Scenario: Mint Classics Company is considering closing one of their storage facilities. They seek suggestions and recommendations for reorganizing or reducing inventory while maintaining timely service to customers. As a data analyst, the task is to analyze the data from the Model Car Database using MySQL Workbench and provide data-driven insights to address the business problem.
Skills Demonstrated:
- Data analysis
- SQL querying
- Critical thinking
- Problem-solving
- Communication
Tools Used:
- MySQL Workbench
- SQL
Summary: The project involves examining the Model Car Database to identify patterns, conduct what-if analysis, and formulate recommendations. Key tasks include analyzing sales patterns, assessing the impact of inventory reduction, and proposing solutions to optimize inventory management.
Solution:
-
Identification of Patterns:
- SQL queries were used to identify top-selling products and analyze sales patterns. For example, the query to identify top-selling products retrieved data from the OrderDetails table grouped by OrderNumber and ProductCode, ordered by total sales.
- Another query analyzed sales by price range to determine the relationship between price and sales.
-
What-If Analysis:
- A what-if analysis was conducted to assess the impact of reducing inventory by 5%. SQL queries were used to calculate the total quantity reduction and the total sales after the reduction.
-
Recommendations:
- Based on the findings, recommendations were formulated to optimize inventory management and enhance customer service while considering the possibility of closing a storage warehouse. Suggestions included prioritizing stocking of top-selling products, adjusting prices strategically, and evaluating the impact of warehouse closure through scenario analysis.
Approach:
- Understanding the Problem: Analyzed the business problem and the objectives of Mint Classics Company.
- Data Exploration: Examined the Model Car Database structure and identified relevant tables and fields.
- SQL Querying: Utilized SQL queries to retrieve and analyze data, focusing on sales patterns, inventory levels, and customer orders.
- Insight Generation: Derived insights from the data analysis to formulate recommendations addressing the business problem.
- Documentation: Prepared a comprehensive report summarizing key findings, recommendations, and SQL scripts used in the analysis.
Conclusion: Through the process of data analysis and SQL querying, actionable insights were generated to assist Mint Classics Company in optimizing inventory management and addressing the challenges associated with potentially closing a storage facility. The project showcases critical thinking, problem-solving skills, and proficiency in SQL, demonstrating the ability to provide valuable solutions to real-world business problems.