Running Man, but for Pizza
Case Study #2 Of Danny Ma’s #8WeekSQLChallenge
Previously with Danny, I took a look at his Diner’s Data. This time around, I will be working with his Pizza outfit’s data.
INTRODUCTION
Danny was scrolling through his Instagram feed when something really caught his eye — “80s Retro Styling and Pizza Is The Future!”
Danny was sold on the idea, but he knew that pizza alone was not going to help him get seed funding to expand his new Pizza Empire — so he had one more genius idea to combine with it — he was going to Uberize it — and so Pizza Runner was launched!
Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.
PROBLEM STATEMENT
Danny needed help cleaning his available data and a general analysis of how his business had fared and how he could optimise operations.
DATA
The Pizza Runner schema contained six tables:
1. runners
The runners table contains the id numbers and the date each of them registered.
2. pizza_names
The pizza_names table has information about the types of pizza sold — Meatlovers and Vegetarian.
3. pizza_toppings
The pizza_toppings table contains information about the kinds of topping used on Meatlovers and Vegetarian, 12 of them in total.
4. pizza_recipes
The pizza_recipes table contains information on how the toppings are used.
— Meatlovers has the following toppings: bacon(1), BBQ sauce(2), beef(3), cheese(4), chicken(5), mushrooms(6), pepperoni(8), salami(10).
— Vegetarian has the following toppings: cheese(4), mushrooms(6), onions(7), peppers(9), tomatoes(11), tomato sauce(12).
5. customer_orders
The customer_orders table contains information about, well, customer orders. It displays the type of pizza bought, whether or not toppings were excluded or added as extras and the time the orders were placed.
6. runner_orders.
The runners_orders table contains information about the runners — the time they arrived to pick up orders, the distance(km) covered to get to customers and how long(minutes) it took them.
The Entity Relationship Diagram can be seen below:
NOTE: ALL queries used for the cleaning and analysis can be found here and postgresql was used for this project.
QUESTIONS
The questions were broken down into four categories:
— Pizza Metrics.
— Runner and Customer Experience.
— Ingredient Optimisation.
— Pricing.
Pizza Metrics
- How many pizzas were ordered?
A total of 14 pizzas were ordered.
2. How many unique customer orders were made?
A total of 10 orders were placed by different customers. Some of the orders had multiple pizzas hence the 14 seen in question 1.
3. How many successful orders were delivered by each runner?
Three runners successfully delivered eight orders. Runner 1 had the most deliveries with four. Runner 4 had not made any delivery as at the time of this report. It should be noted two orders were cancelled.
4. How many of each type of pizza was delivered?
Meatlovers was delivered nine times and Vegetarian was delivered three times.
5. How many Vegetarian and Meatlovers were ordered by each customer?
All customers except customer 104 ordered Vegetarian once and all customers except customer 105 ordered Meatlovers at least once.
6. What was the maximum number of pizzas delivered in a single order?
The 4th order placed at Pizza Runner had three pizzas in it. It was the highest number of pizzas in one order that was succesfully delivered.
7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
A change means there was an extra topping added or there was an exclusion of a topping. Customer 103 excluded cheese from all three orders (which is quite fascinating; what is pizza without cheese?), customer 104 excluded bacon from one order then in the second order, excluded BBQ sauce and mushrooms and asked for extra bacon and cheese, customer 105 excluded bacon from the order.
8. How many pizzas were delivered that had both exclusions and extras?
Only one order had both exclusions and extras, customer 104's order excluded BBQ sauce and mushrooms and had extra bacon and cheese.
9. What was the total volume of pizzas ordered for each hour of the day?
1pm, 6pm, 9pm and 11pm had the most orders with three each. I would assume the 1pm orders were at lunch break, 6pm was probably dinner and the 9pm and 11pm orders were people who became hungry after eating dinner. Again, an assumption.
10. What was the volume of orders for each day of the week?
Wednesday and Saturday seemed to be the busiest days of the week with five orders on both days. No orders were placed on Sunday, Monday and Tuesday, which coincidentally are the first three days of the week.
CONCLUSION
There will be a second article that addresses the other three categories of questions and also provides other information as regards this analysis. Thank you for reading!
You can connect with me on LinkedIn!