8-Week SQL Challenge, Case Study #1 by Danny Ma

This case study centers on analyzing data to give meaningful insights on customer behavior and overall business performance in Danny’s Dinner

TOOLS USED FOR ANALYSIS: SQL

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.

ABOUT THE DATASET

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

• sales
• menu
• members

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.

ENTITY RELATIONSHIP DIAGRAM

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.

CASE STUDY:

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?

SOLUTION

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

SELECT customer_id, sum(price) as totalprice
FROM sales
JOIN menu
ON sales.product_id = menu.product_id
GROUP BY customer_id;

Customer A spent 76 being the highest, Customer B spent 74, while Customer C spent 36 being the lowest

2. How many days has each customer visited the restaurant

SELECT customer_id, count(distinct(order_date)) as number_of_days
FROM sales
GROUP BY customer_id;

Customer B has the highest number of visiting days (5 days), while Customer C has the least numbers of visiting days (2days)

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

WITH first_purchase AS
(SELECT product_id, product_name
FROM menu)
SELECT customer_id, product_name, min(order_date) AS first_order_date
FROM sales s
JOIN first_purchase f
ON s.product_id =f.product_id
GROUP BY customer_id,product_name
ORDER BY customer_id asc, min(order_date) asc;

Customer A purchased Curry and Sushi, Customer B purchased Ramen and Curry, Customer C purchased only Ramen

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

SSELECT product_name, SUM(number_of_item) AS Total_no_sold
        FROM(
   SELECT count(product_name) as number_of_item, product_name, customer_id
   FROM sales s
   JOIN menu m
   ON s.product_id = m.product_id
   GROUP BY customer_id, m.product_id, product_name) items
GROUP BY product_name
ORDER BY total_no_sold DESC;

Ramen is the most purchased product, and it has been purchased 8 times

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

SELECT customer_id,product_name,count(product_name) as most_popular_item
FROM menu m
ON m.product_id =s.product_id
GROUP BY customer_id,product_name
ORDER BY customer_id desc, count(product_name) desc;

Customer A and C most purchased item was Ramen, while Customer purchased all three items the same number of times

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

SELECT ms.customer_id,product_name,order_date,join_date
FROM menu m
JOIN sales s
ON m.product_id =s.product_id
JOIN members ms
ON ms.customer_id =s.customer_id
WHERE order_date >join_date;

Only Customer A and B have loyalty card. After becoming a member, Customer A purchased Ramen, while Customer B purchased Sushi

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

SELECT ms.customer_id,product_name,order_date,join_date
FROM menu m
JOIN sales s
ON m.product_id =s.product_id
JOIN members ms
ON ms.customer_id = s.customer_id
WHERE order_date <join_date

Customer A became a member 2021–01–07, while Customer B became a member 2021–01–09. Customer A purchased Sushi and Ramen, while Customer B purchased Curry, Sushi, and Ramen

8. What is the total item and amount spent by each customer before they became a member?

SELECT s.customer_id,count(product_name) as total_item, sum(price) as total_amount, order_date,join_date
FROM menu m
JOIN sales s
ON m.product_id =s.product_id
JOIN members ms
ON ms.customer_id =s.customer_id
WHERE order_date <join_date
GROUP BY s.customer_id,order_date,join_date

Customer A purchased a total of 2 items, and spent an amount of 25 while Customer B purchased a total of 4 items, and spent a total amount of 52.

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

SELECT customer_id,
        sum(CASE WHEN product_name ='sushi' THEN price *20 ELSE price *10 END) AS total_points
FROM sales s
JOIN menu m
ON s.product_id =m.product_id
GROUP BY customer_id;

In total, Customer A will have a point of 860, Customer B will have the highest point of 940,while Customer C will have a point of 360

10. In the first week after a customer joins the program(including their join date), they earn 2x on all their items, not just sushi-how many points do customer A and B have by the end of January?

WITH first_week_order AS
    (SELECT ms.customer_id, order_date, join_date, SUM(price*20) AS total_ppoints
    FROM menu m
    JOIN sales s
    ON m.product_id =s.product_id
    JOIN members ms
    ON ms.customer_id =s.customer_id 
    WHERE order_date BETWEEN '2021-01-07' AND '2021-01-14' OR order_date BETWEEN '2021-01-09' AND '2021-01-16'
    GROUP BY ms.customer_id,order_date,join_date)
SELECT fw.customer_id, fw.order_date,total_points
FROM first_week_order fw
LEFT JOIN (SELECT s.customer_id,s.order_date, SUM(price*10) AS Final_price
          FROM menu m
          JOIN sales s
    ON m.product_id = s.product_id
          JOIN members ms
    ON ms.customer_id =s.customer_id
    WHERE s.order_date BETWEEN '2021-01-14' AND '2021-01-31' AND s.order_date  BETWEEN '2021-01-16' AND '2021-01-31'
          GROUP BY s.customer_id,s.order_date) AS sub
ON fw.customer_id =sub.customer_id

At the end of January, Customer A has a total price of 1020, while Customer B has a total price of 440

FINDINGS/INSIGHTS.

1. Ramen is the most purchased item while Sushi is the least purchased item.
2. Customer C does not have a membership card, unlike Customer A and Customer B. Customer C also has a few visiting days at the restaurant which might be as a result of the Customer having only one preference for a product. Ramen is the only product Customer C purchased.

RECOMMENDATIONS.

1. Ramen seems to be Customer C’s favorite item, however, customer survey data should be collected and data-driven questions should be made to understand why customer C patronized just once. Other factors like Customer services could be related.
2. Before becoming a member , Ramen was purchased by the customers , after obtaining the membership card, each customers purchased a different product. The customer survey data should be further analyzed to help provide more insights into the customer experience.
3. To increase the purchase of the least popular item like Sushi, strategic marketing campaigns. Discounts, or adjusting the menu can be used in attracting customers to try these products which will help boost their popularity and purchase.

CONCLUSION.

The case study helped in uncovering insights about the customers behavior and these insights would assist in further analysis regarding customer satisfaction with the products and customer service to improve Danny’s business growth.