Danny’s Diner — A SQL Culinary Adventure

The details for the case study presented in this article have been referenced from the following source: 8 Week SQL Challenge — Case Study #1.

Ah, fellow culinary adventurers, gather ‘round! our friend, Danny, is a passionate soul who, in 2021, dared to transform his love for Japanese cuisine into a cozy little restaurant that sells his 3 favorite foods: sushi, curry, and ramen.

The kitchen is sizzling, but Danny’s Diner faces a challenge. Despite capturing some essential data in their few months of operation, the restaurant needs help to turn this data into the secret ingredient that keeps their business running.

Let us cook!

Unlocking Insights

Danny has a desire to extract meaningful insights from the data he’s collected about his customers. He’s particularly interested in understanding their:

• visit patterns,
• spending habits,
• and favorite menu items.

He imagines utilizing the acquired insights to make choices, including the potential expansion of the current customer loyalty program. Danny has provided partial customer data, presenting three main datasets:

• sales,
• menu,
• and members.

Entity Relational Diagram

Danny said to feel free to examine the entity relationship diagram below.

Entity Relational Diagram

Exploring Culinary Insights

We’re going to cook up these insights using SQLite.

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

SELECT
 customer_id,
 SUM(price) as total_amount 
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
GROUP by customer_id

This query joins the “sales” and “menu” tables on the product_id and then groups the results by customer_id, summing up the total amount.

Observing the outcomes,

• customer A spent $76,
• customer B spent $74,
• and customer C spent $36 in total.

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

SELECT
 customer_id,
 COUNT(DISTINCT order_date) as total_days
FROM sales
GROUP BY customer_id

This query counts the distinct order dates for each customer, so it doesn’t count duplicates and then groups the results by customer ID.

Observing the outcomes,

• customer A visited for 4 days,
• customer B for 6 days,
• and customer C for 2 days.

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

SELECT
    s.customer_id,
    m.product_name AS first_purchase
FROM (
  SELECT
   customer_id,
   MIN(order_date) AS first_order_date
  FROM sales
  GROUP BY customer_id
) AS first_orders
INNER JOIN sales s ON s.customer_id = first_orders.customer_id AND s.order_date = first_orders.first_order_date
INNER JOIN menu m ON s.product_id = m.product_id;

This query finds the minimum order date for each customer using SQL subqueries and then matches it with the corresponding product from the menu.

Observing the outcomes,

• customer A’s first purchases were sushi and curry,
• customer B’s was curry,
• and customer C’s was ramen.

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

SELECT
 m.product_name,
 COUNT(s.product_id) as times_purchased
FROM sales s
INNER join menu m ON s.product_id = m.product_id
GROUP BY m.product_id
ORDER by times_purchased desc
LIMIT 1;

This query joins the sales and menu tables, groups the results by product ID, counts the occurrences, and then sorts them in descending order. The LIMIT 1 ensures only the top result is retrieved.

In this case, ramen holds the top spot with 8 purchases. Yum!

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

WITH cte AS (
  SELECT
    s.customer_id,
    m.product_name,
    COUNT(s.product_id) AS order_count,
    RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS product_rank
  FROM sales s
  JOIN menu m ON s.product_id = m.product_id
  GROUP BY
    s.customer_id,
    m.product_name
)

SELECT
 customer_id,
    product_name AS most_popular_item
FROM cte
WHERE product_rank = 1;

This query employs a common table expression (CTE) to count the occurrences of each product, ranking them based on popularity. The final result selects the top-ranked item as the most popular for each customer.

The outcomes show that their most popular item is,

• for customer A, it’s ramen;
• for customer B, it’s ramen, sushi, and curry;
• and for customer C, it’s ramen.

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

SELECT 
    s.customer_id,
    product_name AS first_menu
FROM sales s
inner JOIN members mb on s.customer_id = mb.customer_id
INNER join menu m on s.product_id = m.product_id
where s.order_date >= mb.join_date
GROUP by s.customer_id
HAVING s.order_date =  MIN(s.order_date);

This query joins the sales, members, and menu tables, filtering for purchases made on or after the customer’s join date. The results are grouped by customer id, and the HAVING clause ensures that only entries with the earliest order date are included.

The outcomes show that their first menu after becoming a member,

• for customer A, it was curry,
• and for customer B, it was sushi.
• customer C is not included as they are not a member.

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

WITH latest_purchase AS (
  SELECT
    s.customer_id,
    m.product_name,
    RANK() OVER (PARTITION BY s.customer_id ORDER BY s.order_date DESC) AS purchase_rank
  FROM
    sales s
    INNER JOIN menu m ON s.product_id = m.product_id
    INNER JOIN members mb ON s.customer_id = mb.customer_id
  WHERE
    s.order_date < mb.join_date
)

SELECT customer_id, product_name AS item_purchased_just_before_join
FROM latest_purchase
WHERE purchase_rank = 1;

This query utilizes a common table expression (CTE) named latest_purchase to rank items based on order date in descending order for each customer.

The final result selects the top-ranked item for each customer whose purchase occurred just before they became a member.

In the outcomes,

• for customer A, it was sushi and curry.

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

SELECT
   s.customer_id,
   COUNT(s.product_id) as total_items,
   SUM(m.price) as total_amount
   
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
INNER JOIN members mb ON s.customer_id = mb.customer_id
WHERE s.order_date < mb.join_date
GROUP by s.customer_id

This query joins the sales, menu, and members tables, filters for transactions that occurred before the customer’s join date, and then aggregates the count of products and the sum of prices for each customer.

Observing the outcomes,

• for customer A, the total number of items is 2, and the total amount spent is $25.
• for customer B, the total number of items is 3, and the total amount spent is $40.

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

SELECT
    s.customer_id,
    SUM(
     CASE
        WHEN m.product_id = 1 THEN 2*10*m.price --2x point for sushi
        ELSE 10*m.price
       END
    ) as total_point
   
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
GROUP by s.customer_id

This query utilizes a CASE statement to apply a 2x points multiplier for the product with ID 1 (sushi) and a regular 1x multiplier for other products. The total points are the sum of these calculated values for each customer.

Observing the outcomes,

• customer A has 860 points,
• customer B has 940 points,
• and customer C has 360 points.

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 count_points AS (
    SELECT
      s.customer_id,
      order_date,
      join_date,
      product_name,
      SUM(point) AS point
    FROM
      sales AS s
      JOIN (
        SELECT
          product_id,
          product_name,
          CASE
            WHEN product_name = 'sushi' THEN price * 20
            ELSE price * 10
          END AS point
        FROM
          menu AS m
      ) AS p ON s.product_id = p.product_id
      JOIN members AS mb ON s.customer_id = mb.customer_id
    GROUP BY
      s.customer_id,
      order_date,
      join_date,
      product_name,
      point
  )
SELECT
  customer_id,
  SUM(
    CASE
      WHEN order_date >= join_date
      AND order_date < DATE(join_date, '+7 days') -- SQLite date arithmetic
      AND product_name != 'sushi' THEN point * 2
      ELSE point
    END
  ) AS new_points
FROM count_points
WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY 1
ORDER BY 1

This query calculates the points for customers A and B by considering a 2x points multiplier for all items in the first week after they join the program.

The calculation involves joining the sales, menu, and members tables and applying conditional logic for point assignment. Finally, it filters results for January 2021 and sums the points for each customer.

According to the outcomes, at the end of January,

• customer A had 1370 points,
• and customer B had 820 points.

Whew, we’ve completed our culinary exploration, but now, onto some bonus round!

Joining All The Essential Ingredients

These questions are like crafting essential prep stations — basic data tables that Danny and his team can use to swiftly whip up insights without the urge to use SQL.

SELECT
 customer_id,
    order_date,
    product_name,
    price
FROM sales s
INNER join menu m on s.product_id = m.product_id
order by customer_id, order_date, m.price DESC

This query combines information from the sales and menu tables, displaying customer ID, order date, product name, and price.

Following Danny’s instructions, the results are sorted in descending order by customer ID, order date, and menu price.

Ranking Culinary Adventures

In our journey, Danny’s calling for more details on ranking customer products. Here’s the twist — Danny only wants the rankings for loyal customers. So, we’re expecting some ‘null’ rankings for our new culinary enthusiasts who haven’t joined the loyalty program.

WITH cte AS (
  SELECT
 s.customer_id,
    s.order_date,
    m.product_name,
    m.price,
    CASE
     WHEN s.order_date >= mb.join_date THEN 'Y'
     ELSE 'N'
    END AS member
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
INNER JOIN members mb ON s.customer_id = mb.customer_id
ORDER BY s.customer_id, s.order_date, m.price DESC
)

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

This query utilizes a common table expression (CTE) to organize the data. It then brings in the RANK() window function to assign rankings, crafting a final output that reveals customer ID, order date, product name, price, membership status, and their corresponding rankings.

Ah, dear culinary adventurers, Armed with SQL skills, we learned about the spending habits of our guests — customer A and C favor the richness of ramen, while customer B delight in the trio of ramen, sushi, and curry equally.

Our culinary canvas expanded as we crowned ramen the champion, earning the most purchases.

But the adventure didn’t stop there! Bonus rounds led us to create efficient data tables, simplifying Danny’s quest for insights. We united scattered details and ranked customer choices, reserving the prestige of the loyalty program for initiated members.

May your SQL queries be as precise as sushi rolls, and your data adventures as fulfilling as a bowl of ramen under Tokyo’s moonlight.

Arigatou gozaimasu and happy SQL cooking! 🍣🍜🍛