8 Weeks SQL Challenge Case Study #2 — Pizza Runners: Part II
Hi again.
Let’s continue solving Case Study #2 challenges without wasting too much time. If you want to read the first part or Case Study #1 solutions please click on the links in this sentence.
PS: Before starting, for the sake of solving some challenges I created a new table from Pizza toppings.
CREATE TABLE pizza_tops(
id VARCHAR(1),
topping INTEGER);
INSERT INTO pizza_tops
(id, topping)
VALUES
('1','1'),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,8),
(1,10),
(2,4),
(2,7),
(2,9),
(2,11),
(2,12);Ok, now let’s start.
- What are the standard ingredients for each pizza?
I used Select within select approach here, and retrieve two columns, and return only if the two columns are equal to each other. After that joined with pizza_toppings on topping_id to return topping name on the table.
SELECT ones.topping, p.topping_name
FROM
(SELECT id, topping FROM pizza_tops
WHERE id = 1) AS ones,
(SELECT id, topping FROM pizza_tops
WHERE id = 2) AS twos
JOIN pizza_toppings p ON p.topping_id = topping
WHERE ones.topping = twos.topping
2. What was the most commonly added extra?
I used common table expressions here. First I selected the first letter in extras and UNION it with second letter ( if you check the data some extras are like 1,5). With -1 I choose the last extra. The rest was easy, join cte with pizza_toppings, GROUP BY topping_name and LIMIT to 1.
WITH cte AS
(SELECT substring_index(extras,',', 1) AS extras1
FROM customer_orders
UNION ALL
SELECT substring_index(extras,',', -1)
FROM customer_orders)
SELECT COUNT(topping_name), topping_name FROM cte JOIN pizza_toppings p ON p.topping_id = cte.extras1
GROUP BY topping_name
LIMIT 1;
3. What was the most common exclusion?
Same approach as above.
WITH cte AS
(SELECT substring_index(exclusions,',', 1) AS exclusions
FROM customer_orders
UNION ALL
SELECT substring_index(exclusions,',', -1)
FROM customer_orders)
SELECT COUNT(topping_name), topping_name FROM cte JOIN pizza_toppings p ON p.topping_id = cte.exclusions
GROUP BY topping_name
LIMIT 1;
4. Generate an order item for each record in the customers_orders table in the format of one of the following:
Meat Lovers
Meat Lovers — Exclude Beef
Meat Lovers — Extra Bacon
Meat Lovers — Exclude Cheese, Bacon — Extra Mushroom, Peppers
At my level, this one was really hard for me. Somehow, I managed to solve it. I divided the problem into parts.
a. First I create a temporary table than includes extras and exclusions in different columns. I retrieved substring_index -1 if the value has “,” in it as extras2 or exclusions2.
DROP TABLE IF EXISTS extras_exclusions;
CREATE TEMPORARY TABLE extras_exclusions
SELECT *,
substring_index(exclusions, ',', 1) AS exclusions_part_1,
CASE
WHEN exclusions REGEXP ',' THEN substring_index(exclusions, ',', -1) END AS exclusions_part_2,
substring_index(extras, ',', 1) AS extras_1,
CASE
WHEN extras REGEXP ',' THEN substring_index(extras, ',', -1)
END AS extras_2
FROM customer_orders;
b. Secondly create a temp table that includes the extras and exclusions information as Text data (join with topping_names). There are many CTE’s here :)
DROP TABLE IF EXISTS item_record;
CREATE TEMPORARY TABLE item_record
WITH t3 AS
(WITH t2 AS
(WITH t1 AS
(SELECT e.order_id
,p.pizza_name
,e.exclusions_part_1
,e.exclusions_part_2
,extras_1
,extras_2
,pt.topping_name AS exclusion_1
from extras_exclusions e
JOIN pizza_names p ON p.pizza_id = e.pizza_id
LEFT JOIN pizza_toppings pt ON pt.topping_id = e.exclusions_part_1)
SELECT order_id
,pizza_name
,exclusion_1
,extras_1
,extras_2
,topping_name AS exclusion_2
FROM t1
LEFT JOIN pizza_toppings p ON p.topping_id = t1.exclusions_part_2)
SELECT order_id
,pizza_name
,exclusion_1
,exclusion_2
,extras_2
,topping_name AS extra_1
FROM t2
LEFT JOIN pizza_toppings p ON p.topping_id = t2.extras_1)
SELECT order_id
,pizza_name
,exclusion_1
,exclusion_2
,extra_1
,topping_name AS extra_2
FROM t3
LEFT JOIN pizza_toppings p ON p.topping_id = t3.extras_2;
c. And finally the expected table. I used COALESCE function here. The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. So I checked if the 4 columns are NULL, then it returns pizza_name only. Then checked if the exc2, ext1 and 2 are NULL and exc1 is not, then return pizza_name — Exclude: exc1, did the same for ext1. After these, I checked if exc2 and ext2 are NOT NULL (which meant exc1 and ext1 are not null either) then it returned pizza_name — Extra stuff — Exclude stuff.
SELECT
DISTINCT(CASE
WHEN COALESCE(exclusion_1, exclusion_2, extra_1, extra_2) IS NULL THEN pizza_name
WHEN COALESCE(exclusion_2, extra_1, extra_2) IS NULL AND exclusion_1 IS NOT NULL
THEN CONCAT(pizza_name, ' - Exclude ', exclusion_1)
WHEN COALESCE(exclusion_2, exclusion_1, extra_2) IS NULL AND extra_1 IS NOT NULL
THEN CONCAT(pizza_name, ' - Extra ', extra_1)
WHEN COALESCE( exclusion_2, extra_2) IS NOT NULL
THEN CONCAT_WS("", pizza_name, ' - Extra ', extra_1, ', ', extra_2,
' - Exclude ', exclusion_1,', ', exclusion_2)
END) AS item_record
FROM item_record;
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”
First I created a temporary table which includes all toppings in pizzas.
DROP TABLE IF EXISTS toppings;
CREATE TEMPORARY TABLE toppings
SELECT id, GROUP_CONCAT(topping_name, ' ') AS ingredients
FROM pizza_tops p
JOIN pizza_toppings t on p.topping = t.topping_id
GROUP BY id;
Then created a new table for easier manipulation. Ok, that can be done in one query, but I feel safer when I do it part by part. And check whether I did any mistakes.
DROP TABLE IF EXISTS ingredients;
CREATE TEMPORARY TABLE ingredients
SELECT i.pizza_name, extra_1, extra_2, ingredients
FROM item_record i
JOIN
(SELECT * FROM toppings t
JOIN pizza_names n ON n.pizza_id = t.id) p
ON p.pizza_name = i.pizza_name;
And finally the answer:
SELECT CONCAT(pizza_name, ' : ' ,
CASE
WHEN LOCATE(extra_1, ingredients) > 0 AND LOCATE(extra_2, ingredients) > 0
THEN REPLACE(REPLACE(ingredients, extra_2, CONCAT('2x',extra_2)), extra_1, CONCAT('2x',extra_1))
ELSE ingredients
END)
FROM ingredients;
6. What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?
First I created a temporary table for each pizza topping.
DROP TABLE IF EXISTS extras_exc;
CREATE TEMPORARY TABLE extras_exc
SELECT id, topping FROM pizza_tops;
INSERT INTO extras_exc SELECT pizza_id, extras_1 AS topping FROM extras_exclusions
WHERE extras_1 IS NOT NULL;
INSERT INTO extras_exc SELECT pizza_id, extras_2 AS topping FROM extras_exclusions
WHERE extras_2 IS NOT NULL;
Then retrieve counts of ingredients of each pizza and each order.
SELECT t.topping_name, COUNT((p.topping))
FROM extras_exc e
JOIN pizza_tops p ON p.id = e.id
JOIN pizza_toppings t ON p.topping = t.topping_id
GROUP BY t.topping_name
ORDER BY COUNT((p.topping)) DESC
Part IV. 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?
Solved it with CASE WHEN statement, and retrieve the sum.
SELECT
SUM(CASE WHEN pizza_id=1 THEN 12
WHEN pizza_id = 2 THEN 10
END) AS Total_earnings
FROM runner_orders r
JOIN customer_orders c ON c.order_id = r.order_id
WHERE r.cancellation IS NULL;
2. What if there was an additional $1 charge for any pizza extras?
Solved this with common table expressions again. Created price column, exclusions and extras. And retrieve the sum of all depending on the extras column.
WITH cte AS
(SELECT
(CASE WHEN pizza_id=1 THEN 12
WHEN pizza_id = 2 THEN 10
END) AS pizza_cost,
c.exclusions,
c.extras
FROM runner_orders r
JOIN customer_orders c ON c.order_id = r.order_id
WHERE r.cancellation IS NULL)
SELECT
SUM(CASE WHEN extras IS NULL THEN pizza_cost
WHEN LENGTH(extras) = 1 THEN pizza_cost + 1
ELSE pizza_cost + 2
END )
FROM cte;
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.
I created a random table for that.
DROP TABLE IF EXISTS ratings;
CREATE TABLE ratings
(order_id INTEGER,
rating INTEGER);INSERT INTO ratings
(order_id ,rating)
VALUES
(1,3),
(2,4),
(3,5),
(4,2),
(5,1),
(6,3),
(7,4),
(8,1),
(9,3),
(10,5);
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?
I created new columns with math operations, JOIN customer_orders with runner_orders, and ratings. GROUP BY customer_id, order_id, runner_id, rating and order_time, pickup_time…. (see the code block).
SELECT c.customer_id, c.order_id, r.runner_id, rt.rating, c.order_time,
r.pickup_time, TIMESTAMPDIFF(minute, order_time, pickup_time) as delivery_delay,
r.duration, ROUND(avg(r.distance*60/r.duration),1) as avg_speed,
count(c.pizza_id) as PizzaCount
FROM customer_orders c
JOIN runner_orders r
ON c.order_id = r.order_id
JOIN ratings rt
ON rt.order_id = c.order_id
GROUP BY c.customer_id, c.order_id, r.runner_id, rt.rating, c.order_time,
r.pickup_time, delivery_delay, r.duration
ORDER BY c.customer_id;
4. 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?
SELECT
SUM(CASE WHEN pizza_id=1 THEN 12
WHEN pizza_id = 2 THEN 10
END) - SUM((r.distance+0) * 0.3) AS pizza_cost,
SUM(CASE WHEN pizza_id=1 THEN 12
WHEN pizza_id = 2 THEN 10
END ) AS pizza_only,
(SUM(r.distance+0) * 0.3) AS distance_cost
FROM runner_orders r
JOIN customer_orders c ON c.order_id = r.order_id
WHERE r.cancellation IS NULL
That is the end. This is how I tried to solve Danny Ma’s 8-week SQL challenge week II. I hope you like my approach, if you do please consider giving an upvote.
All the best
Orkun
