Pizza Analysis

Github

Business Questions:

1. How many customers do we have eah day on average?
2. How many pizzas are typically in an order? Do we have any bestsellers?
3. Are there any peak hours?
4. Are there any crest hours?
5. Are there any peak days?
6. Are there any creast days?
7. Which pizza generated the most revenue? The least?
8. How much money did we make this year?

Insights:

1. On average we have 59 customers per day.
2. The average order contains 2.27 pizzas. With Big Meat (small) being the bestseller.
3. The top 3 best hours our 12:00pm, 1:00pm, and 6:00pm
4. The top 3 worst hours our 9:00am, 10:00am, and11:00pm
5. The first of the month has the most orders. 11/27 was the best selling day.
6. The worst selling day was 12/29.
7. The Thai chicken (large) generated the most revenue.
8. We generated $817, 860.05 in revenue this year.

/* 1. How many customers do we have eah day on average? */

SELECT 

    COUNT(order_id) / COUNT(DISTINCT date) AS AvgPizzaOrdersDay

FROM

    orders;

/* 2. How many pizzas are typically in an order? Do we have any bestsellers? */

SELECT 

    COUNT(order_details_id) / COUNT(DISTINCT order_id)

FROM

    order_details;

-- Bestsellers

SELECT 

    pizza_id, COUNT(pizza_id) AS Pizza

FROM

    order_details

GROUP BY 1

ORDER BY 2 DESC;

/* 3. Are there any peak hours? */

SELECT 

    HOUR(time) AS Hour, COUNT(order_id) AS Orders

FROM

    orders

GROUP BY 1

ORDER BY 2 DESC;

/* 4. Are there any crest hours */

SELECT 

    HOUR(time) AS Hour, COUNT(order_id) AS Orders

FROM

    orders

GROUP BY 1

ORDER BY 2;

/* 5. Are there any peak days? */

-- Order by day of Month

SELECT 

    Day(date) AS Day, COUNT(order_id) AS Orders

FROM

    orders

GROUP BY 1

ORDER BY 2 DESC;

-- Day with most sales

SELECT 

    date, COUNT(order_id) AS NumberOfOrders

FROM

    orders

GROUP BY 1

ORDER BY 2 DESC;

/* 6. Are there any creast days? */

-- Day with least sales

SELECT 

    date, COUNT(order_id) AS NumberOfOrders

FROM

    orders

GROUP BY 1

ORDER BY 2;

/* 7. Which pizza generated the most revenue? The least? */

SELECT 

    order_details.pizza_id,

    price * COUNT(order_details.pizza_id) AS Revenue

FROM

    order_details

        LEFT JOIN

    pizzas ON order_details.pizza_id = pizzas.pizza_id

GROUP BY 1 , price

ORDER BY 2 DESC;

/* How much money did we make this year? */

SELECT 

    ROUND(SUM(price * quantity),2) AS Revenue

FROM

    order_details

        LEFT JOIN

    pizzas ON order_details.pizza_id = pizzas.pizza_id;

-- Tables

SELECT *

FROM order_details;

SELECT *

FROM orders;

SELECT *

FROM pizzas;

SELECT *

FROM pizza_types;