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 (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.
This database comprises 5 tables: customer_orders, runners, runner_orders, pizza_name, pizza_recipes, pizza_toppings
Customer pizza orders are captured in the customer_orders table with 1 row for each individual pizza that is part of the order. See table
The pizza_id relates to the type of pizza which was ordered whilst the exclusions are the ingredient_id values which should be removed from the pizza and the extras are the ingredient_id values which need to be added to the pizza.
Case Study Questions
This case study has LOTS of questions — they are broken up by area of focus including:
A. Pizza Metrics
B. Runner and Customer Experience
C. Ingredient Optimisation
So, I will start by answering — Pizza Metrics
A. Pizza Metrics
1. How many pizzas were ordered?
2. How many unique customer orders were made?
3. How many successful orders were delivered by each runner?
4. How many of each type of pizza was delivered?
5. How many Vegetarian and Meatlovers were ordered by each customer?
Customer 102: 2 Meatlovers, 1 Vegetarian
Customer 103: 3 Meatlovers, 1 Vegetarian
Customer 104: 3 Meatlovers
Customer 105: 1 Vegetarian
6. What was the maximum number of pizzas delivered in a single order?
7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
8. How many pizzas were delivered that had both exclusions and extras?
9. What was the total volume of pizzas ordered for each hour of the day?
Lowest volume at 11:00 am, 7:00 pm, and 11:00 pm
10. What was the volume of orders for each day of the week?
5 pizzas were ordered on Friday & Monday. 3 pizzas were ordered on Saturday and a pizza was ordered on Sunday.
Thanks for reading. I would also love feedback incase of code errors and maybe a more simpler way to answer this question.
I’m ready to tackle the Runner and Customer Experience, Ingredient Optimisation in the next post!!
