🍕 Pizza Sales Analysis — SQL + Power BI

📌 Project Overview

This project analyzes 48,620 rows of real pizza sales data using MySQL for data analysis and Power BI for interactive dashboard visualization.

The goal was to extract meaningful business insights from raw sales data — including revenue trends, peak ordering hours, best-selling pizzas, and category performance.

💡 This project was built as part of my Data Analyst internship portfolio. It includes real errors I faced and how I solved them — showing my problem solving ability.

---

🛠️ Tools Used

Tool	Purpose
MySQL	Database creation, data cleaning, SQL queries
MySQL Workbench	Query execution and database management
Power BI Desktop	Interactive dashboard and visualization
CSV Dataset	Raw pizza sales data (48,620 rows)

---

📂 Project Structure

pizza-sales-analysis/
│
├── 📄 README.md                  ← Project documentation
│
├── 📁 sql/
│   ├── setup.sql                 ← Clean database setup + data cleaning
│   ├── setup_raw.sql             ← Original setup with errors & fixes
│   └── pizza_sales_queries.sql   ← All 15 analysis queries
│
├── 📁 dashboard/
│   ├── dashboard.png             ← Dashboard screenshot
│   └── pizza_sales.pdf           ← Dashboard PDF export
│
├── 📁 query_screenshots/         ← Query Outputs
│   ├── Query_1.png
│   ├── Query_2.png
│   └── ...Query_15.png
│
├── 📊 pizza_sales.csv            ← Raw dataset (48,620 rows)
└── 📊 pizza_sales.pbix           ← Power BI dashboard file

---

🗄️ Dataset Description

File: pizza_sales.csv Rows: 48,620 Columns: 12

Column	Type	Description
pizza_id	INT	Unique row identifier
order_id	INT	Unique order number
pizza_name_id	VARCHAR	Short pizza code
quantity	INT	Quantity ordered
order_date	DATE	Date of order
order_time	TIME	Time of order
unit_price	DECIMAL	Price per pizza
total_price	DECIMAL	quantity × unit_price
pizza_size	VARCHAR	S, M, L, XL
pizza_category	VARCHAR	Classic, Supreme, Veggie, Chicken
pizza_ingredients	TEXT	List of ingredients
pizza_name	VARCHAR	Full pizza name

---

🔧 Data Cleaning

The dataset had mixed date formats (M/D/YYYY and DD-MM-YYYY) which required cleaning before analysis:

-- Step 1: Change column to TEXT for import
ALTER TABLE pizza_sales MODIFY COLUMN order_date TEXT;

-- Step 2: Fix M/D/YYYY format
SET SQL_SAFE_UPDATES = 0;
UPDATE pizza_sales
SET order_date = STR_TO_DATE(order_date, '%m/%d/%Y')
WHERE order_date LIKE '%/%';

-- Step 3: Fix DD-MM-YYYY format
UPDATE pizza_sales
SET order_date = STR_TO_DATE(order_date, '%d-%m-%Y')
WHERE order_date NOT LIKE '2015%'
AND order_date NOT LIKE '2016%'
AND order_date NOT LIKE '2017%';
SET SQL_SAFE_UPDATES = 1;

-- Step 4: Convert back to DATE type
ALTER TABLE pizza_sales MODIFY COLUMN order_date DATE;

📁 See sql/setup_raw.sql for the complete journey including all errors faced and how they were debugged.

---

🐛 Challenges Faced & Solutions

Challenge 1 — Date Format Error:

Error: "Incorrect date value: '13-01-2015' for column order_date"

Cause: Dataset had 2 mixed date formats — some rows used M/D/YYYY and others used DD-MM-YYYY.

Solution:

1. Changed column type to TEXT before import
2. Used STR_TO_DATE() with WHERE clause to fix each format separately
3. Converted back to DATE after cleaning

Lesson learned: Always check date formats in dataset before importing!

---

Challenge 2 — Safe Update Mode Error:

Error 1175: You are using safe update mode and you tried 
to update a table without a WHERE that uses a KEY column

Cause: MySQL's safe mode blocks UPDATE statements without a WHERE clause on a key column.

Solution:

SET SQL_SAFE_UPDATES = 0;
-- run update query
SET SQL_SAFE_UPDATES = 1;

Lesson learned: Always turn safe mode back ON after running the update!

---

Challenge 3 — Wrong Row Count After Import:

Expected: 48,620 rows
Got:      30,096 rows

Cause: Import stopped midway due to date format error in the middle of the file.

Solution:

1. Truncated the table to remove partial data
2. Fixed order_date column to TEXT type
3. Reimported CSV successfully — all 48,620 rows loaded

Lesson learned: Always verify row count after import before running any queries!

---

❓ Business Questions & SQL Queries

🟢 Basic Questions

Q1. Total orders placed

SELECT COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales;

Result: 21,350 orders

---

Q2. Total revenue generated

SELECT ROUND(SUM(total_price), 2) AS total_revenue
FROM pizza_sales;

Result: $817,860.05

---

Q3. Highest priced pizza

SELECT DISTINCT pizza_name, pizza_size, unit_price
FROM pizza_sales
ORDER BY unit_price DESC
LIMIT 1;

Result: The Greek Pizza (XXL) — $35.95

---

Q4. Most common pizza size ordered

SELECT pizza_size, COUNT(*) AS order_count
FROM pizza_sales
GROUP BY pizza_size
ORDER BY order_count DESC
LIMIT 1;

Result: Large (L)

---

Q5. Top 5 most ordered pizzas

SELECT pizza_name, SUM(quantity) AS total_qty
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_qty DESC
LIMIT 5;

---

🟡 Intermediate Questions

Q6. Total quantity by pizza category

SELECT pizza_category, SUM(quantity) AS total_quantity
FROM pizza_sales
GROUP BY pizza_category
ORDER BY total_quantity DESC;

---

Q7. Orders by hour of day

SELECT HOUR(order_time) AS order_hour,
COUNT(DISTINCT order_id) AS num_orders
FROM pizza_sales
GROUP BY order_hour
ORDER BY order_hour;

---

Q8. Category wise pizza distribution

SELECT pizza_category,
COUNT(DISTINCT pizza_name) AS num_pizza_types
FROM pizza_sales
GROUP BY pizza_category
ORDER BY num_pizza_types DESC;

---

Q9. Average pizzas ordered per day

SELECT ROUND(AVG(daily_qty), 2) AS avg_pizzas_per_day
FROM (
    SELECT order_date, SUM(quantity) AS daily_qty
    FROM pizza_sales
    GROUP BY order_date
) AS daily_totals;

---

Q10. Top 3 revenue generating pizzas

SELECT pizza_name,
ROUND(SUM(total_price), 2) AS revenue
FROM pizza_sales
GROUP BY pizza_name
ORDER BY revenue DESC
LIMIT 3;

---

🔴 Advanced Questions

Q11. % revenue contribution by pizza category

SELECT pizza_category,
ROUND(SUM(total_price), 2) AS category_revenue,
ROUND(SUM(total_price) * 100.0 /
SUM(SUM(total_price)) OVER (), 2) AS revenue_pct
FROM pizza_sales
GROUP BY pizza_category
ORDER BY revenue_pct DESC;

---

Q12. Cumulative revenue over time

SELECT order_date,
ROUND(SUM(total_price), 2) AS daily_revenue,
ROUND(SUM(SUM(total_price)) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
), 2) AS cumulative_revenue
FROM pizza_sales
GROUP BY order_date
ORDER BY order_date;

---

Q13. Top 3 pizzas by revenue per category

WITH pizza_revenue AS (
    SELECT pizza_category, pizza_name,
    ROUND(SUM(total_price), 2) AS revenue,
    RANK() OVER (
        PARTITION BY pizza_category
        ORDER BY SUM(total_price) DESC
    ) AS rnk
    FROM pizza_sales
    GROUP BY pizza_category, pizza_name
)
SELECT pizza_category, pizza_name, revenue, rnk
FROM pizza_revenue
WHERE rnk <= 3
ORDER BY pizza_category, rnk;

---

Q14. Peak ordering hours

SELECT HOUR(order_time) AS order_hour,
COUNT(DISTINCT order_id) AS num_orders,
RANK() OVER (
    ORDER BY COUNT(DISTINCT order_id) DESC
) AS peak_rank
FROM pizza_sales
GROUP BY order_hour
ORDER BY num_orders DESC;

---

Q15. Best selling day of the week

SELECT DAYNAME(order_date) AS day_of_week,
COUNT(DISTINCT order_id) AS num_orders,
ROUND(SUM(total_price), 2) AS revenue
FROM pizza_sales
GROUP BY day_of_week
ORDER BY num_orders DESC;

---

📊 Power BI Dashboard

The interactive dashboard includes:

Visual	Insight
4 KPI Cards	Total Orders, Revenue, Pizzas Sold, Avg Order Value
Revenue by Category	Classic leads with $220,053
Orders by Hour	Peak at 12 PM with 2,520 orders
Monthly Revenue Trend	Consistent ~$60K-70K per month
Top 5 Pizzas	Thai Chicken leads with $43,434
Pizza Size Distribution	Large (45.95%) most popular
Best Selling Day	Friday with 3,359 orders
Quarter Slicer	Filter dashboard by Q1/Q2/Q3/Q4
Category Slicer	Filter dashboard by pizza category

---

💡 Key Business Insights

1. Classic category generates the highest revenue ($220,053 — 26.9% of total)
2. Friday is the best selling day with 3,359 orders
3. Peak hours are 12 PM and 1 PM — lunch rush drives most sales
4. Large size is most popular — 45.95% of all orders
5. Thai Chicken Pizza is the top revenue generator at $43,434
6. Revenue is consistent throughout the year — no major seasonal dips
7. Sunday is the slowest day — opportunity for promotional campaigns

---

🚀 How to Run This Project

SQL Setup:

1. Install MySQL and MySQL Workbench
2. Open sql/setup.sql in MySQL Workbench
3. Run each query step by step using Ctrl + Enter
4. Import pizza_sales.csv using Table Data Import Wizard
5. Open sql/pizza_sales_queries.sql
6. Run all 15 queries one by one

Power BI Setup:

1. Install Power BI Desktop (free from microsoft.com)
2. Install MySQL Connector/NET (free from mysql.com)
3. Open pizza_sales.pbix
4. Refresh data connection if needed

---

📈 SQL Concepts Used

Concept	Used In
COUNT DISTINCT	Q1, Q7, Q14
SUM, ROUND	Q2, Q6, Q10
ORDER BY + LIMIT	Q3, Q4, Q5
GROUP BY + Aggregations	Q1-Q10
HOUR(), DAYNAME()	Q7, Q15
Subqueries	Q9
Window Functions SUM OVER	Q11, Q12
CTEs WITH clause	Q13
RANK() + PARTITION BY	Q13, Q14
STR_TO_DATE()	Data cleaning

---

👤 About

Project: Pizza Sales Analysis Tools: MySQL · Power BI · SQL Dataset: 48,620 rows of pizza sales data (Jan–Dec 2015) Purpose: Data Analyst Internship Portfolio Project