Join Me on my 8-Week SQL Adventure — Week 1 — Danny’s Diner

10 min readJun 3, 2023

This is the 1st Week of my 8 Week SQL Adventure from 8weeksqlchallenge.com, where I will go from Zero-to-Hero in SQL. SQL is a powerful language for querying and manipulating data and is widely used in data analytics.

In this article, I will share with you my approach to the first challenge: solving the case study of Danny’s Diner, a fictional restaurant that serves sushi, curry, and ramen. If you are interested in joining me on this journey or want to test your SQL skills, you can read the original case study here.

Do Like, Share, & Follow me on Medium for more interesting articles on the topics of Data Analytics, Data Viz, AI & ML and etc.

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

Entity Relationship Diagram:

Database & Tables:

The code to create the required Database and Tables is given here.

CREATE DATABASE Dannys_Diner;

USE Dannys_Diner;

CREATE TABLE sales(
customer_id VARCHAR(1),
order_date DATE,
product_id INTEGER);

CREATE TABLE menu(
product_id INTEGER,
product_name VARCHAR(10),
price INTEGER);

CREATE TABLE members(
customer_id VARCHAR(1),
date_of_joining DATE);

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');
  
INSERT INTO menu
  (product_id, product_name, price)
VALUES
  ('1', 'sushi', '10'),
  ('2', 'curry', '15'),
  ('3', 'ramen', '12');

INSERT INTO members
  (customer_id, date_of_joining)
VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

Solutions for Case Study Questions:

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

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

The query returns the Total Amount each customer has spent. The query first joins the salesand menu tables on the product_idcolumn. Then the SUM() calculates the total amount and the GROUP BY() function aggregates the total amount customer-wise.

[2] How many days has each customer visited the restaurant?

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

The query will first select the customer ID and the order date from the sales table. The COUNT() function will then be used to count the number of distinct order dates for each customer. The results will then be grouped by customer ID and the query will return the results.

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

SELECT customer_id, MIN(order_date) AS First_Purchase_Date, 
       product_name AS Product 
FROM sales
JOIN menu ON sales.product_id = menu.product_id 
GROUP BY customer_id;

The query will join sales and menu on product_id column. It will then group the results by the customer_id column and select the minimum order_date for each customer_id.

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

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

The query will join sales and menu on product_id column. It will then group the results by product_name and counts the number of times the product has been purchased and it is arranged in Descending order, finally the LIMIT() clause filters only the first output which is the number of times the most purchased item was purchased.

[5] Which item was the most popular for each customer?

SELECT s.customer_id AS Customer, m.product_name AS Product,COUNT(s.product_id) AS PopularItem
FROM sales s
JOIN menu m
ON s.product_id=m.product_id
GROUP BY s.customer_id;

The query will join sales and menu on product_id column. It will then group the results by customer_id , counts the number of times the product has been purchased, and selects the respective product_name.

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

SELECT s.customer_id AS Customer, m.date_of_joining AS Joining_Date, s.order_date, p.product_name AS Product
FROM sales s
JOIN menu p
ON s.product_id = p.product_id
JOIN members m
ON s.customer_id = m.customer_id
WHERE s.order_date>=m.date_of_joining
GROUP BY s.customer_id;

The query will then group the data by the customer ID and return one row for each customer. The Joining Date column will show the date that the customer joined the company. The Order Date column will show the date that the customer placed their first order. The Product Name column will show the name of the product that the customer purchased on their first order.

The WHERE clause is used to filter the results so that only rows where the order_date is greater than or equal to the date_of_joining are returned. This ensures that the query only returns rows for customers who have placed an order after they joined the company.

The GROUP BY clause is used to group the data by the customer ID. This ensures that the query returns one row for each customer, even if the customer has placed multiple orders.

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

SELECT s.customer_id AS Customer, s.order_date,m.date_of_joining AS Joining_Date, p.product_name AS Product
FROM sales s
JOIN menu p
ON s.product_id = p.product_id
JOIN members m
ON s.customer_id = m.customer_id
WHERE s.order_date<m.date_of_joining
GROUP BY s.customer_id
ORDER BY s.order_date DESC;

The query first joins the sales, menu, and members tables on the customer_id column. This ensures that only rows where the customer ID is the same in all three tables are selected.

The query then uses the WHERE clause to filter out any rows where the order date is less than the joining date. This ensures that only rows where the customer placed an order before they joined the membership program are selected.

The query then uses the GROUP BY clause to group the results by customer ID. This ensures that each customer only appears once in the results, even if they placed multiple orders.

Finally, the query uses the ORDER BY clause to order the results by order date in descending order. This ensures that the results are sorted by the most recent order date first.

[8] What are the total items and amount spent for each member before they became a member?

SELECT s.customer_id AS Customer, COUNT(s.product_id),SUM(p.price)
FROM sales s
JOIN menu p
ON s.product_id=p.product_id
JOIN members m
ON s.customer_id=m.customer_id
WHERE s.order_date<m.date_of_joining
GROUP BY s.customer_id;

The query works by first joining the sales, menu, and members tables together on the customer ID column. This ensures that only rows where the customer ID is the same in all three tables are returned.

The WHERE clause is then used to filter out any rows where the order date is before the date of joining. This ensures that the query only returns information for customers who have made purchases after they joined the membership program.

Finally, the GROUP BY clause is used to group the results by customer ID. This ensures that the number of products purchased and the total amount spent is calculated for each customer individually.

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

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

The query works by first joining the sales and menu tables together on the product ID column. This ensures that only rows where the product ID is the same in both tables are returned.

The CASE statement is then used to calculate the total points earned for each customer. If the product name is sushi, then the price is multiplied by 20. Otherwise, the price is multiplied by 10. The results are then grouped by 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 customers A and B have at the end of January?

SELECT
  members.customer_id,
  SUM(CASE
    WHEN sales.order_date >= members.date_of_joining AND sales.order_date < DATE_ADD(members.date_of_joining, INTERVAL 7 DAY) THEN price * 20
    WHEN product_name = 'sushi' THEN price * 20
    ELSE price * 10
  END) AS points
FROM members
LEFT JOIN sales
ON members.customer_id = sales.customer_id
LEFT JOIN menu
ON sales.product_id = menu.product_id
GROUP BY members.customer_id;

The query works by first joining the members, sales, and menu tables together on the customer ID column. This ensures that only rows where the customer ID is the same in all three tables are returned.

The CASE statement is then used to calculate the total points earned for each customer. If the order date is within 7 days of the date of joining and the product name is not sushi, then the price is multiplied by 20. If the product name is sushi, then the price is multiplied by 20. Otherwise, the price is multiplied by 10. The results are then grouped by customer ID.

BONUS QUESTIONS

BONUS QUESTION 1 — Join All Things

Recreate the table with — customer_id, order_date, product_name, price, member (Y/N) so that Danny would not need to join the underlying tables using SQL.

ALTER TABLE sales ADD MEMBER VARCHAR(1) DEFAULT 'N';

UPDATE sales s, members m
SET s.MEMBER = 'Y'
WHERE s.order_date>=m.date_of_joining;

SELECT * FROM sales;

The first query, ALTER TABLE sales ADD MEMBER VARCHAR(1) DEFAULT 'N';, adds a new column called MEMBER to the sales table. The column is a single-character string (VARCHAR(1)) and has a default value of N.

The second query, UPDATE sales s, members m SET s.MEMBER = 'Y' WHERE s.order_date>=m.date_of_joining;, updates the MEMBER column in the sales table. The update sets the value of the MEMBER column to Y for all rows where the order_date is greater than or equal to the date_of_joining in the members table.

BONUS QUESTION 2 — Rank All Things

Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

SELECT *,
(CASE WHEN MEMBER='N' then NULL
ELSE RANK() OVER(PARTITION BY customer_id,MEMBER ORDER BY order_date)
END) AS Ranking
FROM sales;

The query SELECT *, (CASE WHEN MEMBER='N' then NULL ELSE RANK() OVER(PARTITION BY customer_id,MEMBER ORDER BY order_date) END) AS Ranking FROM sales; selects all columns from the sales table and calculates the rank of each row within each customer group. The rank is calculated using the RANK() window function, which assigns a rank to each row within a partition. The partition is defined by the customer_id and MEMBER columns. The rows are then ordered by the order_date column.

The CASEstatement is used to handle the case where the MEMBER column is NULL. In this case, the RANK() function will return NULL, so the CASE statement returns NULL as well. Otherwise, the RANK() function returns the rank of the row.

The AS Ranking clause gives the column a new name, Ranking. This is useful for readability and for using the column in subsequent queries.

Conclusion

In conclusion, this article has outlined the first week of my 8-week SQL adventure. I have learned the basics of SQL, including how to create tables, insert data, and select data.

I have also learned about some of the more advanced features of SQL, such as joins, CASE(), and more. I am excited to continue my SQL journey and learn more about this powerful language.

I would also like to thank you for reading this conclusion. I hope that you found it helpful and informative. If you have any questions or comments, please feel free to leave them below.

Solutions for Week 2 of this Challenge are posted here.