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 they've spent
• which menu items are their favorite.

Having this deeper connection with his customers will help him deliver a better and more personalized 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 shared with you 3 key datasets for this study along with the entity relationship diagram.

• sales
• menu
• member

Table relationship

Case Study Questions

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?

Solution:

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

First, I joined the sales and menu table through the product_id column. Then, I used the GROUP BY and SUM function to calculate the total amount spent by each customer.

Customer A spent $76.

Customer B spent $74.

Customer C spent $36.

How many days has each customer visited the restaurant?

I used the COUNT function in conjunction with DISTINCT to calculate the number of days each customer visited the restaurant. Moreover, the DISTINCT function allows us to only count once if a customer visits the restaurant multiple times in the same day.

Customer A visited 4 times.

Customer B visited 6 times.

Customer C visited 2 times.

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

I used common table expression (CTE) for this particular question as well as the RANK () and OVER (PARTITION BY ORDER BY) to create a column that is ranked based on order_date.

Moreover, I grouped the results by customer_id and product_name:

Thus, Customer A's first order was curry and sushi, Customer B was curry and Customer C ordered ramen the first time around.

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

Ramen is the most purchased item on the menu and it was purchased 8 times.

Which item was the most popular for each customer?

I used common table expression (CTE) as well as RANK () and OVER (PARTITION BY ORDER BY) to create a column that is ranked based on COUNT(order_date) in descending fashion.

Then, I create a SELECT statement in which I filter the results where rnk = 1

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

In this CTE, I filter the order_date where it is on or after join_date. Then, used RANK () and OVER (PARTITION BY ORDER BY) to create a column that is ranked based on order_date.

To tidy things up,

Customer A ordered curry after he became a member while customer B ordered sushi.

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

I create a column rank by partitioning customer_id and arranging the order_date in descending fashion (latest date to earliest). Then, I filter the order_date < join_date.

Finally, I created a Select statement that shows the last item ordered by a customer before becoming a member.

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

I filter the order_date before the join_date. Then, I COUNT the product_name and SUM the total prices spent before becoming a member.

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

To sum up the question,

• Each $1 spent = 10 points.
• But, product_name = 'sushi' get 2x points, meaning each $1 spent = 20 points. Thus, we will need to use CASE WHEN to create conditional statements.
• All other product that is not sushi, multiply $1 by 10 points.

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?

Customer A has 1370 points while Customer B has 820 by the end of January.