Skip to content

Files

Latest commit

 

History

History

Case Study #1 - Danny's Diner

Case Study #1: Danny's Diner

📋 Case Study Overview

In this case study, we are tasked with helping Danny, the owner of a small restaurant, analyze his business data. The goal is to help him better understand customer behavior, optimize sales strategies, and improve operational efficiency. The case study involves queries focusing on simple aggregations, joins, and date-based filtering.

View the detailed case study on www.8weeksqlchallange.com

📝 Objectives

The main objectives of the case study are:

  • Understanding customer purchase behavior.
  • Analyzing total sales and revenue generated by the diner.
  • Identifying patterns of frequent diners and how their spending changes over time.

🔧 How to Use

In this folder, you will find the following files:

  • Database_Creation_Queries.sql: This .sql file contains all queries to create database and tables.

  • Questions and Answers Queries.sql: This file contains all the queries used to answer the case study questions.

  • Questions And Answers with Queries and Output.pdf: Each question is answered with the query and the output generated.

    Note: All Solutions are coded with MySQL

📑 Table of Contents

  1. Q1: What is the total amount each customer spent at the restaurant?
SELECT
	sales.customer_id,
	SUM(menu.price) AS 'Total Spending in $'
FROM sales
INNER JOIN menu ON
	sales.product_id=menu.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id;
customer_id Total Spending in $
A 76
B 74
C 36
  1. Q2: How many days has each customer visited the restaurant?
SELECT
	customer_id,
	COUNT(DISTINCT(order_date)) AS days_visited
FROM sales
GROUP BY customer_id;
customer_id days_visited
A 4
B 6
C 2
  1. Q3. What was the first item from the menu purchased by each Customer?
SELECT 
	customer_id,
        product_name AS first_purchased_product
FROM
    (SELECT 
	sales.customer_id,
	menu.product_name,
	DENSE_RANK() OVER( PARTITION BY sales.customer_id ORDER BY sales.order_date) As rnk
      FROM sales
      INNER JOIN menu ON sales.product_id=menu.product_id) AS sales_rank
WHERE rnk = 1
GROUP BY customer_id,product_name;
customer_id first_purchased_product
A sushi
A curry
B curry
C ramen
  1. Q4. What is the most purchased item on the menu and how many times was it purchased by all customers?
  • This question needs to be solved in two parts, in part 1, we'll find the most purchased item and in part 2, we'll find how mny times its purchased by all customers
-- Part 1

SELECT 
	menu.product_name AS 'Most purchased Product',
	COUNT(sales.product_id) AS 'Purchase Count'
FROM sales 
INNER JOIN menu 
	ON sales.product_id = menu.product_id
GROUP BY menu.product_name
ORDER BY COUNT(sales.product_id) DESC
LIMIT 1;                                           -- this gives the most purchased item on menu
Most purchased Product Purchase Count
ramen 8
-- Part 2

SELECT 
	sales.customer_id,
	COUNT(sales.product_id) AS purchase_count
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
WHERE sales.product_id = (SELECT product_id FROM sales
			  GROUP BY product_id
			  ORdER BY count(product_id)
			  DESC LIMIT 1)
GROUP BY sales.customer_id
ORDER BY purchase_count DESC;                    -- this gives the list of customers who have purchased highest purchased item
customer_id purchase_count
A 3
B 2
C 3
  1. Q5. Which item was the most popular for each customer?
WITH cte_popular_products as ( 
	SELECT 
		sales.customer_id,
		menu.product_name,
		COUNT(*) as purchase_count,
		DENSE_RANK() OVER( PARTITION BY sales.customer_id 
				   ORDER BY COUNT(*) DESC) AS rnk
	FROM sales
	INNER JOIN menu ON sales.product_id = menu.product_id
	GROUP BY sales.customer_id, menu.product_name )
SELECT 
	customer_id,
	product_name,
	purchase_count
FROM cte_popular_products
WHERE rnk=1;
customer_id product_name purchase_count
A ramen 3
B curry 2
B sushi 2
B ramen 2
C ramen 3
  1. Q6. Which item was purchased first by the customer after they became a member?
WITH cte_after_membership AS (
  SELECT
    members.customer_id, 
    sales.product_id,
    DENSE_RANK() OVER (
      PARTITION BY members.customer_id
      ORDER BY sales.order_date) AS densrank
  FROM members
  INNER JOIN sales
    ON members.customer_id = sales.customer_id
    AND sales.order_date > members.join_date
)
SELECT 
  	cte_after_membership.customer_id, 
	  menu.product_name 
FROM cte_after_membership
INNER JOIN menu
 	 ON cte_after_membership.product_id = menu.product_id
WHERE densrank = 1
ORDER BY cte_after_membership.customer_id ASC;
customer_id product_name
A ramen
B sushi
  1. Q7. Which item was purchased just before the customer became a member?
WITH cte_after_membership AS (
  SELECT
	members.customer_id, 
	sales.product_id,
	DENSE_RANK() OVER (
        PARTITION BY members.customer_id
        ORDER BY sales.order_date) AS densrank
  FROM members
  INNER JOIN sales
    ON members.customer_id = sales.customer_id
    AND sales.order_date < members.join_date
)
SELECT 
  cte_after_membership.customer_id, 
  menu.product_name 
FROM cte_after_membership
INNER JOIN menu
  ON cte_after_membership.product_id = menu.product_id
WHERE densrank = 1
ORDER BY cte_after_membership.customer_id ASC;
customer_id product_name
A sushi
A curry
B curry
  1. Q8. What is the total items and amount spent for each member before they became a member?
SELECT
	sales.customer_id,
	COUNT(sales.product_id) AS total_items,
	SUM(menu.price) AS total_amt_spent
FROM sales
JOIN members ON sales.customer_id = members.customer_id
JOIN menu ON sales.product_id = menu.product_id
WHERE sales.order_date < members.join_date
group by sales.customer_id
ORDER BY sales.customer_id;
customer_id total_items total_amt_spent
A 2 25
B 3 40
  1. Q9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier, how many points would each customer have?
SELECT 
    sales.customer_id,
    SUM(menu.price) AS total_price,
    SUM(CASE
        WHEN menu.product_name = 'sushi' THEN menu.price * 20
        ELSE menu.price * 10
    END) AS total_points
FROM sales
	JOIN menu ON sales.product_id = menu.product_id
GROUP BY customer_id;
customer_id total_price total_points
A 76 860
B 74 940
C 36 360
  1. Q10. In the first week after a customer joins the program (including their join date), they earn 2x points on all items, not just sushi. How many points do customers A and B have at the end of January?
SELECT 
	sales.customer_id,
	SUM(menu.price) AS total_price,
	SUM(CASE 
		WHEN sales.order_date BETWEEN members.join_date AND DATE_ADD(members.join_date, INTERVAL 6 DAY) THEN menu.price*20
		ELSE 
			CASE WHEN menu.product_name = 'sushi' THEN menu.price * 20
			ELSE menu.price * 10
			END
		END) AS points
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 <= '2021-01-31'
AND sales.customer_id IN ('A','B')
GROUP BY sales.customer_id
ORDER BY sales.customer_id;
customer_id total_price points
A 76 1370
B 62 820

Bonus Questions: Q1. Join All The Things Recreate the table with columns: customer_id, order_date, product_name, price, member_status (Y/N)

SELECT 
  sales.customer_id, 
  sales.order_date,  
  menu.product_name, 
  menu.price,
  CASE
    WHEN sales.order_date >= members.join_date THEN 'Y'
    ELSE 'N' END AS member_status
FROM sales
LEFT JOIN members
  ON sales.customer_id = members.customer_id
INNER JOIN menu
  ON sales.product_id = menu.product_id
ORDER BY members.customer_id;
customer_id order_date product_name price member_status
A 2021-01-01 sushi 10 N
A 2021-01-01 curry 15 N
A 2021-01-07 curry 15 Y
A 2021-01-10 ramen 12 Y
A 2021-01-11 ramen 12 Y
A 2021-01-11 ramen 12 Y
B 2021-01-01 curry 15 N
B 2021-01-02 curry 15 N
B 2021-01-04 sushi 10 N
B 2021-01-11 sushi 10 Y
B 2021-01-16 ramen 12 Y
B 2021-02-01 ramen 12 Y
C 2021-01-01 ramen 12 N
C 2021-01-01 ramen 12 N
C 2021-01-07 ramen 12 N

Q2. Rank All The Things Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases, so he expects null ranking values for the records when customers are not yet part of the loyalty program.

 WITH cte_all_joined AS (
    SELECT
        s.customer_id,
        s.order_date AS order_date,
        m.product_name,
        m.price,
        CASE
            WHEN s.customer_id IS NOT NULL
                 AND s.order_date >= mm.join_date THEN 'Y'
            ELSE 'N'
        END AS member_status
    FROM sales s
    INNER JOIN menu m ON s.product_id = m.product_id
    LEFT JOIN members mm ON s.customer_id = mm.customer_id
)
SELECT
    *,
    CASE
        WHEN member_status = 'Y' THEN
            DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date)
        ELSE
            NULL
    END AS ranking
FROM
    cte_all_joined
ORDER BY
    customer_id,
    order_date,
    product_name;
customer_id order_date product_name price member_status ranking
A 2021-01-01 curry 15 N NULL
A 2021-01-01 sushi 10 N NULL
A 2021-01-07 curry 15 Y 2
A 2021-01-10 ramen 12 Y 3
A 2021-01-11 ramen 12 Y 4
A 2021-01-11 ramen 12 Y 4
B 2021-01-01 curry 15 N NULL
B 2021-01-02 curry 15 N NULL
B 2021-01-04 sushi 10 N NULL
B 2021-01-11 sushi 10 Y 4
B 2021-01-16 ramen 12 Y 5
B 2021-02-01 ramen 12 Y 6
C 2021-01-01 ramen 12 N NULL
C 2021-01-01 ramen 12 N NULL
C 2021-01-07 ramen 12 N NULL

For a detailed walkthrough of this case study, visit the video on iThinkData YouTube channel Playlist.

🙋‍♂️ About Me

I’m Vaibhav Chavan, the creator of iThinkData, a platform dedicated to making data analytics and SQL easy to understand for everyone. Follow along as I dive deep into SQL tutorials, case studies, and data projects on YouTube.

🌟 Stay Connected with iThinkData ! 🌟

🎥 Subscribe to iThinkData : 🔔 Don't miss out on weekly data challenges, tutorials, and expert insights! 💡📈

👨‍💼 LinkedIn : 📊 Let’s connect professionally and build a powerful data-driven network! 💼🌐

💬 Join My WhatsApp Channel : 📱 Be the first to get exclusive content, project updates, and new videos! 🚀📊