Case Study1: Danny’s Diner – SQL Challenge

I stumbled on a SQL challenge by Danny Ma, An expert in SQL. He named the challenge, the 8weeks SQL challenge by Danny Ma. I am still an amateur in SQL so I jumped on the challenge and this is my first-week Documentation.

The first-week challenge or Case study is called “Danny’s Diner

Introduction

Danny loves Japanese food so at 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 fundamental data from its few months of operation. Still, it has no idea how to use its data to help them run the business. – from Danny’s website

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 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 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!

The key dataset for this case study are:

  • sales
  • menu
  • members

Data Schema

Entity Relationship Diagram (ER DIAGRAM)

Dataset schema

The Danny Dinner consists of Ten (10) and two(2) bonus questions that need to be answered to help the restaurant make a data-driven decision.

Questions and Answers

Tools: SQL Server

To make my query simple, I created a SQL view to join all the tables together which also solved the first Bonus question. i named the view table “Alltables”

SQL View: In SQL, a view is a virtual table based on the result set of an SQL statement. A view contains rows and columns, just like a real table.

/*Creating view for Alltables*/
CREATE VIEW AllTables AS
SELECT sales.customer_id, sales.order_date, menu.product_name, menu.price,
/*using the case statment to find customers who are (or not) member of Danny’s Diner loyalty program*/
CASE
WHEN sales.order_date >= members.join_date THEN 'Y'
ELSE 'N'
END AS member
FROM sales
JOIN
menu
ON sales.product_id = menu.product_id
FULL OUTER JOIN members
ON sales.customer_id = members.customer_id

Main Business Questions

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

SELECT customer_id, SUM(price) AS CustomerTotalSpent
FROM AllTables
GROUP BY customer_id
First Question Output

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

SELECT customer_id, COUNT(DISTINCT order_date) As CustomerTotalVisit
FROM AllTables
GROUP BY customer_id
Second Question Result

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

SELECT DISTINCT allt.customer_id, allt.product_name
FROM AllTables allt
WHERE order_date = (SELECT MIN(alltb.order_date)
FROM AllTables alltb)
ORDER BY allt.customer_id
Third Questions Result

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

SELECT Top 1 product_name, COUNT(product_name) As TotalProductSales
FROM AllTables
GROUP BY product_name
ORDER BY 2 DESC
Fourth Question Result

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

I started working with WITH Clause also known as CTE (Common Table Expression)

CTE(With clause): The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution.


/*Most Popular Items by each customer*/
WITH PopularItems AS(
    SELECT customer_id, product_name, COUNT(order_date) As total,
    RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(order_date) DESC) AS product_rank
    FROM AllTables
    GROUP BY customer_id, product_name)
SELECT customer_id, product_name
FROM PopularItems
WHERE product_rank = 1
Fifth Question Result

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

WITH FirstPurchased AS (
SELECT customer_id, product_name, MIN(order_date) AS ordered_date,
RANK () OVER (PARTITION BY customer_id ORDER BY MIN(order_date)) AS ARank
FROM AllTables 
WHERE member = 'Y'
GROUP BY customer_id, product_name)
SELECT customer_id, product_name
FROM FirstPurchased
WHERE ARANK = 1
Sixth Question Result

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

WITH LastPurchased AS (
SELECT customer_id, product_name, MAX(order_date) AS ordered_date,
RANK () OVER (PARTITION BY customer_id ORDER BY MAX(order_date) DESC) AS ARank
FROM PartialTable
WHERE member = 'N' AND join_date is Not NULL
GROUP BY customer_id, product_name)
SELECT customer_id, product_name
FROM LastPurchased
WHERE ARank = 1
Seventh SQL Question

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


SELECT customer_id, COUNT(product_name) As Totalproduct, SUM(price) AS Total
FROM PartialTable
WHERE member = 'N' AND join_date is Not NULL
GROUP BY customer_id
Eighth Question Result

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


WITH GiftPont AS(
SELECT product_id, product_name,
CASE WHEN product_id = 1 THEN price * 20
ELSE price * 10
END AS point
FROM menu)
SELECT customer_id, SUM(point) AS Totalpoint
FROM sales s
join
GiftPont G
ON s.product_id = G.product_id
GROUP BY customer_id
ORDER BY customer_id
Ninth Question Result

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?


WITH dates_cte AS(
	SELECT *, 
		DATEADD(DAY, 6, join_date) AS valid_date, 
		EOMONTH('2021-01-1') AS last_date
	FROM members
)

SELECT
	s.customer_id,
	sum(CASE
		WHEN s.product_id = 1 THEN price*20
		WHEN s.order_date between d.join_date and d.valid_date THEN price*20
		ELSE price*10 
	END) as total_points
FROM
	dates_cte d,
	sales s,
	menu m
WHERE
	d.customer_id = s.customer_id
	AND
	m.product_id = s.product_id
	AND
	s.order_date <= d.last_date
GROUP BY s.customer_id;
Tenth Question Result

Bonus Questions

  1. Join All The Things: To join all the tables
CREATE VIEW AllTables AS
SELECT sales.customer_id, sales.order_date, menu.product_name, menu.price,
 /*using the case statment to find customers who are (or not) member of Danny’s Diner loyalty program*/
 CASE
  WHEN sales.order_date >= members.join_date THEN 'Y'
  ELSE 'N'
  END AS member
FROM sales
JOIN
menu
ON sales.product_id = menu.product_id
FULL OUTER JOIN members
ON sales.customer_id = members.customer_id
Bonus Question 1
  1. Rank All The Things: To rank custom products
SELECT *, 
CASE 
WHEN member = 'Y' THEN RANK() OVER(PARTITION BY customer_id, member ORDER BY order_date) 
ELSE null END AS ranking
FROM AllTable
Bonus Question 2

I’m done……… Hurrary

Thank You and see you next week for the second case study

Damidez

I'm Damilare Damidez, A Data Analyst and Webmaster. I have been on the internet for over a decade. I help business owners to make data driven decision.

More Reading

Post navigation

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *