8 Week SQL Challenge: Case Study #1 — Danny’s Diner

I’ve challenged myself to join this excellent case study by Danny Ma which you can find it here.

And I also uploaded the syntax on my Github, here is the link if you want to check it out.

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 favorite 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 favorite. 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

You can inspect the entity-relationship diagram and example data below.

Case Study Question

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

Solution

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

I used the aggregate function SUM to find out total amount spent by the customers and don’t forget to use GROUP BY to function if we use the aggregate function. Also, I used the JOIN function to join the value that matches at both tables (sales and menu). In this case, we joined the product_id between two tables to find out the total amount of each customer spent at the restaurant.

This is the result of the total amount each customer spent at the restaurant,

• Customer A spent $76.
• Customer B spent $84.
• Customer C spent $36.

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

I used COUNT DISTINCT to make sure we don’t count the repetitive order_date for the visits of the customers to the restaurant. For example, if the customer A visited the restaurant twice on ‘2021–01–01’, we counted it once instead of twice.

• Customer A visited the restaurant 4 times.
• Customer B visited the restaurant 6 times.
• Customer C visited the restaurant 2 times.

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

Here, I used DENSE_RANK() instead of RANK() or ROW_NUMBER() because I want to see the result as the same rank if they ordered different items on the same date because we don’t know which item is ordered first. Hence, we put the WHERE clause to see rank = 1 and then use GROUP BY to show only rank = 1.

• The first items purchased by Customer A are curry and sushi.
• The first items purchased by Customer B is curry.
• The first items purchased by Customer C is ramen.

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

In MySQL, we use the LIMIT clause to specify the number of records to return. In this case, we want to see the most purchased item on the menu, so we use LIMIT 1 to return only the most purchased item.

The most purchased item is ramen which has been purchased 8 times.

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

We use DENSE_RANK() to rank the numbers of orders for each item for each customer. Then, we use the WHERE clause to get items only rank = 1.

• Customer A’s favorite is ramen.
• Customer B’s favorites are curry, ramen, and sushi.
• Customer C’s favorite is ramen.

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

With CTE we get a rank the member by order date because we want to see what items they have purchased after they became a member. After we created a CTE, we can join the CTE to get what items they purchased by joining CTE table and menu table with product_id.

• Customer’s A first purchased after became a member was curry.
• Customer’s B first purchased after became a member was sushi.

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

The code is pretty similar to the previous code. in this case, we want to know which item was purchased before the customer became a member, so we just change the operator from the order date is greater than or equal to join date in the previous code to order date is less than the join date.

• Customer A’s purchased items before becoming a member are curry and sushi.
• Customer B’s purchased item before became a member is sushi.

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

We can use the COUNT clause to count the purchased items, and the SUM clause to get a total sum of the price of the purchased items before they became a member.

• Customer’s A purchased items were 2 sushi with $25 spent.
• Customer’s B purchased items were 3 curry with $40 spent.

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

Again, we use CTE which we called “price_point” to use the CASE statement to create a certain condition. In this case, we multiply the price by 20 for sushi and 10 for others. Then, we join the price point table with the sales table to sum the point by the amount they spent.

• Customer A got 860 points.
• Customer B got 940 points.
• Customer C got 360 points.

Edit: Shout out to Dhyanendra Singh Rathore for sharing his feedback on this post. I should’ve counted the points after they’ve become members which you can see the query below.

• Customer A got 860 points.
• Customer B got 700 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 customers A and B have at the end of January?

First of all, we create CTE which we called dates to call the first week after a customer joins the program as firstweek_join and the end of January as last_date. Then, we joined the dates table with the sales table to get the details of customer id, join date, customer’s date of the first week they joined the program, the end of January as last_date. We can use a CASE statement to create a specific condition which in this case the customer will earn 2x points on all items. These columns are joined on customer id in the dates table and sales table. Then, we joined the sales table and menu table to get the product name and price on product_id.

• Customer A’s point by the end of January is 1370 points
• Customer B’s point by the end of January is 700

Bonus Questions

1. Join all the things to recreate the table with: customer_id, order_date, product_name, price, member [Y/N].

2. Danny also requires further information about the ranking of customer products, but he doesn’t need the ranking for non-members so he expects null ranking values for the records when customers are not part of the membership program.

Insights

From the analysis, we have discovered that would meet Danny’s expectations.

• Customer A is the top spender at the restaurant, while customer B is the most frequent visitor with 6 visits in January 2021.
• The first items that customer A purchased were sushi and curry, customer B was curry, and customer C was ramen. But, the most purchase item is ramen with 8 times purchases.
• Customer A and C love ramen, but customer B enjoys sushi, ramen, and curry.
• The first item that purchased after became a member was curry for Customer A, and sushi for Customer B. And before they became a member the items that Customer A purchased are curry and sushi, and Customer B purchased sushi.
• Before became a member, Customer A spent $25 and Customer B spent $40.
• After a week they became a member until the end of January, Customer A got 1370 points and Customer B got 700 points.

These insights can help Danny to treat the customers on delivering a better and more personalized experience for his loyal customers, and also to help him expand the loyalty program.

I would be glad if you join this challenge too! If you have any feedback on my analysis, please leave your thoughts on the comment below. Happy coding folks!