• Background Information
• Overview of the Datasets
• Data Analysis
  • The total amount each customer spent
  • Count the days of each customer visit
  • The first item be purchased by each customer on their first visit
  • Most popular food & Purchased time
  • Each customer’s favourite food
  • The first item customer purchased after they become a member
  • The last item customer purchased before becoming the member
  • The total number of items and amount of money spent before becoming a member
  • If the membership plan is 1 dollar spent equals 10 points and order sushi has a 2x points multiplier, how many points would each customer earn?
  • Scenario: 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?
  • Bonus question
• Insights
• Strategies

Background Information

Danny seriously decides to embark upon a risky venture and opens up a cute little restaurant that sells his three favourite foods: sushi, curry, and ramen. Danny’s Diner is in need of assistance to help the restaurant stay afloat — the restaurant has captured some fundamental data from its few months of operation. Still, it has no idea how to use its data to help run the business.

Three key datasets have been shared by the restaurant owners, including sales, menu, and members. Danny wants to get some insight into whether he should expand the existing customer loyalty program and generate some well-organized datasets for easily inspecting.

“The case study information and corresponding data sources is belonging to the “8 Weeks SQL Challenge” designed and credited by Danny Ma”

Overview of the Datasets

All datasets exist within the dannys_diner the database schema, the sales table captures all customer_id level purchases with a corresponding order_date and product_id information for when and what menu items were ordered.The menu table maps the product_id to the actual product_name and price of each menu item.The final members table captures the join_date when a customer_id joined the beta version of Danny’s Diner loyalty program.

Data Analysis

Due to the already-built database schema, the data can be directly processed for analysis after review with the ROCCC Principle and all the queries are written with the MySQL server.

The total amount each customer spent

I use the JOIN()function to join the two tables first and use the SUM() to sum up all the purchases to calculate the money spent.

SELECT s.customer_id, SUM (m.price) AS customer_spent
FROM sales s
JOIN menu m
ON s.product_id=m.product_id
GROUP BY s.customer_id;

Count the days of each customer visit

To be aware that Customers can make several purchases per day, the function COUNT() and DISTINCT ()need to be used simultaneously.

SELECT customer_id,
Count (DISTINCT(order_date)) AS number_of_visiting_days
FROM Sales
GROUP BY customer_id;

The first item be purchased by each customer on their first visit

The nested SELECT() is used here (a query within a query). In the sales and menu tables, Main SELECT () is used to select the distinct customer id and product names. Following that, I added the condition where the order date is the minimum order date with the second SELECT().

SELECT DISTINCT(customer_id), product_name FROM sales s
JOIN menu m
ON s.product_id = m.product_id
WHERE s.order_date = ANY (
    SELECT MIN(order_date)
    FROM sales
    GROUP BY customer_id
);

Most popular food & Purchased time

I use JOIN () to join the two tables to generate the output, then, with some sorting functions such as DESC and LIMIT to get extreme value.

SELECT COUNT(product_name) AS most_popular_product, product_name FROM sales s
JOIN menu m
ON s.product_id=m.product_id
GROUP BY product_name
ORDER BY most_popular_product DESC
LIMIT 1;

Each customer’s favourite food

The query gets a little bit complex here; I first developed a common table expression that ranked customers according to the number of products they had purchased; then, I selected the customer id, the product name, and the count from the expression, limiting the rank to 1. To be aware, Function DENSE_RANK () only applied to MySQL v8.0 or above.

WITH cte_ranking AS 
(
SELECT s.customer_id,m.product_name,
        COUNT(s.product_id) as order_count,
        DENSE_RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS cte_ranking
FROM menu m 
JOIN sales s 
ON s.product_id = m.product_id
GROUP BY s.customer_id, s.product_id, m.product_name
) 
SELECT customer_id, product_name, order_count
FROM cte_ranking
WHERE cte_ranking = 1;

The first item customer purchased after they become a member

First, a ranking table will be created to rank customers by order date and filter the result to get the first line that the order date is equal to or beyond the membership date.

WITH ordered_became_member AS
(
  SELECT 
      s.customer_id 
      ,m.product_name 
      ,s.order_date 
      ,DENSE_RANK() OVER(
          PARTITION BY s.customer_id
          ORDER BY s.order_date
      ) AS  rank
  FROM sales s
  JOIN menu m
      ON s.product_id  = m.product_id
  JOIN members mem
      ON s.customer_id = mem.customer_id
  WHERE s.order_date >= mem.join_date
)

SELECT 
  customer_id, product_name,order_date
FROM ordered_became_member
WHERE rank = 1;

The last item customer purchased before becoming the member

Only one query needs to be changed compared to question 6, which is letting order data before the membership date.

WITH ordered_before_member AS
(
  SELECT 
      s.customer_id 
      ,m.product_name 
      ,s.order_date 
      ,DENSE_RANK() OVER(
          PARTITION BY s.customer_id
          ORDER BY s.order_date
      ) AS  rank
  FROM sales s
  JOIN menu m
      ON s.product_id  = m.product_id
  JOIN members mem
      ON s.customer_id = mem.customer_id
  WHERE s.order_date <= mem.join_date
)

SELECT 
  customer_id, product_name
FROM ordered_before_member
WHERE rank = 1;

The total number of items and amount of money spent before becoming a member

Multiple functions JOIN() are adapted by joining three tables together and selecting the order date before joining the membership.

SELECT s.customer_id,
       count(s.product_id) AS total_items, 
       SUM(price) AS money_spent
FROM sales s
JOIN menu m 
ON s.product_id = m.product_id
JOIN members mem
ON s.customer_id = mem.customer_id
WHERE s.order_date < mem.join_date
GROUP BY s.customer_id;

If the membership plan is 1 dollar spent equals 10 points and order sushi has a 2x points multiplier, how many points would each customer earn?

A specific scenario is given here with multiple conditions. Therefore, a CASE() function can be used here; the CASE () function can apply the above conditions in a new column. Then, points are cumulative using the SUM() and displayed after a JOIN() function.

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

Scenario: 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, a common table expression is created to show the order placed and how many days after becoming a member for the first week, then a CASE()function is used to calculate the points corresponding to the above conditions; after that, the EXTRACT function is used to get the first month (January).

SELECT customer_id, SUM(total_points)
FROM 
(WITH points AS
(
SELECT s.customer_id, 
 (s.order_date - mem.join_date) AS first_week,
        m.price,
        m.product_name,
        s.order_date
    FROM sales s
 JOIN menu m ON s.product_id = m.product_id
 JOIN members AS mem
 ON mem.customer_id = s.customer_id
    )
SELECT customer_id,
  CASE 
  WHEN first_week BETWEEN 0 AND 7 THEN price * 20
        WHEN (first_week > 7 OR first_week < 0) AND product_name = 'sushi' THEN price * 20
  WHEN (first_week > 7 OR first_week < 0) AND product_name != 'sushi' THEN price * 10
        END AS total_points,
        order_date
FROM points
WHERE EXTRACT(MONTH FROM order_date) = 1) as time
GROUP BY customer_id;

Bonus question

Creating basic data tables that Danny and his team can derive insights While creating the new table, Function LEFT JOIN is used to pull all rows from the left table and only matching rows from the right table

SELECT s.customer_id,
  s.order_date,
        m.product_name,
        m.price,
        CASE 
        WHEN s.order_date >= mem.join_date THEN 'Y'
        ELSE 'N' 
        END AS member
FROM sales s
LEFT JOIN menu m ON s.product_id = m.product_id
LEFT JOIN members mem ON s.customer_id = mem.customer_id
ORDER BY customer_id, order_date, price DESC;

Ranking of customer products in the loyalty program FUNCTION CASE() is used here to replace the value of non-members with a null value, then in the new ranking column, rank by partitioning product id by descending order date to find out the product order ranking when the customer joins the membership.

WITH membership AS
(SELECT s.customer_id,
  s.order_date,
        m.product_name,
        m.price,
        CASE 
        WHEN s.order_date >= mem.join_date THEN 'Y'
        ELSE 'N' 
        END AS member
FROM sales s
LEFT JOIN menu m ON s.product_id = m.product_id
LEFT JOIN members mem ON s.customer_id = mem.customer_id
ORDER BY customer_id, order_date, 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 membership;

Insights

The most popular product in Danny’s Diner is ramen.

Customer B is the most frequent visitor in the month.

Customers A and C prefer ramen more often, while Customer B did not show an obvious preference.

Their points for Customers A, B, and C were 860, 940, and 360, respectively, during January.

For the two customers join the membership, Customer A spent 25 dollars before joining, and Customer B spent 40 dollars before joining.

Strategies

• Continue improving the points system with phrase rewards such as 1500 points for a considerable discount or 1000 points for free sushi.
• A new marketing strategy can be adopted; customers who spend over 30 dollars may get a chance to join the membership for free or a free sushi dish.
• Bundle ramen with the restaurant brand as the main product and continuously introduce new flavours and promotions.

That’s the end of the project. Thank you for your reading, hooyah!