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

This is my solution to Danny Ma’s 8 Week SQL Challenge Case Study #1 — Danny’s Diner using MySQL. You can access the full 8-week challenge here, or just this challenge from here. Also, you can access my GitHub repo for this challenge by clicking here.

Introduction

Danny, who is a Japanese food lover, takes a risk and starts-up a diner named Danny’s Diner. He serves three different Japanese cousine; Sushi, Ramen and and Curry.

Danny’s Diner needs our help to keep the restaurant running. For that, the restaurant captured some basic data from their few months of operation.

Problem Statement

Danny wants answer to three main questions which are; Visiting patterns, money they spent, and their favorite food. He believes that if he has deeper connection with his customers, he can provide a better and personalised service 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.

In this challenge we have information about customers, their orders and diner menu in three different tables (Table 1):

Sales, Menu, and Members

Table 1. Entity Relationship Diagram

And we have 10 questions to answer:

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?

Solutions

Creating Tables

Danny provided his data for Postgre SQL 13. I intended to solve all in MySQL. To do so, I edited the schema query for MySQL.

CREATE TABLE sales 
(  
customer_id VARCHAR(1),  
order_date DATE,  
product_id INTEGER);   
INSERT INTO sales  
(customer_id, order_date, product_id)
VALUES  
('A', '2021-01-01', '1'),  
('A', '2021-01-01', '2'),  
('A', '2021-01-07', '2'),  
('A', '2021-01-10', '3'),  
('A', '2021-01-11', '3'),  
('A', '2021-01-11', '3'),  
('B', '2021-01-01', '2'),  
('B', '2021-01-02', '2'),  
('B', '2021-01-04', '1'),  
('B', '2021-01-11', '1'),  
('B', '2021-01-16', '3'),  
('B', '2021-02-01', '3'),  
('C', '2021-01-01', '3'),  
('C', '2021-01-01', '3'),  
('C', '2021-01-07', '3');  
CREATE TABLE menu 
(
product_id INTEGER,  
product_name VARCHAR(5),  
price INTEGER); 
INSERT INTO menu  
(product_id, product_name, price)
VALUES  
('1', 'sushi', '10'),  
('2', 'curry', '15'),  
('3', 'ramen', '12');   
CREATE TABLE members 
(
customer_id VARCHAR(1),  
join_date DATE); 
INSERT INTO members  
(customer_id, join_date)
VALUES  
('A', '2021-01-07'),  
('B', '2021-01-09');

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

That was a starter challenge. I joined two tables; sales and menu. And returned customer_id and SUM of prices (aliased as money_spent) and grouped them by customer_id.

SELECT customer_id, 
       SUM(price) AS money_spent 
FROM sales 
JOIN menu 
ON menu.product_id = sales.product_id
GROUP BY customer_id

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

For this question, I will use DISTINCT() and COUNT() functions. Count function will return the selected feature’s count, obviously. However, the usage of DISTINCT() is necessary, since customers can order 2 or more foods on the same day. The DISTINCT() function will remove repeated values (like sets in python)

SELECT customer_id, COUNT(DISTINCT(order_date)) AS number_of_visits
FROM sales
GROUP BY customer_id

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

I used SELECT within SELECT operation here. The first SELECT is to choose unique customer_id, and product names from sales and menu tables. After that I added a condition WHERE order_date is equal to MINIMUM order_dates.

There was a downside of that challenge: One customer ordered 2 items in the first day he/she visited; thus we cannot say which one of the two items is the first one cause we had no additional information.

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

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

Another JOIN challenge; I joined two tables (sales and menu) on product_id. Grouped the output by product_name, sorted DESCending and LIMITed the output by 1 row for each product_name.

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

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

I used WITH, DENSE_RANK() OVER functions on this one. So what I did was created a table named ‘r’ in which the customers ranked by their COUNT of bought products. After that, rest was easy; choose customer_id, productname and count from ‘r’ where the ranks are equal to 1.

WITH r AS 
(
SELECT s.customer_id,m.product_name,
        COUNT(s.product_id) as count,
        DENSE_RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS r
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, count
FROM r
WHERE r = 1

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

I used DENSE_RANK() on this one too. I created a ranks table in which customers ranked by their order date. After ordering, choosed order dates which are bigger or equal to membership date, and same as above choosed Ranks equal to 1.

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

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

Same as question 6; one minor change made to order_date condition. I choosed order_dates before join_date.

WITH ranks AS
(
SELECT s.customer_id,
    s.order_date,
       m.product_name,
    DENSE_RANK() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) AS ranks, mem.join_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
WHERE s.order_date < mem.join_date
)
SELECT * FROM ranks
WHERE ranks = 1

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

A challenge for multiple joins. Joined 3 tables together and choosed order_date’s before joining date, and group by the customer_id.

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

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

A CASE WHEN challenge. CASE WHEN function creates a table named ‘points’. Points are calculated as mentioned as in the question. After that, points and sales tables were JOINed together, and selected SUM(points) which grouped by customer_id.

WITH points AS 
(
SELECT *,
    CASE 
    WHEN m.product_name = 'sushi' THEN price * 20
    WHEN m.product_name != 'sushi' THEN price * 10
    END AS points
FROM menu m
    )
SELECT customer_id, SUM(points) AS points
FROM sales s
JOIN points p ON p.product_id = s.product_id
GROUP BY s.customer_id

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?

A bit more complex than the ones before; first created ‘points’ table with column that gives that the order made how many days after becoming a member (first_week). After that wrote a CASE WHEN function calculates total_points according to description. Finally, get the 1st month with EXTRACT function and get month equal to 1.

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,
       order_date
       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 * 
       END AS total_points
FROM points
WHERE EXTRACT(MONTH FROM order_date) = 1
) as t
GROUP BY customer_id

Bonus Questions

1. Recreate the 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 m.product_id = s.product_id
LEFT JOIN members mem ON mem.customer_id = s.customer_id
ORDER BY customer_id, order_date, price DESC

2. Rank Members — fill non-members with null

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 m.product_id = s.product_id
LEFT JOIN members mem ON mem.customer_id = s.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

Suggestions for Danny

1. Customer A & B visit a lot and spend more money in the Diner.
2. Ramen is the best-seller of all menu.
3. Customer B loves all the food equally, A & C both love Ramen the most. A & B are loyal customers; Danny might make some discounts or give bigger servings for these customers.
4. Customers A & B became members after they ate sushi :) Give Customer C a sushi on the house.
5. Points system might attract customers, even new ones. Say, earning 1500 points in a month provides special discounts, gifts etc. This would lead customers to become members.

This was my way to solve this challenge. If you like and enjoy what you read, please consider giving an upvote. Also, I’m still in SQL learning process, please let me know if I made any mistakes or better ways to solve the challenges.

Have a nice day.