Danny Ma’s SQL Case Study #2 — Pizza Runner

SQL Challenge Link & Dataset

Case Study #2 - Pizza Runner

Introduction

Did you know that over 115 million kilograms of pizza is consumed daily worldwide??? (Well according to Wikipedia anyway…)

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.

Entity Relationship Diagram

Case Study Questions and Solutions Link

MySQL has been used for solving the below solutions.

A. Pizza Metrics

1. How many pizzas were ordered?

SELECT
  COUNT(pizza_id) AS total_pizza_ordered
FROM customer_orders_new;

2. How many unique customer orders were made?

SELECT
  COUNT(DISTINCT order_id) AS unique_customer_orders
FROM customer_orders_new;

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

SELECT
  runner_id,
  COUNT(order_id) AS orders_delivered
FROM runner_orders_new
WHERE pickup_time IS NOT NULL
GROUP BY runner_id;

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

SELECT
  pname.pizza_name,
  COUNT(pname.pizza_id) AS no_of_pizzas
FROM runner_orders_new run
LEFT JOIN customer_orders_new cust
  ON run.order_id = cust.order_id
LEFT JOIN pizza_names pname
  ON cust.pizza_id = pname.pizza_id
WHERE run.pickup_time IS NOT NULL
GROUP BY pname.pizza_id;

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

SELECT
  cust.customer_id,
  pname.pizza_name,
  COUNT(order_id) AS no_of_pizzas
FROM customer_orders_new cust
LEFT JOIN pizza_names pname
  ON cust.pizza_id = pname.pizza_id
GROUP BY cust.customer_id,
         pname.pizza_id
ORDER BY cust.customer_id;

6. What was the maximum number of pizzas delivered in a single order?

WITH max_no_of_pizza
AS (SELECT
  run.order_id,
  COUNT(cust.pizza_id) AS total_pizzas_delivered,
  DENSE_RANK() OVER (ORDER BY COUNT(cust.pizza_id) DESC) AS rnk
FROM runner_orders_new run
LEFT JOIN customer_orders_new cust
  ON run.order_id = cust.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY run.order_id)
SELECT
  order_id,
  MAX(total_pizzas_delivered) AS max_no_of_pizza_delivered
FROM max_no_of_pizza
WHERE rnk = 1;

7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?

SELECT
  cust.customer_id,
  SUM(CASE
    WHEN (cust.exclusions IS NOT NULL AND
      cust.exclusions <> '') OR
      (cust.extras IS NOT NULL AND
      cust.extras <> '') THEN 1
    ELSE 0
  END) AS atleast_one_change,
  SUM(CASE
    WHEN (cust.exclusions IS NULL OR
      cust.exclusions = '') AND
      (cust.extras IS NULL OR
      cust.extras = '') THEN 1
    ELSE 0
  END) AS no_change
FROM runner_orders_new run
LEFT JOIN customer_orders_new cust
  ON run.order_id = cust.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY cust.customer_id
ORDER BY cust.customer_id;

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

SELECT
  cust.order_id,
  cust.customer_id,
  SUM(CASE
    WHEN ((cust.exclusions IS NOT NULL AND
      cust.exclusions <> '') AND
      (cust.extras IS NOT NULL AND
      cust.extras <> '')) THEN 1
    ELSE 0
  END) AS 'exclusions + extras'
FROM runner_orders_new run
LEFT JOIN customer_orders_new cust
  ON run.order_id = cust.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY cust.order_id
ORDER BY 'exclusions + extras' DESC;

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

SELECT
  HOUR(order_time) AS 'Hour',
  COUNT(order_id) AS total_volume_of_pizza
FROM customer_orders_new
GROUP BY HOUR(order_time)
ORDER BY HOUR(order_time);

10. What was the volume of orders for each day of the week?

SELECT
  DAYNAME(order_time) AS 'Day',
  COUNT(order_id) AS volumn_of_orders
FROM customer_orders_new
GROUP BY DAYNAME(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
  week(registration_date) AS weekday,
  COUNT(runner_id) AS no_of_runners_registered
FROM runners
GROUP BY week(registration_date);

2. What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?

WITH avg_time
AS (SELECT
 run.runner_id,
 cust.order_id,
 cust.order_time,
 run.pickup_time,
 TIMESTAMPDIFF(minute, cust.order_time, run.pickup_time) AS minutes_to_pickup
FROM customer_orders_new cust
LEFT JOIN runner_orders_new run
 ON cust.order_id = run.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY cust.order_id)
SELECT
 runner_id,
 ROUND(AVG(minutes_to_pickup), 2)
FROM avg_time
GROUP BY runner_id;

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

WITH avg_time
AS (SELECT
  run.runner_id,
  cust.order_id,
  COUNT(cust.pizza_id) AS no_of_pizza,
  cust.order_time,
  run.pickup_time,
  TIMESTAMPDIFF(minute, cust.order_time, run.pickup_time) AS minutes_to_pickup
FROM customer_orders_new cust
LEFT JOIN runner_orders_new run
  ON cust.order_id = run.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY cust.order_id)
SELECT
  no_of_pizza,
  ROUND(AVG(minutes_to_pickup), 2)
FROM avg_time
GROUP BY no_of_pizza;

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

SELECT
  cust.customer_id,
  ROUND(AVG(run.distance), 2)
FROM runner_orders_new run
LEFT JOIN customer_orders_new cust
  ON run.order_id = cust.order_id
WHERE run.pickup_time IS NOT NULL
GROUP BY cust.customer_id
ORDER BY cust.customer_id;

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

WITH time_diff
AS (SELECT
  order_id,
  duration
FROM runner_orders_new
WHERE pickup_time IS NOT NULL
GROUP BY order_id)
SELECT
  MAX(duration) - MIN(duration) AS delivery_time_diff
FROM time_diff;

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(AVG(distance / (duration / 60)), 2) AS 'avg_speed_in_km/hr'
FROM runner_orders_new
WHERE pickup_time IS NOT NULL
GROUP BY runner_id,
         order_id;

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

WITH delivery
AS (SELECT
  runner_id,
  order_id,
  cancellation,
  CASE
    WHEN (cancellation IS NULL OR
      cancellation = '') THEN 0
    ELSE 1
  END AS flag
FROM runner_orders_new)
SELECT
  runner_id,
  COUNT(flag) AS total_delivery,
  COUNT(CASE
    WHEN flag = 1 THEN 1
  END) AS cancelled,
  COUNT(CASE
    WHEN flag = 0 THEN 0
  END) AS successfully_delivered,
  ROUND(COUNT(CASE
    WHEN flag = 0 THEN 0
  END) / COUNT(flag) * 100, 0) AS 'successful_delivery_%'
FROM delivery
GROUP BY runner_id;

GitHub Link (All Solutions: C,D)

Please refer the below GitHub link. There I have uploaded all the questions and solutions.

SQL-Projects/Pizza Runner at main · ritusantra/SQL-Projects

Happy Learning! Cheers!

If you find my articles helpful, don’t forget to tap the clap button to show your support and save the articles for future reference.