Case Study #1 - Danny's Diner

Please note that all the information regarding the case study has been sourced from the following link: here.

Introduction

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.

Danny’s Diner is in need of your assistance to help the restaurant stay afloat - the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.

Problem Statement

Danny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favourite. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.

He plans on using these insights to help him decide whether he should expand the existing customer loyalty program - additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.

Danny has provided you with a sample of his overall customer data due to privacy issues - but he hopes that these examples are enough for you to write fully functioning SQL queries to help him answer his questions!

Danny has shared with you 3 key datasets for this case study:

• sales

• menu

• members

Entity Relationship Diagram

Example Datasets

All datasets exist within the dannys_diner database schema - be sure to include this reference within your SQL scripts as you start exploring the data and answering the case study questions.

Table 1: sales The sales table captures all customer_id level purchases with an corresponding order_date and product_id information for when and what menu items were ordered.

Table 2: menu The menu table maps the product_id to the actual product_name and price of each menu item.

Table 3: members The final members table captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program.

Interactive SQL Session

You can use Online Platform like DB Fiddle to have interactive session in minutes or You can use Jupyter Notebook to solve this case study or use any IDEA like VS code, Snowflake, Databrick or BigQuery practise as you wish.

Most of time i work with VS code and Jupyter Notebook, so I am Solving the Query using this Way

• ipython-sql or jupysql

• psycopg2

• sqlalchemy

# to connect the Database we need to invoke the SQL magic function %%sql
%load_ext sql

# to connect to database
%sql postgresql://postgres:krish@127.0.0.1:5432/kanna

Schema

Loading Schema into the postgresql database from jupyter notebook itself.

%%sql

CREATE SCHEMA dannys_diner;
SET search_path = dannys_diner;

CREATE TABLE sales (
  "customer_id" VARCHAR(1),
  "order_date" DATE,
  "product_id" INTEGER
);

INSERT INTO sales
  ("customer_id", "order_date", "product_id")
VALUES
  ('A', '2021-01-01', '1'),
  ('A', '2021-01-01', '2'),
  ('A', '2021-01-07', '2'),
  ('A', '2021-01-10', '3'),
  ('A', '2021-01-11', '3'),
  ('A', '2021-01-11', '3'),
  ('B', '2021-01-01', '2'),
  ('B', '2021-01-02', '2'),
  ('B', '2021-01-04', '1'),
  ('B', '2021-01-11', '1'),
  ('B', '2021-01-16', '3'),
  ('B', '2021-02-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-07', '3');


CREATE TABLE menu (
  "product_id" INTEGER,
  "product_name" VARCHAR(5),
  "price" INTEGER
);

INSERT INTO menu
  ("product_id", "product_name", "price")
VALUES
  ('1', 'sushi', '10'),
  ('2', 'curry', '15'),
  ('3', 'ramen', '12');


CREATE TABLE members (
  "customer_id" VARCHAR(1),
  "join_date" DATE
);

INSERT INTO members
  ("customer_id", "join_date")
VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

15 rows affected.

3 rows affected.

2 rows affected.

%%sql

SELECT * FROM dannys_diner.menu

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

%%sql

SELECT * FROM dannys_diner.sales

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

15 rows affected.

Truncated to displaylimit of 10.

%%sql

SELECT * FROM dannys_diner.members

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

Case Study Questions

Each of the following case study questions can be answered using a single SQL statement:

1. What is the total amount each customer spent at the restaurant?

2. How many days has each customer visited the restaurant?

3. What was the first item from the menu purchased by each customer?

4. What is the most purchased item on the menu and how many times was it purchased by all customers?

5. Which item was the most popular for each customer?

6. Which item was purchased first by the customer after they became a member?

7. Which item was purchased just before the customer became a member?

8. What is the total items and amount spent for each member before they became a member?

9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?

10. 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 customer A and B have at the end of January?

1. What is the total amount each customer spent at the restaurant?

How you can approach this SQL problem,

• step 1 - choose column you need,

  • customer_id, price

  • dannys_diner.sales is not enough to answer this question, you need dannys_diner.menu tables as well cause price column available.

• step 2 - choose what type of join you gone use?

  • Use JOIN to merge dannys_diner.sales and dannys_diner.menu tables as sales.customer_id and menu.price are from both tables.

• step 3 - how to get total amount of each customer?

  • Use SUM to calculate the total sales contributed by each customer.

  • Group the aggregated results by sales.customer_id.

%%sql

SELECT s.customer_id, sum(m.price)
FROM dannys_diner.sales s 
JOIN dannys_diner.menu m 
ON s.product_id = m.product_id
GROUP BY s.customer_id
ORDER BY s.customer_id ASC

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

2. How many days has each customer visited the restaurant?

How you can approach this SQL problem,

• step 1 - choose column you need,

  • customer_id, new column which display count of customer visited count

  • dannys_diner.sales is enough to answer this question.

• step 2 - choose which SQL keyword will be useful to solve the problem

  • DISTINCT prevents overcounting: This keyword ensures that each unique order date is counted only once, even if a customer made multiple visits on the same day.

  • Accurate visit count: This approach provides a precise representation of how many different days each customer has visited.

  • Use COUNT keyword to count distinct order_date

• NOTE:

  • as customer visited count is calculated based on order_date, so consider customer visited single time in a day, even though we have multiple order, it is not make valueable sense to consider each order_date as each time customer visited.

%%sql

SELECT s.customer_id, COUNT(DISTINCT(s.order_date))
from dannys_diner.sales as s
GROUP BY s.customer_id

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

3.What was the first item from the menu purchased by each customer?

How to approach this SQL Problem,

• Step 1: Choose Necessary Columns

• [ ] We need customer_id and product_name from the dannys_diner.sales and dannys_diner.menu tables, respectively.

• [ ] Also, we require the _order_date_ from the dannys_diner.sales table to identify the earliest purchase date.

• Step 2: Determine the Join Type

• [ ] Perform an INNER JOIN to merge the dannys_diner.sales and dannys_diner.menu tables based on the common column product_id.

• Step 3: Rank and Filter to Identify the First Item

• [ ] Use a ranking function like ROW_NUMBER() or DENSE_RANK() to assign a sequential number to each product purchased by a customer, ordered by the order_date.

• [ ] Filter the results to fetch only the rows where the ranking is 1 for each customer, indicating their first purchase.

Method 1 - using DENSE RANK

%%sql

WITH ordered_sales AS (
  SELECT 
    sales.customer_id, 
    sales.order_date, 
    menu.product_name,
    DENSE_RANK() OVER (
      PARTITION BY sales.customer_id 
      ORDER BY sales.order_date) AS rank
  FROM dannys_diner.sales
  INNER JOIN dannys_diner.menu
    ON sales.product_id = menu.product_id
)

SELECT 
  customer_id, 
  product_name
FROM ordered_sales
WHERE rank = 1
GROUP BY customer_id, product_name;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

4 rows affected.

Method 2 - Using ROW_NUMBER

%%sql
WITH ranked_sales AS (
    SELECT 
        s.customer_id, 
        m.product_name,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) AS rank
    FROM 
        dannys_diner.sales s
    INNER JOIN 
        dannys_diner.menu m ON s.product_id = m.product_id
)

SELECT 
    customer_id, 
    product_name
FROM 
    ranked_sales
WHERE 
    rank = 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

As you can see there is huge difference using ROW_NUMBER and DENSE RANK,

ROW_NUMBER simply assigns a sequential number to each row within each partition (defined by customer_id) based on the order of order_date. It doesn't care about ties in the ordering column. So, even if two customers have the same first order date, they might not both get the same rank of 1.

DENSE_RANK, on the other hand, does care about ties. If two customers have the same first order date, they will both get the same rank of 1. However, it ensures no gaps in the ranking sequence. So, the next customer (after the two tied customers) will get a rank of 3, not 2, to maintain the dense sequence.

Method 3: Finding the First Purchase for Each Customer

1. Selecting Necessary Columns:

   • The query aims to retrieve customer_id from the sales table and product_name from the menu table.

2. Determining the Join Type:

   • The query performs an INNER JOIN between the sales and menu tables, utilizing the common product_id.

3. Identifying the First Purchase for Each Customer:

   • The WHERE clause within the query filters rows from the sales table based on the condition that the order_date matches the earliest order_date found for each customer_id. This is achieved by using a subquery that finds the minimum order_date for each customer_id.

%%sql

SELECT 
  s.customer_id,
  m.product_name
FROM dannys_diner.sales s
INNER JOIN dannys_diner.menu m ON s.product_id = m.product_id
WHERE s.order_date = (
  SELECT MIN(order_date)
  FROM dannys_diner.sales s2
  WHERE s2.customer_id = s.customer_id
)
GROUP BY s.customer_id, m.product_name;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

4 rows affected.

4.What is the most purchased item on the menu and how many times was it purchased by all customers?

How to approach the Problem

Step 1: Choose Necessary Columns

• Select product_name from the dannys_diner.menu table.

• Count the occurrences of each product from the dannys_diner.sales table.

Step 2: Determine the Join Type

• Utilize an INNER JOIN to merge the dannys_diner.sales and dannys_diner.menu tables based on the common column product_id.

Step 3: Count Purchase Frequencies

• Use the COUNT function to calculate the number of times each item was purchased.

• Group the results by menu.product_name to count purchases for each item.

Step 4: Find the Most Purchased Item

• Order the results in descending order of purchase count (most_purchased_item).

• Retrieve the top row (item) with the highest purchase count.

%%sql

SELECT 
    menu.product_name,
    COUNT(sales.product_id) AS most_purchased_item
FROM 
    dannys_diner.sales
INNER JOIN 
    dannys_diner.menu ON sales.product_id = menu.product_id
GROUP BY 
    menu.product_name
ORDER BY 
    most_purchased_item DESC
LIMIT 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

1 rows affected.

5. Which item was the most popular for each customer?

How to approach the Problem,

1. Choose Necessary Columns:

   • Select customer_id and product_name columns.

2. Determine Join Type:

   • Utilize an INNER JOIN to merge the sales and menu tables based on the common column product_id.

3. Count Each Item's Occurrences for Every Customer:

   • Use the COUNT function to determine how many times each item was purchased by each customer.

   • Group the results by both customer_id and product_name.

4. Identify the Most Popular Item for Each Customer:

   • Apply a ranking function (ROW_NUMBER(), RANK(), or DENSE_RANK()) partitioned by customer_id and ordered by the count of purchases (most_popular_item).

   • Retrieve the rows where the ranking is 1 for each customer_id, indicating the most popular item.

5. Retrieve the Most Popular Item for Each Customer:

   • Construct a query to fetch the customer_id, product_name, and their respective counts or ranks to identify the most popular item for each customer.

Method 1 - using ROW_NUMBER

%%sql

SELECT 
    customer_id,
    product_name,
    most_popular_item
FROM (
    SELECT 
        s.customer_id,
        m.product_name,
        COUNT(*) AS most_popular_item,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY COUNT(*) DESC) AS popularity_rank
    FROM 
        dannys_diner.sales s
    INNER JOIN 
        dannys_diner.menu m ON s.product_id = m.product_id
    GROUP BY 
        s.customer_id, m.product_name
) AS popular_items
WHERE 
    popularity_rank = 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

Method 2 - RANK()

%%sql

SELECT 
    customer_id,
    product_name,
    most_popular_item
FROM (
    SELECT 
        s.customer_id,
        m.product_name,
        COUNT(*) AS most_popular_item,
        RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(*) DESC) AS popularity_rank
    FROM 
        dannys_diner.sales s
    INNER JOIN 
        dannys_diner.menu m ON s.product_id = m.product_id
    GROUP BY 
        s.customer_id, m.product_name
) AS popular_items
WHERE 
    popularity_rank = 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

5 rows affected.

Method 3 - DENSE_RANK()

%%sql

SELECT 
    customer_id,
    product_name,
    most_popular_item
FROM (
    SELECT 
        s.customer_id,
        m.product_name,
        COUNT(*) AS most_popular_item,
        DENSE_RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(*) DESC) AS popularity_rank
    FROM 
        dannys_diner.sales s
    INNER JOIN 
        dannys_diner.menu m ON s.product_id = m.product_id
    GROUP BY 
        s.customer_id, m.product_name
) AS popular_items
WHERE 
    popularity_rank = 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

5 rows affected.

6.Which item was purchased first by the customer after they became a member?

How to approach the Problem

1. Common Table Expression (CTE) - fc (First Purchase After Membership):

   • The CTE (fc) selects customer_id, product_name, and assigns a row number using ROW_NUMBER() over the partition of customer_id, ordered by order_date.

   • It joins the sales, menu, and members tables based on their respective columns (product_id, customer_id) and filters orders (sales) after the customer's join date in the membership table.

2. Main Query:

   • The main query selects all columns from the CTE fc where the row_num is 1, indicating the first purchase made by each customer after joining as a member.

Method 1 - Using CTE

%%sql

with fc as
(
SELECT 
s.customer_id, m.product_name, ROW_NUMBER() OVER ( PARTITION BY mm.customer_id ORDER BY s.order_date) as row_num
FROM
dannys_diner.sales s 
JOIN 
dannys_diner.menu m
ON
m.product_id = s.product_id
JOIN
dannys_diner.members mm
ON
s.customer_id = mm.customer_id
WHERE
s.order_date > mm.join_date 
)

SELECT * from fc
WHERE row_num=1

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

Method 2 - Using Sub-Query

%%sql
SELECT 
    customer_id,
    product_name
FROM (
    SELECT 
s.customer_id, m.product_name, ROW_NUMBER() OVER ( PARTITION BY mm.customer_id ORDER BY s.order_date) as row_num
FROM
dannys_diner.sales s 
JOIN 
dannys_diner.menu m
ON
m.product_id = s.product_id
JOIN
dannys_diner.members mm
ON
s.customer_id = mm.customer_id
WHERE
s.order_date > mm.join_date 
) AS fc
WHERE 
    row_num = 1;

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

7. Which item was purchased just before the customer became a member?

How to approach the Problem

1. Common Table Expression (CTE) - fc (First Purchase After Membership):

   • The CTE (fc) selects customer_id, product_name, and assigns a row number using ROW_NUMBER() over the partition of customer_id, ordered by order_date.

   • It joins the sales, menu, and members tables based on their respective columns (product_id, customer_id) and filters orders (sales) after the customer's join date in the membership table.

2. Main Query:

   • The main query selects all columns from the CTE fc where the row_num is 1, indicating the first purchase made by each customer after joining as a member.

Method 1 - Using Subquery

%%sql

SELECT customer_id, product_name
FROM (
    SELECT s.customer_id,m.product_name,s.order_date,
    ROW_NUMBER() OVER ( PARTITION BY s.customer_id ORDER BY s.order_date DESC) as row_num 
    FROM dannys_diner.sales s
    JOIN dannys_diner.menu m
    ON s.product_id = m.product_id
    JOIN dannys_diner.members mm 
    ON s.customer_id = mm.customer_id
    where s.order_date < mm.join_date
) as fc
where row_num = 1

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

Method 2 - Using CTE

%%sql

with fc as
(
SELECT 
s.customer_id, m.product_name, ROW_NUMBER() OVER ( PARTITION BY mm.customer_id ORDER BY s.order_date DESC) as row_num
FROM
dannys_diner.sales s 
JOIN 
dannys_diner.menu m
ON
m.product_id = s.product_id
JOIN
dannys_diner.members mm
ON
s.customer_id = mm.customer_id
WHERE
s.order_date < mm.join_date 
)

SELECT * from fc
WHERE row_num=1

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

8. What is the total items and amount spent for each member before they became a member?

How to Approach The Problem,

1. Select Necessary Columns:

   • Select customer_id, COUNT of product_id (total items), and SUM of price (total sales).

2. Determine Join Type:

   • Utilize JOIN operations between the sales, menu, and members tables based on their respective columns (product_id, customer_id).

3. Filter Orders Before Membership Start:

   • Use a WHERE clause to filter orders (sales table) where the order_date is less than the membership start date for each customer.

4. Aggregate Totals for Each Member:

   • Use COUNT to calculate the total number of items purchased (total_items) and SUM to compute the total amount spent (total_sales) for each customer before they became a member.

5. Group and Order Results:

   • Group the results by customer_id to aggregate totals for each member.

   • Optionally, use an ORDER BY clause to sort the results based on customer_id.

%%sql

SELECT s.customer_id, 
        COUNT(s.product_id) as total_items, 
        sum(m.price) as total_sales
FROM
dannys_diner.sales s
JOIN 
dannys_diner.menu m
ON
s.product_id = m.product_id
JOIN 
dannys_diner.members mm
ON
s.customer_id = mm.customer_id
WHERE
s.order_date < mm.join_date
GROUP BY s.customer_id
ORDER BY s.customer_id

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?

How to Approach the Problem

1. Common Table Expression (CTE) - cust_point:

   • Generates a set of points awarded for each product based on its price.

   • Applies a CASE statement to assign points, awarding 20 points per dollar for sushi (product_id = 1) and 10 points per dollar for other products.

2. Main Query:

   • Selects customer_id and calculates total points earned for each customer based on their purchases.

   • Joins the sales table with the cust_point CTE on the product_id to retrieve the points assigned to each product.

   • Groups the results by customer_id.

   • Sums up the points for each customer to calculate their total points earned.

3. Result:

   • Provides the total points earned by each customer considering their purchases according to the defined points allocation.

%%sql

WITH cust_point as(

    SELECT product_id, 
    CASE 
    WHEN product_id = 1 THEN price * 20 
    ELSE price * 10 END AS points
    FROM dannys_diner.menu
)

SELECT s.customer_id, sum(cp.points) as total_points
FROM dannys_diner.sales s
JOIN cust_point cp
ON s.product_id = cp.product_id
GROUP BY s.customer_id
ORDER BY s.customer_id

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

3 rows affected.

10. 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 customer A and B have at the end of January?

How to Approach The Problem

1. Common Table Expression (CTE) - offer_period:

   • Generates a timeframe (first week) after customers join the program, identified by join_date and valid_date.

   • Defines the last_date as the end of January.

2. Main Query:

   • Selects customer_id and calculates points earned for each customer based on their purchases.

   • Utilizes a CASE statement within the SUM function to differentiate points earned during the offer period and regular points earned outside this period.

     • Customers earn double points (2x) for all items purchased during the offer period (2 * 10 * m.price).

     • Regular points (1x) are given for items purchased outside the offer period (10 * m.price).

3. Joins:

   • Joins the sales table with the menu table on the product_id.

   • Joins the offer_period CTE based on the customer_id to filter orders within the specified time frame.

4. Grouping and Aggregation:

   • Groups the results by customer_id.

   • Calculates the total points earned based on the defined conditions.

%%sql

WITH offer_period AS (
  SELECT 
    customer_id, 
    join_date, 
    join_date + 6 AS valid_date,
    DATE_TRUNC('month', '2023-01-31'::DATE) + interval '1 month' - interval '1 day' AS last_date
  FROM 
    dannys_diner.members
)

SELECT s.customer_id,
SUM(CASE
WHEN s.order_date BETWEEN op.join_date and op.valid_date THEN 2 * 10 * m.price
ELSE 10 * m.price END
) AS points
FROM 
dannys_diner.sales s
JOIN dannys_diner.menu m
on s.product_id = m.product_id
JOIN offer_period op
on s.customer_id = op.customer_id
WHERE
op.join_date <=s.order_date
AND 
s.order_date <= op.last_date
GROUP BY s.customer_id

Running query in 'postgresql://postgres:***@127.0.0.1:5432/ka..

2 rows affected.

End Notes

Thank you for taking the time to explore Danny's Diner and their data-driven journey. I hope you found the article both enjoyable and informative!

If you're eager to delve deeper into the technical side, check out the GitHub repository housing the SQL scripts used in this case study: Danny's Diner. Feel free to explore and utilize these resources.

Let's continue the conversation! Connect with me on LinkedIn to discuss all things related to data analysis, customer insights, or any other related topics: Nithish Singh. I'm always open to engaging discussions and collaborations in the realm of data-driven decision-making.

Feedback and Discussion:

• Did the article provide valuable insights and engage your interest?

• What other data-driven challenges do you envision Danny's Diner might encounter in the future?

• Share your thoughts, queries, and comments in the section below! Your input fuels enriching discussions.

Stay tuned for more thrilling data-fueled adventures! Keep an eye out for upcoming articles and explorations into the world of data-driven strategies.

I'm continuously eager to learn and enhance my content, so please don't hesitate to share your feedback and suggestions. Thank you once again for your readership and interest!