In this project we use SQL manage pizza orders, including selecting pizza types, placing orders, and analyzing order data.

Table of contents

• 00. Project Overview
• 01. Data Overview & Preparation
• 02. Exploratory Data Analysis

Project Overview

The project aimed to enhance Domino’s India operations and menu selection. Implement SQL queries to manage pizza orders, including selecting pizza types, placing orders, and analyzing order data.

Data Overview & Preparation

Pizzas Table

select * from pizzas;

Output: A sample of first 5 rows are displayed

Data Dictionary:

• Pizza_id – name of the pizza(including size)
• Pizza_type_id – refers to the type of the pizza i.e. veg or non-veg.
• Size – refers to the size of the pizza.
• Price – refers to the price of the pizza.

Pizza Types Table

select * from pizza_types;

Output: A sample of first 5 rows are displayed

Data Dictionary:

• Pizza_type_id – refers to the type of the pizza i.e veg or non-veg
• Name – name of the pizza.
• Category – refers to the category of the pizza.

Orders Table

select * from pizzas;

Output: A sample of first 5 rows are displayed

Data Dictionary:

• order_id – refers to the order ID.
• date – refers to the date of the order placed.
• time – refers to the time of the order placed.

Orders Details Table

select * from order_details;

Output: A sample of first 5 rows are displayed

Data Dictionary:

• order_detail_id – refers to the ID of the order_detail.
• order_id – refers to the ID of the order.
• Pizza_id – name of the pizza(including size)
• Quantity – refers to the quantity of items.

Explorating Data Analysis

This section represents each of the analysis conducted for answering the business questions, along with the key findings and insights.

Daily Customer Count and Peak Hours

SELECT
    DATE(date) AS order_date,
    COUNT(DISTINCT order_id) AS total_customers,
    EXTRACT(HOUR FROM time) AS hour_of_day
FROM Orders
GROUP BY order_date, hour_of_day
ORDER BY total_customers DESC;

Output: A sample of first 10 rows are displayed

Observations

• This query calculates the number of customers each day and identifies peak hours.
• Between 12 to 1 PM ( hour_of_day = 13 ) & 4 to 8 PM ( hour_of_day = 19 ) is the peak where we are getting maximum orders.

Insights

• It can help in understanding daily demand patterns and planning staff schedules accordingly.

Average Pizzas per Order and Bestsellers

/* Average Pizzas per order */
SELECT AVG(quantity) AS avg_pizzas_per_order
FROM Order_Details;


/* Bestselling pizzas */
SELECT pizza_id, SUM(quantity) AS total_sold
FROM Order_Details
GROUP BY pizza_id
ORDER BY total_sold DESC
LIMIT 5;

Output:

Observations

• Average number of pizzas per order is 1 which is essential for inventory management.
• There are 5 best selling pizzas i.e. these pizzas are ordered maximum number of times.

Insights

• Identifying bestselling pizzas allows you to focus on popular items and potentially optimize menu offerings.

Monthly Revenue and Seasonality

SELECT
    MONTHNAME(date) AS month,
    SUM(Price * quantity) AS revenue
FROM Orders
JOIN Order_Details ON Orders.order_id = Order_Details.order_id
JOIN Pizzas ON Order_Details.pizza_id = Pizzas.pizza_id
GROUP BY month
ORDER BY revenue DESC;

Output:

Observations

• It calculates the revenue for each month of the year.
• July month is the highest revenue followed by others. From July-October, The revenue went down.

Insights

• By analyzing monthly revenue, you can identify seasonality in sales and plan promotions or menu changes accordingly.

Pizza Sales Analysis

SELECT od.pizza_id, SUM(od.quantity) AS totalunitssold, (p.Price * SUM(od.quantity)) AS totalrevenue
FROM order_details od 
INNER JOIN pizzas p 
        ON od.pizza_id = p.pizza_id
GROUP BY od.pizza_id
ORDER BY totalunitssold
;

Output: A sample of first 5 rows are displayed

Observations

• This query provides information on the sales of each pizza.

Insights

• It can help in identifying underperforming pizzas that may need to be removed from the menu or promoting top sellers.

Average Order Value by Pizza Category

WITH table1 AS (
SELECT o.order_id, o.date, od.pizza_id, od.quantity, CASE
  WHEN od.quantity = 4 THEN 4*p.price
  WHEN od.quantity = 3 THEN 3*p.price
  WHEN od.quantity = 2 THEN 2*p.price
ELSE p.price
END AS tprice, p.pizza_type_id, pt.Category
FROM orders o 
INNER JOIN order_details od
        ON o.order_id = od.order_id
INNER JOIN pizzas p
        ON od.pizza_id = p.pizza_id
INNER JOIN pizza_types pt
        ON p.pizza_type_id = pt.pizza_type_id
)

SELECT Category, AVG(tprice) AS Average_Order_Value
FROM table1
GROUP BY Category
ORDER BY Average_Order_Value desc;

Output:

Observations

• Average amount spent by customers is highest for Non-veg pizzas around 600 followed by 500 for Veg Pizzas.
• Lowest amount is spent for the category “more” items i.e. water, pepsi, ice tea etc.

Insights

• This information can be used for pricing strategies and understanding customer preferences.

Sales Trends by Day of the Week

SELECT
    DAYNAME(date) AS day_of_week,
    SUM(Price * quantity) AS total_sales
FROM Orders
JOIN Order_Details ON Orders.order_id = Order_Details.order_id
JOIN Pizzas ON Order_Details.pizza_id = Pizzas.pizza_id
GROUP BY day_of_week
ORDER BY total_sales DESC;

Output:

Observations

• Maximum revenue is generated on Sunday followed by Saturday and Monday.
• This makes sense that on weekends there will be more people visiting the restaurants and also there might be special discounts/offers on items which can lead to maximum sales.

Insights

• It can help in optimizing marketing efforts and staffing levels on specific days.