8-Week SQL Challenge

Solutions to All 7 Case Studies from Danny Ma's #8WeekSQLChallenge

Complete MySQL solutions for 7 of the 8 case studies from Danny Ma's 8 Week SQL Challenge — covering restaurant analytics, delivery operations, subscription modelling, banking, retail, e-commerce funnel analysis, and clothing retail reporting.

---

Table of Contents

• Case Studies
• Technical Highlights
• SQL Concepts Demonstrated
• How to Run
• Structure

---

Case Studies

#	Case Study	Domain	SQL Files	Complexity
1	Danny's Diner	Restaurant loyalty	5	Medium
2	Pizza Runner	Delivery operations	9	High
3	Foodie-Fi	Subscription analytics	4	Medium
4	Data Bank	Digital banking	2	High
5	Data Mart	Retail impact analysis	2	Medium
6	Clique Bait	E-commerce funnel	3	Very High
7	Balanced Tree	Clothing retail	8	High

---

Case Studies

Case 1 — Danny's Diner

Domain: Restaurant loyalty analytics Tables: sales, members, menu

Analyses customer spending patterns, first purchases, most-bought items per customer, and loyalty points calculations. The most complex query uses session variables (SET @datea/@dateb) to capture membership join dates and a UNION of four subqueries to calculate time-based point multipliers (2× in the first week after joining, standard 10/20× otherwise).

---

Case 2 — Pizza Runner

Domain: Delivery operations Tables: customer_orders, runner_orders, pizza_names, pizza_toppings

Includes a data cleaning step using CREATE TABLE AS SELECT with CASE WHEN ... LIKE 'null' to handle mixed NULL/string-null values in the exclusions and extras columns. Covers pizza type counts, order modification analysis, runner delivery percentages, and order duration calculations.

---

Case 3 — Foodie-Fi

Domain: Subscription streaming analytics Tables: subscriptions, plans

Uses CREATE VIEW + MONTHNAME() + GROUP BY MONTH() to track trial plan signups by calendar month. Covers churn rate after initial plan, subscription plan distribution, and multi-step customer journey tracking via CTEs.

---

Case 4 — Data Bank

Domain: Digital banking + data allocation Tables: customer_nodes, customer_transactions

Contains the most advanced statistical SQL in the repo: median, 80th percentile, and 95th percentile of customer-node residency duration — computed using ROW_NUMBER(), PERCENT_RANK() window functions, DATEDIFF, and a regex filter (WHERE end_date NOT REGEXP "^9999") to exclude open-ended records — all in a single UNION query.

---

Case 5 — Data Mart

Domain: Sustainable retail packaging impact

Analyses year-over-year sales changes before and after a June 2020 packaging change, broken down by platform and demographic segment.

---

Case 6 — Clique Bait

Domain: E-commerce funnel analytics Tables: events, event_identifier, page_hierarchy

The most technically demanding query in the entire repo — a 10-CTE chain (Aggregated 2.sql) that builds a complete purchase funnel:

1. Joins events with identifiers and page hierarchy
2. Counts page views per product
3. Counts add-to-cart events per product
4. Encodes checkout + purchase events as binary flags
5. Identifies completed purchases via visit_id
6. Determines cart abandonment (added but not purchased)
7. Aggregates by product category: Views → Cart Adds → Abandoned → Purchased

---

Case 7 — Balanced Tree Clothing

Domain: Retail financial reporting Tables: sales, product_details

The most SQL-file-heavy case study (8 files). Highlights include:

• Market basket analysis via triple self-join on txn_id with strict inequality guards (a.product_name < b.product_name < c.product_name) to find the most common 3-product transaction combinations
• Revenue split analysis by category and segment using CTEs
• Product penetration percentages (% of transactions containing each product)

---

Technical Highlights

Window Functions

-- Percentile calculation from Case Study 4
SELECT
 PERCENT_RANK() OVER (ORDER BY duration_days) AS percentile,
 duration_days
FROM customer_node_durations

Multi-CTE Funnel (Case 6 — 10 CTEs)

WITH page_views AS (...),
 cart_adds AS (...),
 purchases AS (...),
 abandoned AS (...)
SELECT * FROM abandoned JOIN purchases ...

Market Basket — Triple Self-Join (Case 7)

SELECT a.product_name, b.product_name, c.product_name, COUNT(*)
FROM sales a
JOIN sales b ON a.txn_id = b.txn_id AND a.product_name < b.product_name
JOIN sales c ON a.txn_id = c.txn_id AND b.product_name < c.product_name
GROUP BY 1,2,3 ORDER BY COUNT(*) DESC

---

SQL Concepts Demonstrated

• WITH CTEs (up to 10 chained)
• Window functions (ROW_NUMBER, PERCENT_RANK, RANK)
• Self-joins for combinatorial analysis
• Session variables for stateful calculations
• DATEDIFF and temporal logic
• Regex filtering (REGEXP)
• Data cleaning with CASE WHEN + LIKE 'null'
• Aggregation with GROUP BY + HAVING
• CREATE VIEW for reusable query abstractions

---

How to Run

1. Set up MySQL Workbench 8.0 or any MySQL 8.x instance
2. Create the schema for each case study from 8weeksqlchallenge.com
3. Run the .sql files in the numbered order within each case study folder

---

Structure

8-Week-SQL-Challenge/
└── SQL DATA WITH DANNY SERIOUS SQL/
 ├── DANNY' DINER/ # Case 1 — 5 SQL files + docx write-up
 ├── PIZZA DANNY/ # Case 2 — 9 SQL files + docx write-up
 ├── Foodie Fi/ # Case 3 — 4 SQL files + docx write-up
 ├── DATA_BANK/ # Case 4 — 2 SQL files + docx write-up
 ├── DATA MART/ # Case 5 — 2 SQL files + docx write-up
 ├── Clique Bait/ # Case 6 — 3 SQL files + docx write-up
 ├── Balanced Tree/ # Case 7 — 8 SQL files + docx write-up
 └── Fresh Segments/ # Case 8 — docx write-up only