🍕 Pizza Sales SQL Analysis

A SQL project based on a fictional pizza sales dataset to practice solving real-world business problems using SQL.

🔍 Objective

Analyze sales, revenue, and ordering patterns for a pizza business using SQL.

---

📁 Dataset

The dataset contains details about:

• Orders

• Pizzas and categories

• Pizza sizes and prices

  🗄️ SQL Database File

You can recreate the database using this SQL file:

📥 Pizza Sales.sql

How to Use:

1. Open MySQL Workbench
2. Go to File > Open SQL Script
3. Select Pizza Sales.sql
4. Create a new schema (database) and run the script to load the tables and data

---

📊 What I did:

• Analyzed total sales, top products, and ordering trends
• Identified the most profitable pizzas and categories
• Explored order patterns by time and pizza size
• Wrote advanced queries using JOINs, CTEs, Subqueries & Window Functions

---

🧠 Skill Highlights:

• ✅ JOINs, GROUP BY, Aggregations

• ✅ Date & Time functions

• ✅ Cumulative revenue analysis using WINDOW functions

• ✅ Business insight extraction using SQL

  ---

📊 SQL Questions & Use Cases

🟢 Basic

• Retrieve the total number of orders placed.
• Calculate total revenue from pizza sales.
• Identify the highest-priced pizza.
• Most common pizza size ordered.
• Top 5 most ordered pizza types (by quantity).

🟢 Basic Queries

SELECT count(order_id) AS total_orders 
FROM ORDERS;

SELECT 
    ROUND(SUM(orders_details.quantity * pizzas.price),
            2) AS total_sales
FROM
    orders_details
        JOIN
    pizzas ON pizzas.pizza_id = orders_details.pizza_id;

📸 Output Screenshots

---

🟡 Intermediate

• Total quantity per pizza category.

• Orders distribution by hour of the day.

• Category-wise pizza distribution.

• Daily average of pizzas ordered.

• Top 3 most ordered pizza types by revenue.

  🟡 Intermediate Queries

 SELECT 
    ROUND(AVG(quantity), 0) AS avg_pizza_ordered_per_day
FROM
    (SELECT 
        orders.order_date, SUM(orders_details.quantity) AS quantity
    FROM
        orders
    JOIN orders_details ON orders.order_id = orders_details.order_id
    GROUP BY orders.order_date) AS order_quantity;

 SELECT 
    HOUR(order_time) AS hour, COUNT(order_id) AS order_count
FROM
    orders
GROUP BY HOUR(order_time);

📸 Output Screenshots

---

🔴 Advanced

• % revenue contribution of each pizza type.
• Cumulative revenue over time.
• Top 3 pizza types by revenue for each category.

🔴 Advanced Queries

SELECT order_date,
SUM(revenue) over(order by order_date) AS cum_revenue
FROM 
(SELECT orders.order_date,
SUM(orders_details.quantity * pizzas.price) AS revenue
FROM orders_details JOIN pizzas
ON orders_details.pizza_id = pizzas.pizza_id
JOIN orders
ON orders.order_id - orders_details.order_id
GROUP BY orders.order_date) AS sales;

SELECT 
    pizza_types.name,
    SUM(orders_details.quantity * pizzas.price) AS revenue
FROM
    pizza_types
        JOIN
    pizzas ON pizzas.pizza_type_id = pizza_types.pizza_type_id
        JOIN
    orders_details ON orders_details.pizza_id = pizzas.pizza_id
GROUP BY pizza_types.name
ORDER BY revenue DESC
LIMIT 3;

📸 Output Screenshots