SQL case study solving customer purchasing behavior using MySQL, joins, CTEs, window functions and aggregation.
This project contains the SQL solutions for the Danny's Dinner Case Study from the 8 Week SQL Challenge.
The objective of this case study is to analyze customer purchasing behavior, restaurant sales, and membership data using SQL queries.
- Analyze customer spending patterns
- Find the most popular menu items
- Calculate total revenue
- Understand customer visits
- Practice SQL using real business scenarios
- MySQL
- SQL
- GitHub
This case study contains three tables:
Stores customer orders.
| Column | Description |
|---|---|
| customer_id | Customer ID |
| order_date | Date of purchase |
| product_id | Purchased product |
Stores menu details.
| Column | Description |
|---|---|
| product_id | Product ID |
| product_name | Product Name |
| price | Product Price |
Stores membership information.
| Column | Description |
|---|---|
| customer_id | Customer ID |
| join_date | Membership Join Date |
- SELECT
- WHERE
- GROUP BY
- ORDER BY
- INNER JOIN
- LEFT JOIN
- Aggregate Functions
- CASE WHEN
- CTE (Common Table Expressions)
- Window Functions
- RANK()
- DENSE_RANK()
- ROW_NUMBER()
- Total amount spent by each customer
- Number of days each customer visited
- First item purchased by each customer
- Most purchased item overall
- Most popular item for each customer
- First item purchased after becoming a member
- Item purchased just before becoming a member
- Total items and amount spent before membership
- Customer reward points
- Bonus reward points calculation
Danny-s-Dinner-SQL-Case-Study/
├── README.md
├── Danny's_dinner_sql_casestudy.sql
└── screenshots/
Through this project, I learned:
- SQL Joins
- Aggregation Functions
- Window Functions
- Common Table Expressions (CTEs)
- Customer Behavior Analysis
- Business Problem Solving with SQL
Sonal Kumari
Aspiring Data Analyst
⭐ If you found this project useful, feel free to star this repository.