Case Study #2 - Pizza Runner

Please note that the case study information provided below has been sourced from the following link: https://8weeksqlchallenge.com/case-study-2/

Table of Contents

Case Study Introduction Problem Statement Database Schema Question and Solution Database Creation & Table Populating Questions Sets A. Pizza Metrics B. Runner and Customer Experience C. Ingredient Optimisation D. Pricing and Ratings E. Bonus Questions 2 Cents

Case Study Introduction

Danny spots a trend on Instagram: "80s retro styling and pizza is the future!" Sold on the idea, he decides pizza alone won't be enough to land seed funding, so he adds his own twist, Uberizing it, and Pizza Runner is born.

Pizza Runner needs your help to run the numbers. Danny has recruited runners and built an app to take orders, but now he needs to make sense of the data to keep his pizza empire growing.

Problem Statement

Danny wants to make sense of his Pizza Runner data to understand how his delivery operations are actually performing: order patterns, runner efficiency, and how well pizzas are being customized and delivered. This will help him optimize runner allocation and improve overall operations as Pizza Runner scales.

To make this process smoother, he needs some basic datasets and SQL queries to analyze the data, but the exclusions, extras, and other fields need cleaning before they're usable. Danny has provided the raw operational data and expects these examples to be enough to craft effective queries.

You have six key datasets to work with:

runners
customer_orders
runner_orders
pizza_names
pizza_recipes
pizza_toppings

Database Schema

(created using ChatGPT x Info on original site)

Question and Solution

I am going to use DuckDB SQL Workbench to solve these queries.

Database Creation & Table Populating


CREATE SCHEMA IF NOT EXISTS pizza_runner;
DROP TABLE IF EXISTS runners;

-- runners
USE pizza_runner;
CREATE TABLE pizza_runner.runners (
    runner_id INTEGER,
    registration_date DATE
);
insert into pizza_runner.runners
    (runner_id, registration_date)
values
    (1, '2021-01-01'),
    (2, '2021-01-03'),
    (3, '2021-01-08'),
    (4, '2021-01-15');

-- customer_orders
drop table if exists pizza_runner.customer_orders;

create table pizza_runner.customer_orders (
    order_id integer,
    customer_id integer,
    pizza_id integer,
    exclusions varchar(4),
    extras varchar(4),
    order_time timestamp
);

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

-- runner_orders

drop table if exists pizza_runner.runner_orders;

create table pizza_runner.runner_orders (
    order_id integer,
    runner_id integer,
    pickup_time varchar(19),
    distance varchar(7),
    duration varchar(10),
    cancellation varchar(23)
);

insert into pizza_runner.runner_orders
    (order_id, runner_id, pickup_time, distance, duration, cancellation)
values
    (1, 1, '2020-01-01 18:15:34', '20km', '32 minutes', ''),
    (2, 1, '2020-01-01 19:10:54', '20km', '27 minutes', ''),
    (3, 1, '2020-01-03 00:12:37', '13.4km', '20 mins', null),
    (4, 2, '2020-01-04 13:53:03', '23.4', '40', null),
    (5, 3, '2020-01-08 21:10:57', '10', '15', null),
    (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');

-- pizza_names

drop table if exists pizza_runner.pizza_names;

create table pizza_runner.pizza_names (
    pizza_id integer,
    pizza_name text
);

insert into pizza_runner.pizza_names
    (pizza_id, pizza_name)
values
    (1, 'Meatlovers'),
    (2, 'Vegetarian');

-- pizza_recipes

drop table if exists pizza_runner.pizza_recipes;

create table pizza_runner.pizza_recipes (
    pizza_id integer,
    toppings text
);

insert into pizza_runner.pizza_recipes
    (pizza_id, toppings)
values
    (1, '1, 2, 3, 4, 5, 6, 8, 10'),
    (2, '4, 6, 7, 9, 11, 12');

-- pizza_toppings

drop table if exists pizza_runner.pizza_toppings;

create table pizza_runner.pizza_toppings (
    topping_id integer,
    topping_name text
);

insert into pizza_runner.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');

-- check tables

select * from pizza_runner.runners;
select * from pizza_runner.customer_orders;
select * from pizza_runner.runner_orders;
select * from pizza_runner.pizza_names;
select * from pizza_runner.pizza_recipes;
select * from pizza_runner.pizza_toppings;

Questions Sets

A. Pizza Metrics

#1 How many pizzas were ordered?


SELECT COUNT(*) AS pizza_ordered
FROM pizza_runner.customer_orders;

#2 How many unique customer orders were made?


SELECT COUNT(distinct order_id) AS unique_order_cnt
FROM pizza_runner.customer_orders;

#3 How many successful orders were delivered by each runner?


select runner_id, count(order_id) as successful_order
from pizza_runner.runner_orders
where distance != 'null'
group by runner_id

#4 How many of each type of pizza was delivered?


select pizza_id, count(order_id) as pizza_delivered
from pizza_runner.customer_orders
where order_id in 
  ( select order_id from 
    pizza_runner.runner_orders 
    where distance != 'null' group by order_id
  )  
group by pizza_id

#5 How many Vegetarian and Meatlovers were ordered by each customer?


select
    co.customer_id,
    sum(case when pn.pizza_name = 'Vegetarian' then 1 else 0 end) as vegetarian,
    sum(case when pn.pizza_name = 'Meatlovers' then 1 else 0 end) as meatlovers
from pizza_runner.customer_orders co
left join pizza_runner.pizza_names pn
    on co.pizza_id = pn.pizza_id
group by co.customer_id
order by co.customer_id;

#6 Which customer placed the order containing the maximum number of pizzas, and how many pizzas were included in that order? [Original: What was the maximum number of pizzas delivered in a single order?]


select
    order_id,
    customer_id,
    count(*) as pizza_count
from pizza_runner.customer_orders
group by order_id, customer_id
having count(*) = (
    select max(pizza_count)
    from (
        select
            order_id,
            count(*) as pizza_count
        from pizza_runner.customer_orders
        group by order_id
    ) x
)
order by order_id;

#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')
              or co.extras is not null
                 and co.extras not in ('', 'null')
            then 1
            else 0
        end
    ) as at_least_1_change,
    sum(
        case
            when (co.exclusions is null or co.exclusions in ('', 'null'))
             and (co.extras is null or co.extras in ('', 'null'))
            then 1
            else 0
        end
    ) as no_changes
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by co.customer_id
order by co.customer_id;

#8 How many pizzas were delivered that had both exclusions and extras?


select count(*) as pizzas_with_both_changes
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where (ro.cancellation is null or ro.cancellation in ('', 'null'))
  and co.exclusions not in ('', 'null')
  and co.extras not in ('', 'null');

#9 What was the total volume of pizzas ordered for each hour of the day?


select extract(hour from order_time) as order_hour, count(*) as pizza_count
from pizza_runner.customer_orders
group by order_hour
order by order_hour;

#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 order_count
from pizza_runner.customer_orders
group by dayofweek(order_time), dayname(order_time)
order by dayofweek(order_time);

B. Runner and Customer Experience

#1 How many runners signed up for each 1 week period? (i.e. week starts 2021-01-01)


select
    floor(date_diff('day', date '2021-01-01', registration_date) / 7) + 1 as week_number,
    count(*) as runner_count
from pizza_runner.runners
group by week_number
order by week_number;

#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(
            datediff(
                'minute',
                co.order_time,
                cast(ro.pickup_time as timestamp)
            )
        ), 2
    ) as avg_pickup_time
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by ro.runner_id
order by ro.runner_id;

#3 Is there any relationship between the number of pizzas and how long the order takes to prepare?


select
    count(*) as pizza_count,
    round(
        avg(
            datediff(
                'minute',
                co.order_time,
                cast(ro.pickup_time as timestamp)
            )
        ), 2
    ) as avg_preparation_time
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by co.order_id
order by pizza_count;

#4 What was the average distance travelled for each customer?


select
    co.customer_id,
    round(
        avg(
            cast(
                regexp_replace(ro.distance, 'km', '', 'gi') as double
            )
        ), 2
    ) as avg_distance
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by co.customer_id
order by co.customer_id;

#5 What was the difference between the longest and shortest delivery times for all orders?


select
    max(cast(regexp_extract(ro.duration, '[0-9]+') as integer))
    - min(cast(regexp_extract(ro.duration, '[0-9]+') as integer))
    as delivery_time_difference
from pizza_runner.runner_orders ro
where ro.cancellation is null
   or ro.cancellation in ('', 'null');

#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(regexp_extract(distance, '[0-9.]+') as double)
        / (cast(regexp_extract(duration, '[0-9]+') as double) / 60),
        2
    ) as avg_speed_kmh
from pizza_runner.runner_orders
where cancellation is null
   or cancellation in ('', 'null')
order by runner_id, order_id;

#7 What is the successful delivery percentage for each runner?


select
    runner_id,
    round(
        100.0 * sum(
            case
                when cancellation is null or cancellation in ('', 'null')
                then 1
                else 0
            end
        ) / count(*),
        2
    ) as successful_delivery_percentage
from pizza_runner.runner_orders
group by runner_id
order by runner_id;

C. Ingredient Optimisation

#1 What are the standard ingredients for each pizza?


select
    pn.pizza_name,
    string_agg(pt.topping_name, ', ' order by pt.topping_name) as ingredients
from pizza_runner.pizza_recipes pr
join pizza_runner.pizza_names pn
    on pr.pizza_id = pn.pizza_id
cross join unnest(string_split(pr.toppings, ', ')) as t(topping_id)
join pizza_runner.pizza_toppings pt
    on cast(t.topping_id as integer) = pt.topping_id
group by pn.pizza_name
order by pn.pizza_name;

#2 What was the most commonly added extra?


select
    pt.topping_name,
    count(*) as extra_count
from pizza_runner.customer_orders co
cross join unnest(string_split(co.extras, ', ')) as e(topping_id)
join pizza_runner.pizza_toppings pt
    on cast(e.topping_id as integer) = pt.topping_id
where co.extras is not null
  and co.extras not in ('', 'null')
group by pt.topping_name
order by extra_count desc
limit 1;

#3 What was the most common exclusion?


select
    pt.topping_name,
    count(*) as exclusion_count
from pizza_runner.customer_orders co
cross join unnest(string_split(co.exclusions, ', ')) as e(topping_id)
join pizza_runner.pizza_toppings pt
    on cast(e.topping_id as integer) = pt.topping_id
where co.exclusions is not null
  and co.exclusions not in ('', 'null')
group by pt.topping_name
order by exclusion_count desc
limit 1;

#4 Generate an order item for each record


with exclusions as (
    select
        co.order_id,
        co.pizza_id,
        string_agg(pt.topping_name, ', ' order by pt.topping_name) as exclusion_names
    from pizza_runner.customer_orders co
    cross join unnest(string_split(co.exclusions, ', ')) as e(topping_id)
    join pizza_runner.pizza_toppings pt
        on cast(e.topping_id as integer) = pt.topping_id
    where co.exclusions is not null
      and co.exclusions not in ('', 'null')
    group by co.order_id, co.pizza_id
),

extras as (
    select
        co.order_id,
        co.pizza_id,
        string_agg(pt.topping_name, ', ' order by pt.topping_name) as extra_names
    from pizza_runner.customer_orders co
    cross join unnest(string_split(co.extras, ', ')) as e(topping_id)
    join pizza_runner.pizza_toppings pt
        on cast(e.topping_id as integer) = pt.topping_id
    where co.extras is not null
      and co.extras not in ('', 'null')
    group by co.order_id, co.pizza_id
)

select
    pn.pizza_name
    || case
        when e.exclusion_names is not null
        then ' - Exclude ' || e.exclusion_names
        else ''
    end
    || case
        when x.extra_names is not null
        then ' - Extra ' || x.extra_names
        else ''
    end as order_item
from pizza_runner.customer_orders co
join pizza_runner.pizza_names pn
    on co.pizza_id = pn.pizza_id
left join exclusions e
    on co.order_id = e.order_id
   and co.pizza_id = e.pizza_id
left join extras x
    on co.order_id = x.order_id
   and co.pizza_id = x.pizza_id;

order_item
Meatlovers - Exclude Cheese - Extra Bacon, Chicken
Meatlovers - Exclude BBQ Sauce, Mushrooms - Extra Bacon, Cheese
Meatlovers - Exclude BBQ Sauce, Mushrooms - Extra Bacon, Cheese
Meatlovers - Extra Bacon
Vegetarian - Extra Bacon
Meatlovers - Exclude Cheese, Cheese
Meatlovers - Exclude Cheese, Cheese
Vegetarian - Exclude Cheese
Meatlovers
Meatlovers
Meatlovers
Vegetarian
Vegetarian
Meatlovers

#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


with order_ingredients as (
    select
        co.order_id,
        co.pizza_id,
        unnest(string_split(pr.toppings, ', ')) as topping_id
    from pizza_runner.customer_orders co
    join pizza_runner.pizza_recipes pr
        on co.pizza_id = pr.pizza_id

    union all

    select
        co.order_id,
        co.pizza_id,
        unnest(string_split(co.extras, ', ')) as topping_id
    from pizza_runner.customer_orders co
    where co.extras is not null
      and co.extras not in ('', 'null')
),

final_ingredients as (
    select
        oi.order_id,
        oi.pizza_id,
        pt.topping_name,
        count(*) as quantity
    from order_ingredients oi
    join pizza_runner.pizza_toppings pt
        on cast(oi.topping_id as integer) = pt.topping_id
    where not exists (
        select 1
        from pizza_runner.customer_orders co
        where co.order_id = oi.order_id
          and co.pizza_id = oi.pizza_id
          and co.exclusions is not null
          and co.exclusions not in ('', 'null')
          and ',' || replace(co.exclusions, ' ', '') || ','
              like '%,' || oi.topping_id || ',%'
    )
    group by oi.order_id, oi.pizza_id, pt.topping_name
)

select
    fi.order_id,
    pn.pizza_name || ': ' ||
    string_agg(
        case
            when fi.quantity > 1
            then fi.quantity || 'x' || fi.topping_name
            else fi.topping_name
        end,
        ', ' order by fi.topping_name
    ) as ingredient_list
from final_ingredients fi
join pizza_runner.pizza_names pn
    on fi.pizza_id = pn.pizza_id
group by fi.order_id, fi.pizza_id, pn.pizza_name
order by fi.order_id;

#6 What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?


with delivered_orders as (
    select
        co.order_id,
        co.pizza_id,
        co.exclusions,
        co.extras
    from pizza_runner.customer_orders co
    join pizza_runner.runner_orders ro
        on co.order_id = ro.order_id
    where ro.cancellation is null
       or ro.cancellation in ('', 'null')
),

standard_ingredients as (
    select
        d.order_id,
        d.pizza_id,
        unnest(string_split(pr.toppings, ', ')) as topping_id
    from delivered_orders d
    join pizza_runner.pizza_recipes pr
        on d.pizza_id = pr.pizza_id
),

extras as (
    select
        d.order_id,
        d.pizza_id,
        unnest(string_split(d.extras, ', ')) as topping_id
    from delivered_orders d
    where d.extras is not null
      and d.extras not in ('', 'null')
),

all_ingredients as (
    select * from standard_ingredients
    union all
    select * from extras
)

select
    pt.topping_name,
    count(*) as total_quantity
from all_ingredients ai
join pizza_runner.pizza_toppings pt
    on cast(ai.topping_id as integer) = pt.topping_id
where not exists (
    select 1
    from delivered_orders d
    where d.order_id = ai.order_id
      and d.pizza_id = ai.pizza_id
      and d.exclusions is not null
      and d.exclusions not in ('', 'null')
      and ',' || replace(d.exclusions, ' ', '') || ','
          like '%,' || ai.topping_id || ',%'
)
group by pt.topping_name
order by total_quantity desc;

D. Pricing and Ratings

#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 
    coalesce(pn.pizza_name, 'Total') as pizza_name,
    sum(
        case
            when pn.pizza_name = 'Meatlovers' then 12
            when pn.pizza_name = 'Vegetarian' then 10
        end
    ) as total_revenue
from pizza_runner.customer_orders co
join pizza_runner.pizza_names pn
    on co.pizza_id = pn.pizza_id
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by rollup(pn.pizza_name)
order by
    case when pn.pizza_name is null then 2 else 1 end,
    pn.pizza_name;

#2 What if there was an additional $1 charge for any pizza extras? [Add cheese is $1 extra]


select
  coalesce(pn.pizza_name, 'Total') as pizza_name,  
  sum(
        case
            when pn.pizza_name = 'Meatlovers' then 12
            when pn.pizza_name = 'Vegetarian' then 10
        end
    )
    +
    sum(
        case
            when co.extras is null or co.extras in ('', 'null') then 0
            else length(co.extras) - length(replace(co.extras, ',', '')) + 1
        end
    ) as total_revenue
from pizza_runner.customer_orders co
join pizza_runner.pizza_names pn
    on co.pizza_id = pn.pizza_id
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by rollup(pn.pizza_name)
order by
    case when pn.pizza_name is null then 2 else 1 end,
    pn.pizza_name;

#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.

Approach

Since every successful order gets exactly one runner rating, order_id works well as the primary key here, no need for a separate rating ID.

Check it from here:


drop table if exists runner_ratings;

create table pizza_runner.runner_ratings (
    order_id integer primary key,
    rating integer,
    rating_time timestamp
);
insert into pizza_runner.runner_ratings
    (order_id, rating, rating_time)
values
    (1, 5, '2020-01-01 19:00:00'),
    (2, 4, '2020-01-01 20:00:00'),
    (3, 5, '2020-01-03 01:00:00'),
    (4, 3, '2020-01-04 14:30:00'),
    (5, 4, '2020-01-08 21:45:00'),
    (7, 5, '2020-01-08 22:00:00'),
    (8, 4, '2020-01-10 01:00:00'),
    (10, 5, '2020-01-11 19:30:00');

select *
from pizza_runner.runner_ratings
order by order_id;

#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,
    co.order_id,
    ro.runner_id,
    rr.rating,
    min(co.order_time) as order_time,
    cast(ro.pickup_time as timestamp) as pickup_time,
    datediff(
        'minute',
        min(co.order_time),
        cast(ro.pickup_time as timestamp)
    ) as time_to_pickup,
    cast(regexp_extract(ro.duration, '[0-9]+') as integer) as delivery_duration,
    round(
        cast(regexp_extract(ro.distance, '[0-9.]+') as double)
        /
        (cast(regexp_extract(ro.duration, '[0-9]+') as double) / 60),
        2
    ) as average_speed,
    count(*) as total_pizzas
from pizza_runner.customer_orders co
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
join pizza_runner.runner_ratings rr
    on co.order_id = rr.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by
    co.customer_id,
    co.order_id,
    ro.runner_id,
    rr.rating,
    ro.pickup_time,
    ro.duration,
    ro.distance
order by co.order_id;

#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
  coalesce(pn.pizza_name, 'Total') as pizza_name,  
    sum(
        case
            when pn.pizza_name = 'Meatlovers' then 12
            when pn.pizza_name = 'Vegetarian' then 10
        end
    ) as total_revenue,
    sum(
        cast(regexp_extract(ro.distance, '[0-9.]+') as double)
    ) as total_distance,
    round(
        sum(
            cast(regexp_extract(ro.distance, '[0-9.]+') as double)
        ) * 0.30,
        2
    ) as runner_payment,
    round(
        sum(
            case
                when pn.pizza_name = 'Meatlovers' then 12
                when pn.pizza_name = 'Vegetarian' then 10
            end
        )
        -
        sum(
            cast(regexp_extract(ro.distance, '[0-9.]+') as double)
        ) * 0.30,
        2
    ) as money_left
from pizza_runner.customer_orders co
join pizza_runner.pizza_names pn
    on co.pizza_id = pn.pizza_id
join pizza_runner.runner_orders ro
    on co.order_id = ro.order_id
where ro.cancellation is null
   or ro.cancellation in ('', 'null')
group by rollup(pn.pizza_name)
order by
    case when pn.pizza_name is null then 2 else 1 end,
    pn.pizza_name;

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?

Approach

Danny wants to expand the menu, first instinct was to assume this breaks something. Turns out it doesn't, the design was already set up well enough that adding a pizza just means adding rows, nothing structural to touch.

pizza_names holds the name, pizza_recipes holds the toppings tied to it, pizza_toppings is just the master list. So Supreme goes in cleanly.


insert into pizza_runner.pizza_names
    (pizza_id, pizza_name)
values
    (3, 'Supreme');

insert into pizza_runner.pizza_recipes
    (pizza_id, toppings)
values
    (3, '1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12');

That's really it. No new column, no restructuring, just two inserts and the menu's bigger.

If I'm being honest though, the toppings column bugs me a little, it's a comma-separated string doing a job a proper junction table should be doing. Works fine for now, but I'd fix that eventually if this were a real system, not just a case study.

2 Cents

Started this thinking it was just another SQL challenge, the kind you do once and forget. Took me over a week in the end, way longer than I expected going in, mostly because there was as much unlearning happening as there was learning.

First thing that hit me: NULL, 'null', and empty strings are apparently three different things depending on how someone typed the data in. A simple where cancellation is null quietly missed half the cancelled orders, and I didn't catch it right away either, which was a little humbling. Learned pretty fast that understanding how data is actually stored matters more than knowing the right SQL syntax. You can write a technically perfect query and still get a business answer that's just wrong.

Also had to unlearn something obvious-sounding: an order and a pizza aren't the same row. Three pizzas in one order is one order, three pizza-level records. Sounds trivial until you're calculating revenue or prep time and get the wrong number because you mixed up the two. Spent longer than I'd like to admit double-checking this before trusting my own aggregations.

The delivery side turned into its own rabbit hole. Distance, duration, speed, success rate, and averages hid more than they revealed. A couple of runners were consistently fast, a couple weren't, and just looking at the mean would've flattened that story completely into something meaningless. That's probably the moment I stopped thinking of this as a SQL exercise and started thinking of it as an operations problem with SQL as the tool.

Exclusions and extras were the messiest part by far, comma-separated values crammed into a single field, 1, 4 meaning nothing without context, and needing string functions just to make sense of what should've been a simple relationship. Worked around it for the exercise, split, parsed, joined back against the toppings table, but if this were a real system, I'd want a proper junction table between pizzas and toppings instead. Would've saved a lot of regex headaches, and probably a few of the days I lost here.

The revenue piece was honestly the most satisfying part of the whole thing. Fixed prices, extras charged separately, runner payments subtracted, and suddenly $138 in pizza revenue became $142 with extras, then dropped to roughly $94 after delivery costs. Small numbers on paper, but it's the exact same logic that scales up to real business decisions, SQL answering not just "what happened" but "what does this actually mean financially." That distinction felt like the real unlock.

Last question was about adding a new pizza, Supreme, loaded with every topping. Turned out the existing design handled that without needing a single new column, which is honestly the sign of a well-thought-out schema, something I didn't fully appreciate until I tried to break it and couldn't. Still think the toppings-as-string thing should be a junction table eventually, minor gripe against an otherwise solid structure, but a gripe nonetheless.

If there's one real takeaway, it's this:

The SQL itself was never really the hard part. Figuring out what one row actually represents, before writing a single query, is what made everything else click. Business question, data grain, joins, filters, aggregation, insight, in that order, every time. Took a full week to get there, mostly unlearning bad habits and assumptions I didn't realize I had. But somewhere in the middle of all the joins and CTEs, this stopped feeling like a SQL challenge and started feeling like actually understanding how a small pizza delivery business runs, order to plate to doorstep to review.