SQL Sales Analysis

🎯 Purpose: Analyze a mock retail dataset to derive key sales metrics, identify top products, customer segments, and regional trends.

---

Checking Data

SELECT * FROM pizza_sales;

---

Fix Data Types

-- Convert order_date
UPDATE pizza_sales
SET order_date = STR_TO_DATE(order_date, '%d-%m-%Y');

ALTER TABLE pizza_sales
MODIFY COLUMN order_date DATE;

-- Convert order_time
UPDATE pizza_sales
SET order_time = STR_TO_DATE(order_time, '%H:%i:%s');

ALTER TABLE pizza_sales
MODIFY COLUMN order_time TIME;

---

Total Revenue

SELECT SUM(total_price) AS Total_Revenue FROM pizza_sales;

---

Average Order Value

SELECT SUM(total_price) / COUNT(DISTINCT order_id) AS Avg_Order_Value FROM pizza_sales;

---

Total Orders

SELECT COUNT(DISTINCT order_id) AS Total_Orders FROM pizza_sales;

---

Total Pizzas Sold

SELECT SUM(quantity) AS Total_Pizza_Sold FROM pizza_sales;

---

Average Pizzas per Order

SELECT CAST(SUM(quantity) AS DECIMAL(10,2)) / CAST(COUNT(DISTINCT order_id) AS DECIMAL(10,2)) AS Avg_Pizzas_Per_Order
FROM pizza_sales;

---

Daily Trend for Total Orders

SELECT
    DAYNAME(order_date) AS order_day,
    COUNT(DISTINCT order_id) AS total_orders
FROM
    pizza_sales
GROUP BY
    order_day, WEEKDAY(order_date)
ORDER BY
    WEEKDAY(order_date);

---

Monthly Trend for Orders

SELECT
    MONTHNAME(order_date) AS Month_name,
    COUNT(DISTINCT order_id) AS total_orders
FROM
    pizza_sales
GROUP BY
    MONTHNAME(order_date);

---

Monthly Trend (Ordered Way)

SELECT
    MONTHNAME(order_date) AS Month_Name,
    COUNT(DISTINCT order_id) AS Total_Orders
FROM
    pizza_sales
GROUP BY
    MONTH(order_date), MONTHNAME(order_date)
ORDER BY
    MONTH(order_date);

---

% of Sales by Pizza Category

SELECT
    pizza_category,
    CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,
    CAST(SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales) AS DECIMAL(10,2)) AS PCT
FROM
    pizza_sales
GROUP BY
    pizza_category;

---

First Month Sales % by Category

SELECT
    pizza_category,
    CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,
    CAST(SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales WHERE MONTH(order_date) = 1) AS DECIMAL(10,2)) AS PCT
FROM
    pizza_sales
WHERE
    MONTH(order_date) = 1
GROUP BY
    pizza_category;

---

% of Sales by Pizza Size

SELECT
    pizza_size,
    CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,
    CAST(SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales) AS DECIMAL(10,2)) AS PCT
FROM
    pizza_sales
GROUP BY
    pizza_size
ORDER BY
    PCT DESC;

---

First Quarter Sales % by Size

SELECT
    pizza_size,
    CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,
    CAST(SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales WHERE QUARTER(order_date) = 1) AS DECIMAL(10,2)) AS PCT
FROM
    pizza_sales
WHERE
    QUARTER(order_date) = 1
GROUP BY
    pizza_size
ORDER BY
    PCT DESC;

---

Total Pizzas Sold by Category (Feb)

SELECT
    pizza_category,
    SUM(quantity) AS Total_Quantity_Sold
FROM
    pizza_sales
WHERE
    MONTH(order_date) = 2
GROUP BY
    pizza_category
ORDER BY
    Total_Quantity_Sold DESC;

---

Top 5 Pizzas by Revenue

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

---

Bottom 5 Pizzas by Revenue

SELECT
    pizza_name,
    SUM(total_price) AS Total_Revenue
FROM
    pizza_sales
GROUP BY
    pizza_name
ORDER BY
    Total_Revenue ASC
LIMIT 5;

---

Top 5 Pizzas by Total Orders

SELECT
    pizza_name,
    COUNT(DISTINCT order_id) AS Total_Orders
FROM
    pizza_sales
GROUP BY
    pizza_name
ORDER BY
    Total_Orders DESC
LIMIT 5;

---

END

This SQL project analyzes sales trends, product popularity, and customer behavior for a fictional pizza chain using MySQL and visual breakdowns.