8 Week SQL Challenge #2 — Pizza Runner
Who loves pizza? Danny’s back with a great idea for you 🍕Check out full script on GitHub.
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.
Available Data
Because Danny had a few years of experience as a data scientist — he was very aware that data collection was going to be critical for his business’ growth.
He has prepared for us an entity relationship diagram of his database design but requires further assistance to clean his data and apply some basic calculations so he can better direct his runners and optimise Pizza Runner’s operations.
Entity Relationship Diagram
Case Study Questions
This case study has LOTS of questions — they are broken up by area of focus including:
- Pizza Metrics
- Runner and Customer Experience
- Ingredient Optimisation
- Pricing and Ratings
- Bonus DML Challenges (DML = Data Manipulation Language)
Before you start writing your SQL queries however — you might want to investigate the data, you may want to do something with some of those null values and data types in the customer_orders and runner_orders tables!
A. Pizza Metrics
B. Runner and Customer Experience
C. Ingredient Optimisation
D. Pricing and Ratings
Solution
Prepocessing Data
First of all, do the preprocessing of the data provided by Danny.
We got the tables.
A. Pizza Metrics
- How many pizzas were ordered?
Use COUNT to find out how many pizzas ordered.
14 pizzas ordered.
2. How many unique customer orders were made?
Use COUNT DISTINCT to find out how many unique orders were made.
10 unique customer orders made.
3. How many successful orders were delivered by each runner?
Use COUNT with GROUP BY to find out how many successful orders were delivered by each runner.
Orders that were successfully sent by runners 1, 2 and 3 in sequence are 4, 3, and 1.
4. How many of each type of pizza was delivered?
Use COUNT with GROUP BY to find out how many pizzas were delivered of each type.
Vegetarian pizza ordered 3 times by customers. While Meatlovers 9 times.
5. How many Vegetarian and Meatlovers were ordered by each customer?
Use SUM with GROUP BY to find out how many pizzas were delivered of each type by each customer.
It’s known the total number of pizzas delivered of each type by each customer.
6. What was the maximum number of pizzas delivered in a single order?
Use COUNT with GROUP BY to find out how many pizzas each order delivered. Next find the MAX number of pizzas delivered in a single order.
In a single order, a maximum of 3 pizzas are delivered.
7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
Use SUM with GROUP BY to find out how pizzas delivered changed/no changes by each customer.
It’s known that the total number of pizzas delivered has a change or not by each customer.
8. How many pizzas were delivered that had both exclusions and extras?
Use SUM to find out how many pizzas delivered had exceptions and extras.
1 pizza delivered had both exclusions and extras.
9. What was the total volume of pizzas ordered for each hour of the day?
Use COUNT with GROUP BY to find out how many pizzas are delivered each hour.
At 11 and 19, only 1 pizza was ordered. In addition, 3 pizzas ordered.
10. What was the volume of orders for each day of the week?
Adjust first day of week as Monday by adding 2. Then use COUNT with GROUP BY to find out how many pizzas are delivered each day of the week.
On Saturday ordered 3 pizzas. Whereas on Friday and Monday, ordered 5 pizzas. Unfortunately on Sunday only 1 pizza ordered.
B. Runner and Customer Experience
- How many runners signed up for each 1 week period? (i.e. week starts
2021-01-01)
Use COUNT with GROUP BY to find out how many runners signed up for each 1 week period.
In the first week there were 2 runners who signed up. As for the following weeks, only 1 runner signed up each week.
2. What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?
Use DATEDIFF to find out how long each runner takes by each order. Next AVG by each runner.
The average time it takes for runners 1, 2 and 3 respectively to take orders are 14, 20 and 10 minutes.
3. Is there any relationship between the number of pizzas and how long the order takes to prepare?
Use DATEDIFF to find out how long each runner takes by each order. Next AVG by each runner.
Yes, there is. The number of pizzas ordered seems to be correlated to the preparation time.
4. What was the average distance travelled for each customer?
Assuming distance is calculated from how far the runner must travel to deliver the order to the customer’s place. Then it’s known that the average distance traveled by customers from 101 to 105 respectively is 20 km, 18.4 km, 23.4 km, 10 km and 25 km.
5. What was the difference between the longest and shortest delivery times for all orders?
The difference between the longest and shortest delivery time is 30 minutes
6. What was the average speed for each runner for each delivery and do you notice any trend for these values?
The average speed of runner 3 is 40 km/h. While the average speed of runner 1 is between 37.5 km/hour to 60 km/hour and runner 2’s average speed is between 35.1 km/hour to 93.6 km/hour.
7. What is the successful delivery percentage for each runner?
The percentages of successful deliveries for runners 1,2 and 3 respectively to take orders are 100%, 75% and 50%.
C. Ingredient Optimisation
- What are the standard ingredients for each pizza?
Use STRING_AGG with GROUP BY to combine ingredients for each pizza.
Standard ingredients for Meatlovers pizza are: Bacon, BBQ Sauce, Beef, Cheese, Chicken, Mushrooms, Pepperoni, Salami. Meanwhile for Vegetarian pizza are: Cheese, Mushrooms, Onions, Peppers, Tomatoes, Tomato Sauce.
2. What was the most commonly added extra?
Use COUNT to count the number of extras added.
The most added extra is Bacon 4 times.
3. What was the most common exclusion?
Use COUNT to count the number of exclusion removed.
The most removed exclusion is Cheese 2 times.
4. Generate an order item for each record in the customers_orders table in the format of one of the following:
Meat LoversMeat Lovers - Exclude BeefMeat Lovers - Extra BaconMeat Lovers - Exclude Cheese, Bacon - Extra Mushroom, Peppers
CREATE a column with specifications:
Order item column has been added.
5. Generate an alphabetically ordered comma separated ingredient list for each pizza order from the customer_orders table and add a 2x in front of any relevant ingredients
- For example:
"Meat Lovers: 2xBacon, Beef, ... , Salami"
CREATE a column with specifications:
Ingredient list column has been added.
6. What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?
COUNT column topping with specifications:
It’s known the total amount of each ingredient used in all pizzas delivered and sorted by most frequent first.
D. Pricing and Ratings
- If a Meat Lovers pizza costs $12 and Vegetarian costs $10 and there were no charges for changes — how much money has Pizza Runner made so far if there are no delivery fees?
Use CASE with conditional pizza: Meat Lovers (pizza_id:1) = 12, Vegetarian (pizza_id:2) = 10.
If there are no delivery fees, Pizza Runner earns $138.
2. What if there was an additional $1 charge for any pizza extras?
- Add cheese is $1 extra
Separate the extras, because each extra = $1. Use UNION ALL to combine extra cost with pizza cost (see D. Pricing and Ratings syntax no. 1). Next SUM all costs.
If there was an additional charge for any pizza extras, Pizza Runner earns $142.
3. The Pizza Runner team now wants to add an additional ratings system that allows customers to rate their runner, how would you design an additional table for this new dataset — generate a schema for this new table and insert your own data for ratings for each successful customer order between 1 to 5.
CREATE a table with specifications:
INSERT new record in the table.
Rating table schema has been created.
4. Using your newly generated table — can you join all of the information together to form a table which has the following information for successful deliveries?
customer_idorder_idrunner_idratingorder_timepickup_time- Time between order and pickup
- Delivery duration
- Average speed
- Total number of pizzas
CREATE a table with specifications:
New generated table has been created.
5. If a Meat Lovers pizza was $12 and Vegetarian $10 fixed prices with no cost for extras and each runner is paid $0.30 per kilometre traveled — how much money does Pizza Runner have left over after these deliveries?
Multiply the total distance of all runners by -0.3 (multiplied by minus because the pizza profit is reduced by the runner's pay). Use UNION ALL to combine runner pay with pizza cost (see D. Price and Rating syntax no. 1). Next SUM all costs.
After deliveries Pizza Runner has leftover money $94.44.
