🍜 Case Study #1: Danny’s Diner

9 min readMar 17, 2024

📚 Table of Contents

Business Task

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.

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.

Question and Solution

Please join me in executing the queries using PostgreSQL on DB Fiddle. It would be great to work together on the questions!

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

SELECT 
  sales.customer_id, 
  SUM(menu.price) AS total_sales
FROM dannys_diner.sales
INNER JOIN dannys_diner.menu
  ON sales.product_id = menu.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id ASC;

Steps:

Use JOIN to merge dannys_diner.sales and dannys_diner.menu tables as sales.customer_id and menu.price are from both tables.
Use SUM to calculate the total sales contributed by each customer.
Group the aggregated results by sales.customer_id.

Answer:

customer_idtotal_salesA76B74C36

Customer A spent $76.
Customer B spent $74.
Customer C spent $36.

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

SELECT 
  customer_id, 
  COUNT(DISTINCT order_date) AS visit_count
FROM dannys_diner.sales
GROUP BY customer_id;

Steps:

To determine the unique number of visits for each customer, utilize COUNT(DISTINCT order_date).
It’s important to apply the DISTINCT keyword while calculating the visit count to avoid duplicate counting of days. For instance, if Customer A visited the restaurant twice on ‘2021–01–07’, counting without DISTINCT would result in 2 days instead of the accurate count of 1 day.

Answer:

customer_idvisit_countA4B6C2

Customer A visited 4 times.
Customer B visited 6 times.
Customer C visited 2 times.

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

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;

Steps:

Create a Common Table Expression (CTE) named ordered_sales_cte. Within the CTE, create a new column rank and calculate the row number using DENSE_RANK() window function. The PARTITION BY clause divides the data by customer_id, and the ORDER BY clause orders the rows within each partition by order_date.
In the outer query, select the appropriate columns and apply a filter in the WHERE clause to retrieve only the rows where the rank column equals 1, which represents the first row within each customer_id partition.
Use the GROUP BY clause to group the result by customer_id and product_name.

Answer:

customer_idproduct_nameAcurryAsushiBcurryCramen

Customer A placed an order for both curry and sushi simultaneously, making them the first items in the order.
Customer B’s first order is curry.
Customer C’s first order is ramen.

I have received feedback suggesting the use of ROW_NUMBER() instead of DENSE_RANK() for determining the "first order" in this question.

However, since the order_date does not have a timestamp, it is impossible to determine the exact sequence of items ordered by the customer.

Therefore, it would be inaccurate to conclude that curry is the customer’s first order purely based on the alphabetical order of the product names. For this reason, I maintain my solution of using DENSE_RANK() and consider both curry and sushi as Customer A's first order.

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

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;

Steps:

Perform a COUNT aggregation on the product_id column and ORDER BY the result in descending order using most_purchased field.
Apply the LIMIT 1 clause to filter and retrieve the highest number of purchased items.

Answer:

most_purchasedproduct_name8ramen

Most purchased item on the menu is ramen which is 8 times. Yummy!

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

WITH most_popular AS (
  SELECT 
    sales.customer_id, 
    menu.product_name, 
    COUNT(menu.product_id) AS order_count,
    DENSE_RANK() OVER (
      PARTITION BY sales.customer_id 
      ORDER BY COUNT(sales.customer_id) DESC) AS rank
  FROM dannys_diner.menu
  INNER JOIN dannys_diner.sales
    ON menu.product_id = sales.product_id
  GROUP BY sales.customer_id, menu.product_name
)

SELECT 
  customer_id, 
  product_name, 
  order_count
FROM most_popular 
WHERE rank = 1;

Each user may have more than 1 favourite item.

Steps:

Create a CTE named fav_item_cte and within the CTE, join the menu table and sales table using the product_id column.
Group results by sales.customer_id and menu.product_name and calculate the count of menu.product_id occurrences for each group.
Utilize the DENSE_RANK() window function to calculate the ranking of each sales.customer_id partition based on the count of orders COUNT(sales.customer_id) in descending order.
In the outer query, select the appropriate columns and apply a filter in the WHERE clause to retrieve only the rows where the rank column equals 1, representing the rows with the highest order count for each customer.

Answer:

customer_idproduct_nameorder_countAramen3Bsushi2Bcurry2Bramen2Cramen3

Customer A and C’s favourite item is ramen.
Customer B enjoys all items on the menu. He/she is a true foodie, sounds like me.

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

WITH joined_as_member AS (
  SELECT
    members.customer_id, 
    sales.product_id,
    ROW_NUMBER() OVER (
      PARTITION BY members.customer_id
      ORDER BY sales.order_date) AS row_num
  FROM dannys_diner.members
  INNER JOIN dannys_diner.sales
    ON members.customer_id = sales.customer_id
    AND sales.order_date > members.join_date
)

SELECT 
  customer_id, 
  product_name 
FROM joined_as_member
INNER JOIN dannys_diner.menu
  ON joined_as_member.product_id = menu.product_id
WHERE row_num = 1
ORDER BY customer_id ASC;

members.join_date ) SELECT customer_id, product_name FROM joined_as_member INNER JOIN dannys_diner.menu ON joined_as_member.product_id = menu.product_id WHERE row_num = 1 ORDER BY customer_id ASC;” tabindex=”0" role=”button” style=”box-sizing: border-box; position: relative; font-size: 14px; font-weight: var( — base-text-weight-medium, 500); line-height: 20px; white-space: nowrap; vertical-align: middle; cursor: pointer; border: 0px; border-top-left-radius: 6px; border-top-right-radius: 6px; border-bottom-right-radius: 6px; border-bottom-left-radius: 6px; color: var( — fgColor-accent, var( — color-accent-fg)); background-color: transparent; box-shadow: none; transition: color 80ms cubic-bezier(0.33, 1, 0.68, 1) 0s, background-color, box-shadow, border-color; width: var( — control-small-size, 28px); height: var( — control-small-size, 28px); display: flex !important; padding: 0px !important; justify-content: center !important; align-items: center !important; margin: var( — base-size-8, 8px) !important;”>

Steps:

Create a CTE named joined_as_member and within the CTE, select the appropriate columns and calculate the row number using the ROW_NUMBER() window function. The PARTITION BY clause divides the data by members.customer_id and the ORDER BY clause orders the rows within each members.customer_id partition by sales.order_date.
Join tables dannys_diner.members and dannys_diner.sales on customer_id column. Additionally, apply a condition to only include sales that occurred after the member's join_date (sales.order_date > members.join_date).
In the outer query, join the joined_as_member CTE with the dannys_diner.menu on the product_id column.
In the WHERE clause, filter to retrieve only the rows where the row_num column equals 1, representing the first row within each customer_id partition.
Order result by customer_id in ascending order.

Answer:

customer_idproduct_nameAramenBsushi

Customer A’s first order as a member is ramen.
Customer B’s first order as a member is sushi.

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

WITH purchased_prior_member AS (
  SELECT 
    members.customer_id, 
    sales.product_id,
    ROW_NUMBER() OVER (
      PARTITION BY members.customer_id
      ORDER BY sales.order_date DESC) AS rank
  FROM dannys_diner.members
  INNER JOIN dannys_diner.sales
    ON members.customer_id = sales.customer_id
    AND sales.order_date < members.join_date
)

SELECT 
  p_member.customer_id, 
  menu.product_name 
FROM purchased_prior_member AS p_member
INNER JOIN dannys_diner.menu
  ON p_member.product_id = menu.product_id
WHERE rank = 1
ORDER BY p_member.customer_id ASC;

Steps:

Create a CTE called purchased_prior_member.
In the CTE, select the appropriate columns and calculate the rank using the ROW_NUMBER() window function. The rank is determined based on the order dates of the sales in descending order within each customer’s group.
Join dannys_diner.members table with dannys_diner.sales table based on the customer_id column, only including sales that occurred before the customer joined as a member (sales.order_date < members.join_date).
Join purchased_prior_member CTE with dannys_diner.menu table based on product_id column.
Filter the result set to include only the rows where the rank is 1, representing the earliest purchase made by each customer before they became a member.
Sort the result by customer_id in ascending order.

Answer:

customer_idproduct_nameAsushiBsushi

Both customers’ last order before becoming members are sushi.

8. 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_sales
FROM dannys_diner.sales
INNER JOIN dannys_diner.members
  ON sales.customer_id = members.customer_id
  AND sales.order_date < members.join_date
INNER JOIN dannys_diner.menu
  ON sales.product_id = menu.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id;

Steps:

Select the columns sales.customer_id and calculate the count of sales.product_id as total_items for each customer and the sum of menu.price as total_sales.
From dannys_diner.sales table, join dannys_diner.members table on customer_id column, ensuring that sales.order_date is earlier than members.join_date (sales.order_date < members.join_date).
Then, join dannys_diner.menu table to dannys_diner.sales table on product_id column.
Group the results by sales.customer_id.
Order the result by sales.customer_id in ascending order.

Answer:

customer_idtotal_itemstotal_salesA225B340

Before becoming members,

Customer A spent $25 on 2 items.
Customer B spent $40 on 3 items.

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

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

SELECT 
  sales.customer_id, 
  SUM(points_cte.points) AS total_points
FROM dannys_diner.sales
INNER JOIN points_cte
  ON sales.product_id = points_cte.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id;

Steps:

Let’s break down the question to understand the point calculation for each customer’s purchases.

Each $1 spent = 10 points. However, product_id 1 sushi gets 2x points, so each $1 spent = 20 points.
Here’s how the calculation is performed using a conditional CASE statement:
If product_id = 1, multiply every $1 by 20 points.
Otherwise, multiply $1 by 10 points.
Then, calculate the total points for each customer.

Answer:

customer_idtotal_pointsA860B940C360

Total points for Customer A is $860.
Total points for Customer B is $940.
Total points for Customer C is $360.

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?

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

SELECT 
  sales.customer_id, 
  SUM(CASE
    WHEN menu.product_name = 'sushi' THEN 2 * 10 * menu.price
    WHEN sales.order_date BETWEEN dates.join_date AND dates.valid_date THEN 2 * 10 * menu.price
    ELSE 10 * menu.price END) AS points
FROM dannys_diner.sales
INNER JOIN dates_cte AS dates
  ON sales.customer_id = dates.customer_id
  AND dates.join_date <= sales.order_date
  AND sales.order_date <= dates.last_date
INNER JOIN dannys_diner.menu
  ON sales.product_id = menu.product_id
GROUP BY sales.customer_id;

Assumptions:

On Day -X to Day 1 (the day a customer becomes a member), each $1 spent earns 10 points. However, for sushi, each $1 spent earns 20 points.
From Day 1 to Day 7 (the first week of membership), each $1 spent for any items earns 20 points.
From Day 8 to the last day of January 2021, each $1 spent earns 10 points. However, sushi continues to earn double the points at 20 points per $1 spent.

Steps:

Create a CTE called dates_cte.
In dates_cte, calculate the valid_date by adding 6 days to the join_date and determine the last_date of the month by subtracting 1 day from the last day of January 2021.
From dannys_diner.sales table, join dates_cte on customer_id column, ensuring that the order_date of the sale is after the join_date (dates.join_date <= sales.order_date) and not later than the last_date (sales.order_date <= dates.last_date).
Then, join dannys_diner.menu table based on the product_id column.
In the outer query, calculate the points by using a CASE statement to determine the points based on our assumptions above.
If the product_name is 'sushi', multiply the price by 2 and then by 10. For orders placed between join_date and valid_date, also multiply the price by 2 and then by 10.
For all other products, multiply the price by 10.
Calculate the sum of points for each customer.

Answer:

customer_idtotal_pointsA1020B320

Total points for Customer A is 1,020.
Total points for Customer B is 320.

BONUS QUESTIONS

Join All The Things

Recreate the table with: customer_id, order_date, product_name, price, member (Y/N)

SELECT 
  sales.customer_id, 
  sales.order_date,  
  menu.product_name, 
  menu.price,
  CASE
    WHEN members.join_date > sales.order_date THEN 'N'
    WHEN members.join_date <= sales.order_date THEN 'Y'
    ELSE 'N' END AS member_status
FROM dannys_diner.sales
LEFT JOIN dannys_diner.members
  ON sales.customer_id = members.customer_id
INNER JOIN dannys_diner.menu
  ON sales.product_id = menu.product_id
ORDER BY members.customer_id, sales.order_date

sales.order_date THEN ’N’ WHEN members.join_date

Answer:

customer_idorder_dateproduct_namepricememberA2021–01–01sushi10NA2021–01–01curry15NA2021–01–07curry15YA2021–01–10ramen12YA2021–01–11ramen12YA2021–01–11ramen12YB2021–01–01curry15NB2021–01–02curry15NB2021–01–04sushi10NB2021–01–11sushi10YB2021–01–16ramen12YB2021–02–01ramen12YC2021–01–01ramen12NC2021–01–01ramen12NC2021–01–07ramen12N

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 customers_data AS (
  SELECT 
    sales.customer_id, 
    sales.order_date,  
    menu.product_name, 
    menu.price,
    CASE
      WHEN members.join_date > sales.order_date THEN 'N'
      WHEN members.join_date <= sales.order_date THEN 'Y'
      ELSE 'N' END AS member_status
  FROM dannys_diner.sales
  LEFT JOIN dannys_diner.members
    ON sales.customer_id = members.customer_id
  INNER JOIN dannys_diner.menu
    ON sales.product_id = menu.product_id
)

SELECT 
  *, 
  CASE
    WHEN member_status = 'N' then NULL
    ELSE RANK () OVER (
      PARTITION BY customer_id, member_status
      ORDER BY order_date
  ) END AS ranking
FROM customers_data;

sales.order_date THEN ’N’ WHEN members.join_date

Answer:

customer_idorder_dateproduct_namepricememberrankingA2021–01–01sushi10NNULLA2021–01–01curry15NNULLA2021–01–07curry15Y1A2021–01–10ramen12Y2A2021–01–11ramen12Y3A2021–01–11ramen12Y3B2021–01–01curry15NNULLB2021–01–02curry15NNULLB2021–01–04sushi10NNULLB2021–01–11sushi10Y1B2021–01–16ramen12Y2B2021–02–01ramen12Y3C2021–01–01ramen12NNULLC2021–01–01ramen12NNULLC2021–01–07ramen12NNULL

🍜 Case Study #1: Danny’s Diner

📚 Table of Contents

Business Task

Entity Relationship Diagram

Example Datasets

Table 1: sales

Table 2: menu

Table 3: members

Question and Solution

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Steps:

Answer:

Assumptions:

Steps:

Answer:

BONUS QUESTIONS

Answer:

Answer:

Written by Mahnoor Fatima