Pizza_Runner_SQL_CaseStudy.sql

CREATE DATABASE pizza_runner;
USE pizza_runner;

CREATE TABLE runners (
    runner_id INT,
    registration_date DATE
);

CREATE TABLE customer_orders (
    order_id INT,
    customer_id INT,
    pizza_id INT,
    exclusions VARCHAR(10),
    extras VARCHAR(10),
    order_time DATETIME
);

CREATE TABLE runner_orders (
    order_id INT,
    runner_id INT,
    pickup_time VARCHAR(20),
    distance VARCHAR(10),
    duration VARCHAR(20),
    cancellation VARCHAR(30)
);

CREATE TABLE pizza_names (
    pizza_id INT,
    pizza_name VARCHAR(50)
);

CREATE TABLE pizza_recipes (
    pizza_id INT,
    toppings VARCHAR(50)
);

CREATE TABLE pizza_toppings (
    topping_id INT,
    topping_name VARCHAR(50)
);

INSERT INTO runners (runner_id, registration_date) VALUES
(1,'2021-01-01'),
(2,'2021-01-03'),
(3,'2021-01-08'),
(4,'2021-01-15');

INSERT INTO customer_orders 
(order_id, customer_id, pizza_id, exclusions, extras, order_time)
VALUES
(1,101,1,'','','2021-01-01 18:05:02'),
(2,101,1,'','','2021-01-01 19:00:52'),
(3,102,1,'','','2021-01-02 23:51:23'),
(3,102,2,'','NaN','2021-01-02 23:51:23'),
(4,103,1,'4','','2021-01-04 13:23:46'),
(4,103,1,'4','','2021-01-04 13:23:46'),
(4,103,2,'4','','2021-01-04 13:23:46'),
(5,104,1,'null','1','2021-01-08 21:00:29'),
(6,101,2,'null','null','2021-01-08 21:03:13'),
(7,105,2,'null','1','2021-01-08 21:20:29'),
(8,102,1,'null','null','2021-01-09 23:54:33'),
(9,103,1,'4','1, 5','2021-01-10 11:22:59'),
(10,104,1,'null','null','2021-01-11 18:34:49'),
(10,104,1,'2, 6','1, 4','2021-01-11 18:34:49');

INSERT INTO runner_orders
(order_id, runner_id, pickup_time, distance, duration, cancellation)
VALUES
(1,1,'2021-01-01 18:15:34','20km','32 minutes',''),
(2,1,'2021-01-01 19:10:54','20km','27 minutes',''),
(3,1,'2021-01-03 00:12:37','13.4km','20 mins','NaN'),
(4,2,'2021-01-04 13:53:03','23.4','40','NaN'),
(5,3,'2021-01-08 21:10:57','10','15','NaN'),
(6,3,'null','null','null','Restaurant Cancellation'),
(7,2,'2020-01-08 21:30:45','25km','25mins','null'),
(8,2,'2020-01-10 00:15:02','23.4 km','15 minute','null'),
(9,2,'null','null','null','Customer Cancellation'),
(10,1,'2020-01-11 18:50:20','10km','10minutes','null');

INSERT INTO pizza_names (pizza_id, pizza_name)
VALUES
(1,'Meat Lovers'),
(2,'Vegetarian');

INSERT INTO pizza_recipes (pizza_id, toppings)
VALUES
(1,'1, 2, 3, 4, 5, 6, 8, 10'),
(2,'4, 6, 7, 9, 11, 12');

INSERT INTO pizza_toppings (topping_id, topping_name)
VALUES
(1,'Bacon'),
(2,'BBQ Sauce'),
(3,'Beef'),
(4,'Cheese'),
(5,'Chicken'),
(6,'Mushrooms'),
(7,'Onions'),
(8,'Pepperoni'),
(9,'Peppers'),
(10,'Salami'),
(11,'Tomatoes'),
(12,'Tomato Sauce');

SELECT COUNT(*) FROM runners;
SELECT COUNT(*) FROM customer_orders;
SELECT COUNT(*) FROM runner_orders;
SELECT COUNT(*) FROM pizza_names;
SELECT COUNT(*) FROM pizza_recipes;
SELECT COUNT(*) FROM pizza_toppings;

-- A (Pizza_Metrics)

-- Question.1 :How many pizzas were ordered?
SELECT COUNT(*) AS total_pizzas_ordered
FROM customer_orders;

-- Question.2 :How many unique customer orders were made?
SELECT COUNT(DISTINCT order_id) AS unique_customer_orders
FROM customer_orders;

-- Question.3 :How many successful orders were delivered by each runner?
SELECT
    runner_id,
    COUNT(*) AS successful_orders
FROM runner_orders
WHERE cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation')
GROUP BY runner_id
ORDER BY runner_id;

-- Question.4 :How many of each type of pizza was delivered?
SELECT
    pn.pizza_name,
    COUNT(*) AS delivered_pizzas
FROM customer_orders co
JOIN runner_orders ro
    ON co.order_id = ro.order_id
JOIN pizza_names pn
    ON co.pizza_id = pn.pizza_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation')
GROUP BY pn.pizza_name;

-- Question.5 :How many Vegetarian and Meatlovers were ordered by each customer?
SELECT
    co.customer_id,
    pn.pizza_name,
    COUNT(*) AS total_orders
FROM customer_orders co
JOIN pizza_names pn
    ON co.pizza_id = pn.pizza_id
GROUP BY co.customer_id, pn.pizza_name
ORDER BY co.customer_id, pn.pizza_name;

-- Question.6 :What was the maximum number of pizzas delivered in a single order?
SELECT
    co.order_id,
    COUNT(*) AS total_pizzas
FROM customer_orders co
JOIN runner_orders ro
    ON co.order_id = ro.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation')
GROUP BY co.order_id
ORDER BY total_pizzas DESC
LIMIT 1;

-- Question.7 :For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
SELECT
    co.customer_id,
    SUM(
        CASE
            WHEN (co.exclusions IS NOT NULL
                  AND co.exclusions NOT IN ('', 'null', 'NaN'))
              OR (co.extras IS NOT NULL
                  AND co.extras NOT IN ('', 'null', 'NaN'))
            THEN 1
            ELSE 0
        END
    ) AS pizzas_with_changes,
    SUM(
        CASE
            WHEN (co.exclusions IS NULL
                  OR co.exclusions IN ('', 'null', 'NaN'))
             AND (co.extras IS NULL
                  OR co.extras IN ('', 'null', 'NaN'))
            THEN 1
            ELSE 0
        END
    ) AS pizzas_without_changes
FROM customer_orders co
JOIN runner_orders ro
    ON co.order_id = ro.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation')
GROUP BY co.customer_id
ORDER BY co.customer_id;

-- Question.8 :How many pizzas were delivered that had both exclusions and extras?
SELECT COUNT(*) AS pizzas_with_both
FROM customer_orders co
JOIN runner_orders ro
    ON co.order_id = ro.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation')
  AND co.exclusions NOT IN ('', 'null', 'NaN')
  AND co.extras NOT IN ('', 'null', 'NaN');
  
  -- Question.9 :What was the total volume of pizzas ordered for each hour of the day?
  SELECT
    HOUR(order_time) AS order_hour,
    COUNT(*) AS total_pizzas
FROM customer_orders
GROUP BY HOUR(order_time)
ORDER BY order_hour;

-- Question.10 :What was the volume of orders for each day of the week?
SELECT
    DAYNAME(order_time) AS day_of_week,
    COUNT(DISTINCT order_id) AS total_orders
FROM customer_orders
GROUP BY DAYOFWEEK(order_time), DAYNAME(order_time)
ORDER BY DAYOFWEEK(order_time);

-- B. (Runner and Customer Experience)

-- Question.1 :How many runners signed up for each 1 week period? (i.e. week starts 2021-01-01)
SELECT
    FLOOR(DATEDIFF(registration_date, '2021-01-01') / 7) + 1 AS week_number,
    COUNT(*) AS runners_signed_up
FROM runners
GROUP BY week_number
ORDER BY week_number;

-- Question.2 :What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?
SELECT
    ro.runner_id,
    ROUND(
        AVG(
            TIMESTAMPDIFF(
                MINUTE,
                co.order_time,
                CASE
                    WHEN ro.pickup_time LIKE '2020%'
                    THEN STR_TO_DATE(REPLACE(ro.pickup_time,'2020','2021'),
                                     '%Y-%m-%d %H:%i:%s')
                    ELSE STR_TO_DATE(ro.pickup_time,
                                     '%Y-%m-%d %H:%i:%s')
                END
            )
        ),
        2
    ) AS avg_pickup_time_minutes
FROM runner_orders ro
JOIN customer_orders co
    ON ro.order_id = co.order_id
WHERE ro.pickup_time NOT IN ('null', '')
GROUP BY ro.runner_id
ORDER BY ro.runner_id;

-- Question.3 :Is there any relationship between the number of pizzas and how long the order takes to prepare?
SELECT
    co.order_id,
    COUNT(co.pizza_id) AS number_of_pizzas,
    TIMESTAMPDIFF(
        MINUTE,
        MIN(co.order_time),
        CASE
            WHEN ro.pickup_time LIKE '2020%'
            THEN STR_TO_DATE(REPLACE(ro.pickup_time,'2020','2021'),
                             '%Y-%m-%d %H:%i:%s')
            ELSE STR_TO_DATE(ro.pickup_time,
                             '%Y-%m-%d %H:%i:%s')
        END
    ) AS preparation_time
FROM customer_orders co
JOIN runner_orders ro
ON co.order_id = ro.order_id
WHERE ro.pickup_time NOT IN ('null','')
GROUP BY co.order_id, ro.pickup_time
ORDER BY co.order_id;

-- Question.4 :What was the average distance travelled for each customer?
SELECT
    co.customer_id,
    ROUND(
        AVG(
            CAST(
                REPLACE(REPLACE(ro.distance, 'km', ''), ' ', '')
                AS DECIMAL(5,2)
            )
        ),
        2
    ) AS avg_distance
FROM customer_orders co
JOIN runner_orders ro
ON co.order_id = ro.order_id
WHERE ro.distance NOT IN ('null', '')
GROUP BY co.customer_id
ORDER BY co.customer_id;

-- Question.5 :What was the difference between the longest and shortest delivery times for all orders?
SELECT
    MAX(
        CAST(
            REPLACE(
                REPLACE(duration,'minutes',''),
                'mins',''
            ) AS UNSIGNED
        )
    )
    -
    MIN(
        CAST(
            REPLACE(
                REPLACE(duration,'minutes',''),
                'mins',''
            ) AS UNSIGNED
        )
    ) AS difference_in_minutes
FROM runner_orders
WHERE duration NOT IN ('null', '');

-- Question.6 :What was the average speed for each runner for each delivery and do you notice any trend for these values?
SELECT
    runner_id,
    order_id,
    ROUND(
        (
            CAST(REPLACE(REPLACE(distance,'km',''),' ','') AS DECIMAL(5,2))
            /
            (CAST(REPLACE(REPLACE(REPLACE(duration,'minutes',''),'minute',''),'mins','') AS DECIMAL(5,2)) / 60)
        ),
        2
    ) AS avg_speed_km_hr
FROM runner_orders
WHERE distance NOT IN ('null','')
ORDER BY runner_id, order_id;

-- Question.7 :What is the successful delivery percentage for each runner?
 SELECT
    runner_id,
    ROUND(
        SUM(
            CASE
                WHEN cancellation IN ('Restaurant Cancellation', 'Customer Cancellation')
                THEN 0
                ELSE 1
            END
        ) * 100.0 / COUNT(*),
        2
    ) AS successful_delivery_percentage
FROM runner_orders
GROUP BY runner_id
ORDER BY runner_id;

-- C. (Ingredient Optimisation)

-- Question.1 :What are the standard ingredients for each pizza?
SELECT
    pn.pizza_name,
    pr.toppings
FROM pizza_names pn
JOIN pizza_recipes pr
ON pn.pizza_id = pr.pizza_id;

-- Question.2 :What was the most commonly added extra?
SELECT
    extras,
    COUNT(*) AS total
FROM customer_orders
WHERE extras NOT IN ('', 'null', 'NaN')
GROUP BY extras
ORDER BY total DESC
LIMIT 1;

-- Question.3 :What was the most common exclusion?
SELECT
    exclusions,
    COUNT(*) AS total
FROM customer_orders
WHERE exclusions NOT IN ('', 'null', 'NaN')
GROUP BY exclusions
ORDER BY total DESC
LIMIT 1;

/* Question.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 */

SELECT
    co.order_id,
    co.customer_id,
    CASE
        WHEN (co.exclusions IN ('', 'null', 'NaN') OR co.exclusions IS NULL)
         AND (co.extras IN ('', 'null', 'NaN') OR co.extras IS NULL)
        THEN pn.pizza_name

        WHEN (co.exclusions NOT IN ('', 'null', 'NaN') AND co.exclusions IS NOT NULL)
         AND (co.extras IN ('', 'null', 'NaN') OR co.extras IS NULL)
        THEN CONCAT(pn.pizza_name, ' - Exclude ', co.exclusions)

        WHEN (co.exclusions IN ('', 'null', 'NaN') OR co.exclusions IS NULL)
         AND (co.extras NOT IN ('', 'null', 'NaN') AND co.extras IS NOT NULL)
        THEN CONCAT(pn.pizza_name, ' - Extra ', co.extras)

        ELSE CONCAT(
            pn.pizza_name,
            ' - Exclude ', co.exclusions,
            ' - Extra ', co.extras
        )
    END AS order_item
FROM customer_orders co
JOIN pizza_names pn
ON co.pizza_id = pn.pizza_id
ORDER BY co.order_id;

/* Question.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" */
-- increase this if ingredient lists get long
SET SESSION group_concat_max_len = 1000000;

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 20   -- 20 is safely more than max toppings count
),

-- split base recipe toppings into rows
recipe_split AS (
    SELECT
        pr.pizza_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(pr.toppings, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM pizza_recipes pr
    JOIN seq
      ON seq.n <= 1 + LENGTH(pr.toppings) - LENGTH(REPLACE(pr.toppings, ',', ''))
),

-- split exclusions into rows
exclusions_split AS (
    SELECT
        co.order_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(co.exclusions, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM customer_orders co
    JOIN seq
      ON seq.n <= 1 + LENGTH(co.exclusions) - LENGTH(REPLACE(co.exclusions, ',', ''))
    WHERE co.exclusions IS NOT NULL AND co.exclusions <> ''
),

-- split extras into rows
extras_split AS (
    SELECT
        co.order_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(co.extras, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM customer_orders co
    JOIN seq
      ON seq.n <= 1 + LENGTH(co.extras) - LENGTH(REPLACE(co.extras, ',', ''))
    WHERE co.extras IS NOT NULL AND co.extras <> ''
),

-- base toppings per order, after removing exclusions
order_base_toppings AS (
    SELECT co.order_id, rs.topping_id
    FROM customer_orders co
    JOIN recipe_split rs ON rs.pizza_id = co.pizza_id
    WHERE NOT EXISTS (
        SELECT 1 FROM exclusions_split ex
        WHERE ex.order_id = co.order_id AND ex.topping_id = rs.topping_id
    )
),

-- base toppings + extras combined (extras create a duplicate row -> count becomes 2)
order_all_toppings AS (
    SELECT order_id, topping_id FROM order_base_toppings
    UNION ALL
    SELECT order_id, topping_id FROM extras_split
),

-- count how many times each topping shows up per order
topping_counts AS (
    SELECT order_id, topping_id, COUNT(*) AS cnt
    FROM order_all_toppings
    GROUP BY order_id, topping_id
),

-- attach names + build "2x" label where needed
labelled AS (
    SELECT
        tc.order_id,
        pt.topping_name,
        CASE WHEN tc.cnt >= 2 THEN CONCAT('2x', pt.topping_name)
             ELSE pt.topping_name END AS ingredient
    FROM topping_counts tc
    JOIN pizza_toppings pt ON pt.topping_id = tc.topping_id
)

SELECT
    co.order_id,
    pn.pizza_name,
    GROUP_CONCAT(l.ingredient ORDER BY l.topping_name SEPARATOR ', ') AS ingredient_list
FROM labelled l
JOIN customer_orders co ON co.order_id = l.order_id
JOIN pizza_names pn     ON pn.pizza_id = co.pizza_id
GROUP BY co.order_id, pn.pizza_name
ORDER BY co.order_id;

-- Question.6 :What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?
SET SESSION group_concat_max_len = 1000000;

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 20
),

-- only delivered orders (adjust table/column names to your schema)
delivered_orders AS (
    SELECT co.order_id, co.pizza_id, co.exclusions, co.extras
    FROM customer_orders co
    JOIN runner_orders ro ON ro.order_id = co.order_id
    WHERE ro.cancellation IS NULL
       OR ro.cancellation IN ('', 'null')   -- handles messy 'null' strings if present
),

-- split base recipe toppings into rows
recipe_split AS (
    SELECT
        pr.pizza_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(pr.toppings, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM pizza_recipes pr
    JOIN seq
      ON seq.n <= 1 + LENGTH(pr.toppings) - LENGTH(REPLACE(pr.toppings, ',', ''))
),

-- split exclusions into rows (only for delivered orders)
exclusions_split AS (
    SELECT
        d.order_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.exclusions, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM delivered_orders d
    JOIN seq
      ON seq.n <= 1 + LENGTH(d.exclusions) - LENGTH(REPLACE(d.exclusions, ',', ''))
    WHERE d.exclusions IS NOT NULL AND d.exclusions <> ''
),

-- split extras into rows (only for delivered orders)
extras_split AS (
    SELECT
        d.order_id,
        CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.extras, ',', seq.n), ',', -1)) AS UNSIGNED) AS topping_id
    FROM delivered_orders d
    JOIN seq
      ON seq.n <= 1 + LENGTH(d.extras) - LENGTH(REPLACE(d.extras, ',', ''))
    WHERE d.extras IS NOT NULL AND d.extras <> ''
),

-- base toppings per delivered order, after removing exclusions
order_base_toppings AS (
    SELECT d.order_id, rs.topping_id
    FROM delivered_orders d
    JOIN recipe_split rs ON rs.pizza_id = d.pizza_id
    WHERE NOT EXISTS (
        SELECT 1 FROM exclusions_split ex
        WHERE ex.order_id = d.order_id AND ex.topping_id = rs.topping_id
    )
),

-- base toppings + extras combined
order_all_toppings AS (
    SELECT order_id, topping_id FROM order_base_toppings
    UNION ALL
    SELECT order_id, topping_id FROM extras_split
)

-- final: total quantity per ingredient across all delivered pizzas
SELECT
    pt.topping_name AS ingredient,
    COUNT(*) AS total_quantity
FROM order_all_toppings oat
JOIN pizza_toppings pt ON pt.topping_id = oat.topping_id
GROUP BY pt.topping_name
ORDER BY total_quantity DESC;

-- D. (Pricing and Ratings)

-- Question.1 :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?

SELECT
    SUM(
        CASE
            WHEN pn.pizza_name = 'Meat Lovers' THEN 12
            WHEN pn.pizza_name = 'Vegetarian' THEN 10
        END
    ) AS total_revenue
FROM customer_orders co
JOIN pizza_names pn
ON co.pizza_id = pn.pizza_id
JOIN runner_orders ro
ON co.order_id = ro.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation');

-- Question.2 :What if there was an additional $1 charge for any pizza extras?
-- Add cheese is $1 extra

SELECT
    SUM(
        CASE
            WHEN pn.pizza_name = 'Meat Lovers' THEN 12
            WHEN pn.pizza_name = 'Vegetarian' THEN 10
        END
        +
        CASE
            WHEN co.extras IN ('', 'null', 'NaN') OR co.extras IS NULL THEN 0
            ELSE LENGTH(co.extras) - LENGTH(REPLACE(co.extras, ',', '')) + 1
        END
    ) AS total_revenue_with_extras
FROM customer_orders co
JOIN pizza_names pn
    ON co.pizza_id = pn.pizza_id
JOIN runner_orders ro
    ON co.order_id = ro.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation', 'Customer Cancellation');

-- Question.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 TABLE ratings (
    order_id INT,
    rating INT
);

INSERT INTO ratings (order_id, rating)
VALUES
(1,5),
(2,4),
(3,5),
(4,3),
(5,4),
(7,5),
(8,4),
(10,5);

/* Question.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_id
order_id
runner_id
rating
order_time
pickup_time
Time between order and pickup
Delivery duration
Average speed
Total number of pizzas */

SELECT
    co.customer_id,
    ro.order_id,
    ro.runner_id,
    r.rating,
    MIN(co.order_time) AS order_time,
    ro.pickup_time,
    TIMESTAMPDIFF(
        MINUTE,
        MIN(co.order_time),
        CASE
            WHEN ro.pickup_time LIKE '2020%'
            THEN STR_TO_DATE(REPLACE(ro.pickup_time,'2020','2021'),
                             '%Y-%m-%d %H:%i:%s')
            ELSE STR_TO_DATE(ro.pickup_time,
                             '%Y-%m-%d %H:%i:%s')
        END
    ) AS pickup_minutes,
    ro.duration,
    ROUND(
        CAST(REPLACE(REPLACE(ro.distance,'km',''),' ','') AS DECIMAL(5,2))
        /
        (CAST(REPLACE(REPLACE(REPLACE(ro.duration,'minutes',''),'minute',''),'mins','') AS DECIMAL(5,2))/60),
        2
    ) AS avg_speed_km_hr,
    COUNT(co.pizza_id) AS total_pizzas
FROM customer_orders co
JOIN runner_orders ro
    ON co.order_id = ro.order_id
JOIN ratings r
    ON ro.order_id = r.order_id
WHERE ro.cancellation NOT IN ('Restaurant Cancellation','Customer Cancellation')
GROUP BY
    co.customer_id,
    ro.order_id,
    ro.runner_id,
    r.rating,
    ro.pickup_time,
    ro.duration,
    ro.distance
ORDER BY ro.order_id;

-- Question.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?

SELECT
    ROUND(
        (
            SELECT SUM(
                CASE
                    WHEN pn.pizza_name = 'Meat Lovers' THEN 12
                    ELSE 10
                END
            )
            FROM customer_orders co
            JOIN pizza_names pn
              ON co.pizza_id = pn.pizza_id
            JOIN runner_orders ro
              ON co.order_id = ro.order_id
            WHERE ro.cancellation NOT IN ('Restaurant Cancellation','Customer Cancellation')
        )
        -
        (
            SELECT SUM(
                CAST(REPLACE(REPLACE(distance,'km',''),' ','') AS DECIMAL(5,2)) * 0.30
            )
            FROM runner_orders
            WHERE cancellation NOT IN ('Restaurant Cancellation','Customer Cancellation')
              AND distance NOT IN ('null','')
        ),
        2
    ) AS profit;
    
    
   --  E. (Bonus Questions)
   
-- If Danny wants to expand his range of pizzas - how would this impact the existing data design? Write an INSERT statement to demonstrate what would happen if a new Supreme pizza with all the toppings was added to the Pizza Runner menu?

INSERT INTO pizza_names (pizza_id, pizza_name)
VALUES
(3, 'Supreme');

INSERT INTO pizza_recipes (pizza_id, toppings)
VALUES
(3, '1,2,3,4,5,6,7,8,9,10,11,12');

SELECT *
FROM pizza_names;

SELECT *
FROM pizza_recipes;