Transforming raw pizza sales data into crisp, actionable business intelligence — one query at a time.
Core numbers from the dataset powering this analysis.
Four relational tables connected across order, detail, pizza, and type dimensions.
orders ─── 1:N ───> order_details | order_details ─── N:1 ───> pizzas | pizzas ─── N:1 ───> pizza_types
13 queries organized across four analytical pillars.
| # | Business Question | SQL Technique |
|---|---|---|
| Q1 | Total revenue generated | SUM(price × quantity) |
| Q2 | Highest-priced pizza | ORDER BY price DESC LIMIT 1 |
| Q3 | Average order value | Total revenue ÷ COUNT(orders) |
| # | Business Question | SQL Technique |
|---|---|---|
| Q4 | Total number of orders placed | COUNT(DISTINCT order_id) |
| Q5 | Most preferred pizza size | GROUP BY size ORDER BY COUNT |
| Q6 | Peak ordering hours | HOUR(time) extraction & GROUP BY |
| # | Business Question | SQL Technique |
|---|---|---|
| Q7 | Top 5 pizzas by quantity sold | SUM(quantity) GROUP BY + LIMIT 5 |
| Q8 | Sales distribution by category | GROUP BY category with % share |
| Q9 | Category generating most revenue | Revenue aggregated per category |
| # | Business Question | SQL Technique |
|---|---|---|
| Q10 | Cumulative revenue growth over time | SUM() OVER (ORDER BY date) |
| Q11 | Average daily order volume | CTE → AVG of daily counts |
| Q12 | Top 3 pizzas within each category | RANK() OVER (PARTITION BY category) |
| Q13 | Revenue % per category | Subquery for total ÷ category revenue |
Key queries demonstrating advanced SQL concepts used across all four modules.
-- Q10: Cumulative revenue growth over time (Rolling Window) SELECT date, SUM(revenue) OVER ( ORDER BY date ) AS cumulative_revenue FROM ( SELECT o.date, SUM(od.quantity * p.price) AS revenue FROM orders o JOIN order_details od ON o.order_id = od.order_id JOIN pizzas p ON od.pizza_id = p.pizza_id GROUP BY o.date ) daily_revenue ORDER BY date;
-- Q11: Average daily order volume using CTE WITH daily_orders AS ( SELECT date, COUNT(order_id) AS total_orders FROM orders GROUP BY date ) SELECT ROUND(AVG(total_orders), 2) AS avg_orders_per_day FROM daily_orders;
-- Q12: Top 3 pizzas within each category using RANK() SELECT category, name, quantity, rnk FROM ( SELECT pt.category, pt.name, SUM(od.quantity) AS quantity, RANK() OVER ( PARTITION BY pt.category ORDER BY SUM(od.quantity) DESC ) AS rnk FROM pizza_types pt JOIN pizzas p ON pt.pizza_type_id = p.pizza_type_id JOIN order_details od ON p.pizza_id = od.pizza_id GROUP BY pt.category, pt.name ) ranked WHERE rnk <= 3 ORDER BY category, rnk;
Findings distilled from all 13 queries across the full dataset.
Staffing and promotional campaigns should prioritize these two daily windows.
All 13 business logic queries — labelled by question for easy navigation.
Project documentation, schema diagram, insights summary, and setup guide.
git clone https://github.com/pritwrk9-ux/Pizza_sale--Sql.gitMySQL Workbench and connect to your local MySQL server.orders, order_details, pizzas, pizza_types.Pizza_sales_analysis.sql and run queries individually or all at once. Each is labelled with its business question.