Danny's Diner SQL Case Study

SQL case study analyzing customer spending, loyalty membership behavior, and purchasing patterns using MySQL — based on Case Study #1 of the 8 Week SQL Challenge.

Overview

Danny runs a small Japanese restaurant serving three dishes — sushi, curry, and ramen — and wants to use the data he's collected so far to understand his business better: how much customers spend, how often they come back, which dishes are most popular, and whether his new loyalty program is actually changing customer behavior. This repository answers those questions using nothing but raw SQL — aggregations, joins, conditional logic, and window functions — run against a three-table MySQL database.

Dataset

The database has three tables.

Table	Columns	Description
sales	customer_id, order_date, product_id	Every order placed, by customer and date
menu	product_id, product_name, price	Maps each product to its name and price
members	customer_id, join_date	Loyalty program enrollment date, where applicable

Three customers appear in the data: Customer A (joined the loyalty program on 2021-01-07), Customer B (joined 2021-01-09), and Customer C (never joined).

Repository Structure

dannys-diner-sql-case-study/
├── README.md
├── sql/
│   ├── dannys_diner_setup.sql        # Database, tables, and sample data
│   └── dannys_diner_solutions.sql    # All 12 case study queries, commented
├── screenshots/                      # Query result screenshots
└── assets/                           # Supporting images / diagrams

Case Study Questions

Customer behavior

1. Total amount spent by each customer
2. Number of days each customer visited
3. First item purchased by each customer
4. Most purchased item across all customers
5. Most popular item for each individual customer

Membership & loyalty 6. First item purchased after becoming a member 7. Item purchased immediately before becoming a member 8. Total items and amount spent before becoming a member 9. Points earned under the standard loyalty scheme (sushi earns 2x points) 10. Points earned through January, including the first-week 2x membership bonus

Bonus challenges 11. Join All The Things — one combined view of every order with price and membership status 12. Rank All The Things — ranking each member's orders, starting only once they've joined

SQL Concepts Demonstrated

This project works through SELECT, WHERE, GROUP BY, and ORDER BY fundamentals, aggregate functions (COUNT, SUM), conditional logic with CASE, INNER JOIN and LEFT JOIN across three tables, correlated subqueries, window functions (DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)), and date arithmetic (DATE_ADD, BETWEEN) to model the membership bonus window.

A Few Findings

• Customer B visited the most often (6 separate days), but Customer A spent slightly more overall ($76 vs. $74) — visit frequency and total spend don't move together here.
• Ramen was the most-ordered item across all customers (8 orders total), and it was also the top individual item for both Customer A and Customer C.
• The first-week membership bonus mattered a lot for Customer A specifically: three of A's largest orders happened to land inside that 7-day bonus window, pushing January points to 1,370 versus roughly 860 under the standard scheme alone.

What I Learned

This case study was a good forcing function for thinking in sets rather than loops — particularly using DENSE_RANK() to solve "first/last item per customer" problems without procedural code, and nesting CASE logic inside SUM() to encode business rules (like the loyalty bonus window) directly into an aggregate query.

How to Run This Project

1. Clone this repository.
2. Open a MySQL client (MySQL Workbench, CLI, or similar).
3. Run sql/dannys_diner_setup.sql to create the database, tables, and sample data.
4. Run sql/dannys_diner_solutions.sql to execute all 12 queries against that data.

Source

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