PIZZA-SALES-CASE-STUDY

Introduction:

Things are going OK at Plato’s, but there’s room for improvement. Plato’s Pizza have been collecting transactional data for the past year, but haven’t been able to put it to good use. As a Data Analyst, Plato’s Pizza needs me to analyze the data and put together a report to help them find opportunities to drive more sales and work more efficiently.

About Dataset Contents This pizza sales dataset has 12 relevant features:

• order_id: Unique identifier for each order placed by a table

• order_details_id: Unique identifier for each pizza placed within each order (pizzas of the same type and size are kept in the same row, and the quantity increases)

• pizza_id: Unique key identifier that ties the pizza ordered to its details, like size and price

• quantity: Quantity ordered for each pizza of the same type and size

• order_date: Date the order was placed (entered into the system prior to cooking & serving)

• order_time: Time the order was placed (entered into the system prior to cooking & serving)

• unit_price: Price of the pizza in USD

• total_price: unit_price * quantity

• pizza_size: Size of the pizza (Small, Medium, Large, X Large, or XX Large)

• pizza_type: Unique key identifier that ties the pizza ordered to its details, like size and price

• pizza_ingredients: ingredients used in the pizza as shown in the menu (they all include Mozzarella Cheese, even if not specified; and they all include Tomato Sauce, unless another sauce is specified)

• pizza_name: Name of the pizza as shown in the menu

sql skill- join, window function,aggregate function,where/having clause, order by , etc.

Business Tasks

1. Find the different KPIs
   1. what is the total revenue?
   2. what is the average order value?
   3. How much total Pizzas Sold?
   4. what is the Total number of Orders?
   5. what are Average Pizzas Per Order?
2. what are the Daily Trends for Total Orders?
3. what are the Monthly Trend for Orders?
4. How much is the % of Sales by Pizza Category?
5. How much is the % of Sales by Pizza Size?
6. how much is the Total Pizzas Sold by Pizza Category?
7. What percentage of sales of pizza category by month?
8. Percentage of sales by pizza category (SIZE)?
9. Percentage of sales of pizza category by quarter?
10. what are the top 5 best sellers by revenue, total quantity, and total order?
11. what are the bottom five worst sellers by revenue, total quantity, and total order?