Project Description
This project focuses on analyzing a pizza business's operations using SQL to optimize inventory management, calculate costs, and evaluate employee productivity. The goal was to derive actionable insights from raw data to improve decision-making and efficiency.
1. Joining Tables
The first operation was to combine tables using a LEFT JOIN. I connected the address, orders, and item tables. This initial step enabled me to analyze the quantity of ingredients required to fulfill all orders. I also joined the orders and item tables using a LEFT JOIN on the item_id column, which allowed me to calculate the total number of pizzas ordered.
2. Using Subqueries for Simplification
I used subqueries to work with selected columns more effectively. This facilitated the next step: determining the total required quantity of each ingredient for all orders. At this stage, I generated a new column, ingredient_cost, which represented the total cost of ingredients per order.
3. Transition to Common Table Expressions (CTEs)
To improve readability and handle multiple datasets simultaneously, I replaced subqueries with CTEs (Common Table Expressions). This allowed me to calculate the weight of all ingredients used for pizza production and assess the inventory_remaining for each ingredient.
4. Employee Hours and Salary Calculation
I shifted my focus to calculating employee work hours and their corresponding wages. Using the TIMEDIFF() function, I converted work hours into minutes, divided the total by 60 to obtain hours, and then multiplied the results by the hourly wage. This yielded the total labor costs for each employee.
Final Outputs:
- Ingredient Cost Analysis: A detailed breakdown of the cost of ingredients per order.
- Inventory Management: Calculated the remaining stock percentage for each ingredient and identified items needing restocking.
- Operational Costing: Assessed employee labor costs to better understand total operational expenses.
This structured approach using SQL provided actionable insights to streamline inventory management, optimize labor costs, and improve overall business efficiency.
Project Description
This project focuses on analyzing a pizza business's operations using SQL to optimize inventory management, calculate costs, and evaluate employee productivity. The goal was to derive actionable insights from raw data to improve decision-making and efficiency.
THE STORY
There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text. There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text.
OUR APPROACH
There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text. There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text.
OUR APPROACH
There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text. There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isn't anything embarrassing hidden in the middle of text.