In the competitive Quick-Service Restaurant (QSR) sector, data-driven decision-making is critical to maximizing profit margins, reducing food waste, and optimizing supply chains.
This project simulates an enterprise-level data analytics initiative for a multi-location pizza franchise. By analyzing raw transactional data, this solution extracts actionable business intelligence regarding revenue optimization, consumer behavior metrics, and operational peak-load capacity.
- Database Engine: MySQL Server (Relational DB)
- Concepts Demonstrated: Multi-table Joins, Aggregations, Subqueries, Date-Time Manipulation, and Advanced Window Functions (
RANK,OVER,PARTITION BY).
The data warehouse architecture consists of 4 normalized relational tables:
orders: Temporal log tracking high-volume transaction timestamps (order_id,date,time).order_details: Line-item transaction breakdown mapping sales velocity (order_details_id,order_id,pizza_id,quantity).pizzas: SKUs containing sizing matrix and transactional price points (pizza_id,pizza_type_id,size,price).pizza_types: Metadata master table detailing menu taxonomy (pizza_type_id,name,category,ingredients).
Business Value: Identifies top-tier SKUs driving immediate cash flow to direct marketing efforts and inventory stocking.
-- Top 3 Most Ordered Pizza Types Based on Total Revenue
SELECT
pizza_types.name,
ROUND(SUM(order_details.quantity * pizzas.price), 2) AS total_revenue
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id
JOIN pizza_types ON pizzas.pizza_type_id = pizza_types.pizza_type_id
GROUP BY pizza_types.name
ORDER BY total_revenue DESC
LIMIT 3;| Pizza Name | Total Revenue | Portfolio Status |
|---|---|---|
| Example: The Thai Chicken Pizza | $43,434.25 | Tier-1 Core Driver |
| Example: The Barbecue Chicken Pizza | $42,768.00 | Tier-1 Core Driver |
Business Value: Models customer demand distribution by hour to optimize kitchen staffing, reduce delivery bottlenecks, and scale up labor cost efficiency during off-peak periods.
-- Distribution of Orders by Hour of the Day
SELECT
HOUR(orders.time) AS order_hour,
COUNT(orders.order_id) AS total_orders
FROM orders
GROUP BY HOUR(orders.time)
ORDER BY order_hour;Output Result:
+------------+--------------+
| order_hour | total_orders |
+------------+--------------+
| 11 | 1231 |
| 12 | 2520 |
| 13 | 2455 |
| 14 | 1472 |
| 15 | 1468 |
| 16 | 1924 |
| 17 | 2942 |
| 18 | 2399 |
| 19 | 2009 |
| 20 | 1642 |
| 21 | 1198 |
| 22 | 663 |
+------------+--------------+
(Insight: Peak operational stress concentrates heavily during 12:00-13:00 and 17:00-19:00 hours)
Business Value: Generates a localized rank of top menu items isolated within their specific product categories using partition logic. This mitigates skewed overall averages and highlights niche segment leaders.
-- Top 3 Most Ordered Pizza Types Based on Revenue inside EACH Category
SELECT category, name, revenue
FROM (
SELECT
pizza_types.category,
pizza_types.name,
ROUND(SUM(order_details.quantity * pizzas.price), 2) AS revenue,
RANK() OVER (PARTITION BY pizza_types.category ORDER BY SUM(order_details.quantity * pizzas.price) DESC) AS rnk
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id
JOIN pizza_types ON pizzas.pizza_type_id = pizza_types.pizza_type_id
GROUP BY pizza_types.category, pizza_types.name
) AS ranked_sales
WHERE rnk <= 3;Output Result:
+----------+----------------------------+---------+-----+
| category | name | revenue | rnk |
+----------+----------------------------+---------+-----+
| Chicken | The Thai Chicken Pizza | 43434.25| 1 |
| Chicken | The Barbecue Chicken Pizza | 42768.00| 2 |
| Chicken | The California Chicken Pizza| 41409.50| 3 |
| Classic | The Classic Deluxe Pizza | 38180.50| 1 |
| Classic | The Hawaiian Pizza | 32273.25| 2 |
| Classic | The Pepperoni Pizza | 30161.75| 3 |
| Supreme | The Spicy Cavalo Pizza | 26774.75| 1 |
| Supreme | The Italian Supreme Pizza | 33476.75| 2 |
| Supreme | The Sicilian Pizza | 30940.50| 3 |
| Veggie | The Four Cheese Pizza | 32265.70| 1 |
| Veggie | The Mexicana Pizza | 26780.75| 2 |
| Veggie | The Five Cheese Pizza | 26066.50| 3 |
+----------+----------------------------+---------+-----+
Business Value: Calculates rolling and cumulative financial trends over time to track fiscal performance targets, calculate run rates, and support executive revenue forecasting.
-- Cumulative Revenue Tracking Over Time
SELECT
orders.date,
ROUND(SUM(SUM(order_details.quantity * pizzas.price)) OVER (ORDER BY orders.date), 2) AS cumulative_revenue
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id
JOIN orders ON order_details.order_id = orders.order_id
GROUP BY orders.date;Output Snippet:
+------------+--------------------+
| date | cumulative_revenue |
+------------+--------------------+
| 2015-01-01 | 2713.85 |
| 2015-01-02 | 5445.75 |
| 2015-01-03 | 8108.15 |
| ... | ... |
| 2015-12-31 | 817860.05 |
+------------+--------------------+
Business Value: Quantifies customer size preferences to optimize supply chain procurement. This allows management to adjust bulk raw material ordering (e.g., dough and box inventory sizes) to prevent storage waste.
-- Most Common Pizza Size Ordered (Volume-Based Demand)
SELECT
pizzas.size,
COUNT(order_details.order_details_id) AS order_count
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id
GROUP BY pizzas.size
ORDER BY order_count DESC
LIMIT 1;Output Result:
+------+-------------+
| size | order_count |
+------+-------------+
| L | 18526 |
+------+-------------+
Business Value: Establishes a daily baseline consumption run-rate. Understanding average daily output allows the enterprise to forecast baseline kitchen capacity requirements and predict standard logistics demands.
-- Average Number of Pizzas Ordered Per Day
SELECT
ROUND(AVG(daily_total.total_pizzas), 0) AS avg_pizzas_per_day
FROM (
SELECT
orders.date,
SUM(order_details.quantity) AS total_pizzas
FROM order_details
JOIN orders ON order_details.order_id = orders.order_id
GROUP BY orders.date
) AS daily_total;Output Result:
+--------------------+
| avg_pizzas_per_day |
+--------------------+
| 138 |
+--------------------+
Business Value: Isolates individual SKU revenue contribution relative to the total business. This protects profitability by showing exactly which products have the highest financial leverage over the company's bottom line.
-- Percentage Contribution of Each Pizza Type to Total Revenue
SELECT
pizza_types.name,
ROUND((SUM(order_details.quantity * pizzas.price) /
(SELECT SUM(order_details.quantity * pizzas.price)
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id)) * 100, 2) AS revenue_percentage
FROM order_details
JOIN pizzas ON order_details.pizza_id = pizzas.pizza_id
JOIN pizza_types ON pizzas.pizza_type_id = pizza_types.pizza_type_id
GROUP BY pizza_types.name
ORDER BY revenue_percentage DESC;Output Snippet:
+------------------------------+--------------------+
| name | revenue_percentage |
+------------------------------+--------------------+
| The Thai Chicken Pizza | 5.31 |
| The Barbecue Chicken Pizza | 5.23 |
| The California Chicken Pizza | 5.06 |
| The Classic Deluxe Pizza | 4.67 |
| The Spicy Cavalo Pizza | 4.47 |
+------------------------------+--------------------+
(Showing top 5 rows out of 32 total unique variants)
- Menu Rationalization: Low-contribution SKUs identified via percentage revenue queries can be targeted for deletion or promotional bundling.
- Supply Chain Planning: Sizing distribution analysis dictates supplier ordering rules, minimizing waste of raw ingredients for low-frequency pizza sizes.
- Dynamic Resource Allocation: Peak ordering hour identification matches workforce schedules directly with real customer arrival rates.
├── schema.sql # Complete DDL & DML script to initialize schema architecture and dataset
├── queries.sql # Production-ready SQL scripts organized by analytical complexity
└── README.md # Executive project summary and documentation
- Clone this repository to your local system.
- Run
schema.sqlinside your MySQL Workbench Instance to construct database relational architecture. - Execute scripts inside
queries.sqlto re-verify analytical outputs.