8weeksqlchallenge - Week1

Case Study #1 - Danny's Dinner

Introduction

This is the simple study case about Danny Japanese restaurant. With 3 simple datasets - sales, menu, members, you can practice to write queries to support him with the practical insights of a restaurant. Danny wants to leverage customer data to gain insights into their visiting patterns, spending habits, and favorite menu items. This information will allow him to provide a more personalized experience to his loyal customers and potentially expand his existing customer loyalty program.

Case Study Insights

The case study focus on the insights relating the customer experiences that is vital element of successful restaurant. Although the result of queries does not show clearly relation, but the most important thing of this scenario is customer metrics that we need to consider for improvement of customer experiences.

Here are the key points:

1. Customer Insights : Danny wants to understand his customers better. This includes analyzing their visiting patterns, how much money they've spent, and identifying their favorite menu items.

2. Personalized Experience: The goal is to use these insights to deliver a better and more personalized experience to loyal customers.

3. Loyalty Program Expansion: Danny intends to decide whether to expand his existing customer loyalty program based on the insights gained from the data.

Or you can refer these resources for more restaurant metrics:

SQL code

The techniques that you can learn from this case study :

Let’s get through some questions of this study case to review of technique.

												
													SELECT customer_id,
															SUM(menu.price) as Total_Spent
													FROM sales 
													JOIN menu 
													ON sales.product_id = menu.product_id 
													GROUP BY customer_id;
												
											

												
													WITH rank_purchase_item AS(
																	SELECT sales.customer_id, 
																		product_name, 
																		order_date, 
																		join_date,
																		DENSE_RANK() OVER(Partition by sales.customer_id ORDER BY order_date) AS RANK 
																	FROM sales 
																	INNER JOIN menu
																	ON sales.product_id = menu.product_id
																	INNER JOIN members
																	ON sales.customer_id = members.customer_id
																	WHERE sales.order_date >= members.join_date
													)
													SELECT customer_id, product_name
													FROM rank_purchase_item
													WHERE RANK =1;
												
											

												
													SELECT customer_id,
															SUM(CASE WHEN product_name = 'sushi' THEN price *20 
																	ELSE price * 10 END ) AS total_point
													FROM sales S
													JOIN menu M ON S.product_id = M.product_id
													GROUP BY  customer_id;
												
											

We have total 10 questions. You can access my github link for full code and explanation

My Github