Case Study #1 - Solution Danny's Diner
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!
Danny has shared with you 3 key datasets for this case study:
salesmenumembers
You can inspect the entity relationship diagram and example data below.
Table Entity Relationship Diagram
1. What
is the total amount each customer spent at the restaurant?
SELECT sales.customer_id, SUM(menu.price) as Total_spent FROM sales as sales
JOIN menu as menu
on sales.product_id = menu.product_id
GROUP BY sales.customer_id
Output:
2. How
many days has each customer visited the restaurant?
SELECT customer_id, COUNT(DISTINCT order_date) as Customer_visit
FROM sales
GROUP BY customer_id
Output:
3. What
was the first item from the menu purchased by each customer?
SELECT temp.customer_id, temp.product_name FROM(
SELECT sales.customer_id, sales.order_date, menu.product_name,
ROW_NUMBER() OVER(PARTITION BY sales.customer_id ORDER BY sales.order_date) as R_number
FROM sales as sales
JOIN menu as menu
ON sales.product_id = menu.product_id
) as temp
WHERE temp.R_number = 1;
Output:
4. What
is the most purchased item on the menu and how many times was it purchased by
all customers?
SELECT menu.product_name , COUNT(menu.product_name) as MostPurchased_Item
FROM sales as sales
LEFT JOIN menu as menu
ON sales.product_id = menu.product_id
GROUP BY menu.product_name
ORDER BY MostPurchased_Item DESC
Output:
5. Which
item was the most popular for each customer?
SELECT temp.customer_id, temp.product_name, temp.MostPopular_Item
FROM (
SELECT sales.customer_id, menu.product_name,
COUNT(menu.product_id) as MostPopular_Item,
DENSE_RANK() OVER(PARTITION BY sales.customer_id ORDER BY COUNT(sales.customer_id) DESC) as D_rank
FROM sales as sales
LEFT JOIN menu as menu
ON sales.product_id = menu.product_id
GROUP BY sales.customer_id, menu.product_name
) as temp
WHERE temp.D_rank = 1;
Output:
6. Which
item was purchased first by the customer after they became a member?
SELECT temp.customer_id, temp.product_name, temp.order_date, temp.join_date
FROM (
SELECT sales.customer_id, menu.product_name, sales.order_date, members.join_date,
ROW_NUMBER() OVER(PARTITION BY sales.customer_id ORDER BY sales.order_date) as R_number
FROM sales as sales
JOIN members as members
ON sales.customer_id = members.customer_id
JOIN menu as menu
ON sales.product_id = menu.product_id
WHERE sales.order_date> members.join_date
) as temp
WHERE temp.R_number = 1;
Output:
7. Which
item was purchased just before the customer became a member?
SELECT temp.customer_id, temp.product_name, temp.order_date, temp.join_date
FROM (
SELECT sales.customer_id, menu.product_name, sales.order_date, members.join_date,
ROW_NUMBER() OVER(PARTITION BY sales.customer_id ORDER BY sales.order_date DESC) as R_number
FROM sales as sales
JOIN members as members
ON sales.customer_id = members.customer_id
JOIN menu as menu
ON sales.product_id = menu.product_id
WHERE sales.order_date< members.join_date
) as temp
WHERE temp.R_number = 1;
Output:
7. What
is the total items and amount spent for each member before they became a
member?
SELECT sales.customer_id, COUNT(menu.product_id) as Item_count, SUM(menu.price) as Amount_spent
FROM sales as sales
JOIN members as members
ON sales.customer_id = members.customer_id
JOIN menu as menu
ON sales.product_id = menu.product_id
WHERE sales.order_date < members.join_date
GROUP BY sales.customer_id
Output:
8. If
each $1 spent equates to 10 points and sushi has a 2x points multiplier - how
many points would each customer have?
SELECT sales.customer_id,
SUM(CASE WHEN menu.product_id = 1 THEN menu.price*20 ELSE menu.price*10 END) as Customer_points
FROM sales as sales
JOIN menu as menu
ON sales.product_id = menu.product_id
GROUP BY sales.customer_id
Output:
9. 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?
SELECT sales.customer_id,
SUM(menu.price*20) as TotalEarn_points
FROM sales as sales
JOIN menu as menu
ON sales.product_id = menu.product_id
JOIN members as members
ON sales.customer_id = members.customer_id
WHERE sales.order_date >= members.join_date AND sales.order_date < '2021-02-01'
GROUP BY sales.customer_id
Output:
Bonus Questions
The following questions are related creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.
Recreate the following table output using the available data:
SELECT
sales.customer_id,
sales.order_date,
menu.product_name,
menu.price,
CASE
WHEN members.join_date > sales.order_date THEN 'N'
WHEN members.join_date <= sales.order_date THEN 'Y'
ELSE 'N' END AS member_status
FROM sales as sales
LEFT JOIN members as members
ON sales.customer_id = members.customer_id
JOIN menu as menu
ON sales.product_id = menu.product_id
ORDER BY members.customer_id, sales.order_date
Output:
