Pizza Runner SQL Case Study

SQL case study covering data cleaning, order metrics, runner logistics, ingredient analysis, and revenue modeling using MySQL — based on Case Study #2 of the 8 Week SQL Challenge.

Overview

Danny is expanding his restaurant into delivery — "Pizza Runner" — recruiting independent runners to deliver fresh Meatlovers and Vegetarian pizzas from his HQ. The raw data his app collected is genuinely messy: inconsistent NULL representations, mixed units in the same column, and comma-separated lists buried inside text fields. This project cleans that data from scratch and then uses it to answer 29 business questions across four areas: pizza ordering metrics, runner and customer experience, ingredient optimization, and pricing/profitability.

Unlike a typical "tidy CSV" exercise, a meaningful chunk of this project is the data cleaning layer itself — string parsing, type casting, and recursive CTEs to explode comma-separated values into proper rows — before any business question can be answered correctly.

Dataset

Six tables make up the pizza_runner schema.

Table	Description
runners	Registration date for each delivery runner
customer_orders	One row per pizza ordered, with topping exclusions/extras as raw comma-separated text
runner_orders	Pickup time, distance, duration, and cancellation status per order
pizza_names	Maps pizza IDs to names (Meatlovers, Vegetarian)
pizza_recipes	Standard topping list for each pizza, as comma-separated text
pizza_toppings	Maps topping IDs to names

Known data quality issues, handled in the cleaning layer: exclusions/extras use three different "no value" markers (empty string, the literal text 'null', and true NULL); distance mixes formats like '20km', '23.4 km', and plain '10'; duration mixes '32 minutes', '20 mins', '15 minute', and plain '40'. All of this is normalized before any question is answered.

Repository Structure

pizza-runner-sql-case-study/
├── README.md
├── sql/
│   ├── pizza_runner_setup.sql        # Schema, raw data, and the full cleaning layer
│   └── pizza_runner_solutions.sql    # All 29 case study queries, commented
├── screenshots/                      # Query result screenshots
└── assets/                           # Supporting images / diagrams

Case Study Questions

A. Pizza Metrics (10 questions) — order volume, delivery counts by pizza type and customer, busiest hours/days, and how often customers customize their order.

B. Runner and Customer Experience (7 questions) — runner signup cadence, pickup and prep times, delivery distance and speed, and each runner's success rate.

C. Ingredient Optimisation (6 questions) — standard recipe breakdowns, the most-added and most-excluded toppings, generating a human-readable order summary per pizza, and total ingredient usage across all deliveries.

D. Pricing and Ratings (5 questions) — revenue under a fixed-price model, the impact of charging for extras, a self-designed ratings table, a full join of delivery + rating data, and net profit after paying runners per kilometre.

E. Bonus (1 question) — the schema change and INSERT statements needed to add a new "Supreme" pizza to the menu.

SQL Concepts Demonstrated

This project goes beyond standard joins and aggregation into genuine data engineering: CASE-based string normalization across three different NULL representations, type casting from text to DATETIME/DECIMAL/INT, recursive CTEs to split comma-separated topping lists into individual rows, correlated subqueries (NOT EXISTS) to remove excluded toppings from a base recipe, GROUP_CONCAT with custom ordering to rebuild human-readable strings, window-free running totals via UNION ALL + GROUP BY for ingredient quantity counts, and date/time arithmetic (TIMESTAMPDIFF, DATE_ADD) for delivery-time analysis.

A Few Findings

• Order prep time scales almost linearly with order size: single-pizza orders average 12 minutes from order to pickup, two-pizza orders average 18 minutes, and the one three-pizza order took 29 minutes.
• Runner reliability varies sharply: Runner 1 completed 100% of assigned deliveries (4/4), Runner 2 completed 75% (3/4), and Runner 3 completed just 50% (1/2).
• Cheese is the most commonly excluded topping (4 times) while bacon is the most commonly added extra (4 times) — a useful pairing for Danny to know if he's tuning the default recipes.
• One delivery (Runner 2, Order 8) computes to an average speed of roughly 94 km/h — almost certainly a data quality artifact in the captured distance or duration rather than a real delivery, and worth flagging rather than taking at face value.
• Under a flat $12/$10 pricing model, delivered pizzas generated $138 in revenue ($142 once extras are charged at $1 each); after paying runners $0.30/km, net revenue comes out to $94.44.

What I Learned

This case study pushed well past basic querying into data engineering: writing a recursive CTE from scratch to explode '2, 6'-style comma lists into individual rows, then using that split data with NOT EXISTS to compute a real per-pizza ingredient list (base recipe, minus exclusions, plus extras, with "2x" for doubled toppings). It was also a good lesson in not trusting raw data at face value — the distance/duration fields needed real investigation before they could be cast to usable numeric types at all.

How to Run This Project

1. Clone this repository.
2. Open a MySQL client (MySQL Workbench, CLI, or similar). Requires MySQL 8.0+ (recursive CTE support).
3. Run sql/pizza_runner_setup.sql to create the database, raw tables, and the cleaned/normalized tables used by every query.
4. Run sql/pizza_runner_solutions.sql to execute all 29 questions against that data.

Source

This case study is based on Case Study #2: Pizza Runner from Danny Ma's 8 Week SQL Challenge.