In this case study, we are tasked with helping Danny, the owner of a small restaurant, analyze his business data. The goal is to help him better understand customer behavior, optimize sales strategies, and improve operational efficiency. The case study involves queries focusing on simple aggregations, joins, and date-based filtering.
View the detailed case study on www.8weeksqlchallange.com
The main objectives of the case study are:
- Understanding customer purchase behavior.
- Analyzing total sales and revenue generated by the diner.
- Identifying patterns of frequent diners and how their spending changes over time.
In this folder, you will find the following files:
-
Database_Creation_Queries.sql: This .sql file contains all queries to create database and tables.
-
Questions and Answers Queries.sql: This file contains all the queries used to answer the case study questions.
-
Questions And Answers with Queries and Output.pdf: Each question is answered with the query and the output generated.
Note: All Solutions are coded with MySQL
- Q1: What is the total amount each customer spent at the restaurant?
SELECT
sales.customer_id,
SUM(menu.price) AS 'Total Spending in $'
FROM sales
INNER JOIN menu ON
sales.product_id=menu.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id;
customer_id | Total Spending in $ |
---|---|
A | 76 |
B | 74 |
C | 36 |
- Q2: How many days has each customer visited the restaurant?
SELECT
customer_id,
COUNT(DISTINCT(order_date)) AS days_visited
FROM sales
GROUP BY customer_id;
customer_id | days_visited |
---|---|
A | 4 |
B | 6 |
C | 2 |
- Q3. What was the first item from the menu purchased by each Customer?
SELECT
customer_id,
product_name AS first_purchased_product
FROM
(SELECT
sales.customer_id,
menu.product_name,
DENSE_RANK() OVER( PARTITION BY sales.customer_id ORDER BY sales.order_date) As rnk
FROM sales
INNER JOIN menu ON sales.product_id=menu.product_id) AS sales_rank
WHERE rnk = 1
GROUP BY customer_id,product_name;
customer_id | first_purchased_product |
---|---|
A | sushi |
A | curry |
B | curry |
C | ramen |
- Q4. What is the most purchased item on the menu and how many times was it purchased by all customers?
- This question needs to be solved in two parts, in part 1, we'll find the most purchased item and in part 2, we'll find how mny times its purchased by all customers
-- Part 1
SELECT
menu.product_name AS 'Most purchased Product',
COUNT(sales.product_id) AS 'Purchase Count'
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
GROUP BY menu.product_name
ORDER BY COUNT(sales.product_id) DESC
LIMIT 1; -- this gives the most purchased item on menu
Most purchased Product | Purchase Count |
---|---|
ramen | 8 |
-- Part 2
SELECT
sales.customer_id,
COUNT(sales.product_id) AS purchase_count
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
WHERE sales.product_id = (SELECT product_id FROM sales
GROUP BY product_id
ORdER BY count(product_id)
DESC LIMIT 1)
GROUP BY sales.customer_id
ORDER BY purchase_count DESC; -- this gives the list of customers who have purchased highest purchased item
customer_id | purchase_count |
---|---|
A | 3 |
B | 2 |
C | 3 |
- Q5. Which item was the most popular for each customer?
WITH cte_popular_products as (
SELECT
sales.customer_id,
menu.product_name,
COUNT(*) as purchase_count,
DENSE_RANK() OVER( PARTITION BY sales.customer_id
ORDER BY COUNT(*) DESC) AS rnk
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
GROUP BY sales.customer_id, menu.product_name )
SELECT
customer_id,
product_name,
purchase_count
FROM cte_popular_products
WHERE rnk=1;
customer_id | product_name | purchase_count |
---|---|---|
A | ramen | 3 |
B | curry | 2 |
B | sushi | 2 |
B | ramen | 2 |
C | ramen | 3 |
- Q6. Which item was purchased first by the customer after they became a member?
WITH cte_after_membership AS (
SELECT
members.customer_id,
sales.product_id,
DENSE_RANK() OVER (
PARTITION BY members.customer_id
ORDER BY sales.order_date) AS densrank
FROM members
INNER JOIN sales
ON members.customer_id = sales.customer_id
AND sales.order_date > members.join_date
)
SELECT
cte_after_membership.customer_id,
menu.product_name
FROM cte_after_membership
INNER JOIN menu
ON cte_after_membership.product_id = menu.product_id
WHERE densrank = 1
ORDER BY cte_after_membership.customer_id ASC;
customer_id | product_name |
---|---|
A | ramen |
B | sushi |
- Q7. Which item was purchased just before the customer became a member?
WITH cte_after_membership AS (
SELECT
members.customer_id,
sales.product_id,
DENSE_RANK() OVER (
PARTITION BY members.customer_id
ORDER BY sales.order_date) AS densrank
FROM members
INNER JOIN sales
ON members.customer_id = sales.customer_id
AND sales.order_date < members.join_date
)
SELECT
cte_after_membership.customer_id,
menu.product_name
FROM cte_after_membership
INNER JOIN menu
ON cte_after_membership.product_id = menu.product_id
WHERE densrank = 1
ORDER BY cte_after_membership.customer_id ASC;
customer_id | product_name |
---|---|
A | sushi |
A | curry |
B | curry |
- Q8. What is the total items and amount spent for each member before they became a member?
SELECT
sales.customer_id,
COUNT(sales.product_id) AS total_items,
SUM(menu.price) AS total_amt_spent
FROM sales
JOIN members ON sales.customer_id = members.customer_id
JOIN menu ON sales.product_id = menu.product_id
WHERE sales.order_date < members.join_date
group by sales.customer_id
ORDER BY sales.customer_id;
customer_id | total_items | total_amt_spent |
---|---|---|
A | 2 | 25 |
B | 3 | 40 |
- Q9. 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(menu.price) AS total_price,
SUM(CASE
WHEN menu.product_name = 'sushi' THEN menu.price * 20
ELSE menu.price * 10
END) AS total_points
FROM sales
JOIN menu ON sales.product_id = menu.product_id
GROUP BY customer_id;
customer_id | total_price | total_points |
---|---|---|
A | 76 | 860 |
B | 74 | 940 |
C | 36 | 360 |
- Q10. 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
sales.customer_id,
SUM(menu.price) AS total_price,
SUM(CASE
WHEN sales.order_date BETWEEN members.join_date AND DATE_ADD(members.join_date, INTERVAL 6 DAY) THEN menu.price*20
ELSE
CASE WHEN menu.product_name = 'sushi' THEN menu.price * 20
ELSE menu.price * 10
END
END) AS points
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
INNER JOIN members ON sales.customer_id = members.customer_id
WHERE sales.order_date <= '2021-01-31'
AND sales.customer_id IN ('A','B')
GROUP BY sales.customer_id
ORDER BY sales.customer_id;
customer_id | total_price | points |
---|---|---|
A | 76 | 1370 |
B | 62 | 820 |
Bonus Questions: Q1. Join All The Things Recreate the table with columns: customer_id, order_date, product_name, price, member_status (Y/N)
SELECT
sales.customer_id,
sales.order_date,
menu.product_name,
menu.price,
CASE
WHEN sales.order_date >= members.join_date THEN 'Y'
ELSE 'N' END AS member_status
FROM sales
LEFT JOIN members
ON sales.customer_id = members.customer_id
INNER JOIN menu
ON sales.product_id = menu.product_id
ORDER BY members.customer_id;
customer_id | order_date | product_name | price | member_status |
---|---|---|---|---|
A | 2021-01-01 | sushi | 10 | N |
A | 2021-01-01 | curry | 15 | N |
A | 2021-01-07 | curry | 15 | Y |
A | 2021-01-10 | ramen | 12 | Y |
A | 2021-01-11 | ramen | 12 | Y |
A | 2021-01-11 | ramen | 12 | Y |
B | 2021-01-01 | curry | 15 | N |
B | 2021-01-02 | curry | 15 | N |
B | 2021-01-04 | sushi | 10 | N |
B | 2021-01-11 | sushi | 10 | Y |
B | 2021-01-16 | ramen | 12 | Y |
B | 2021-02-01 | ramen | 12 | Y |
C | 2021-01-01 | ramen | 12 | N |
C | 2021-01-01 | ramen | 12 | N |
C | 2021-01-07 | ramen | 12 | N |
Q2. Rank All The 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.
WITH cte_all_joined AS (
SELECT
s.customer_id,
s.order_date AS order_date,
m.product_name,
m.price,
CASE
WHEN s.customer_id IS NOT NULL
AND s.order_date >= mm.join_date THEN 'Y'
ELSE 'N'
END AS member_status
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
LEFT JOIN members mm ON s.customer_id = mm.customer_id
)
SELECT
*,
CASE
WHEN member_status = 'Y' THEN
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date)
ELSE
NULL
END AS ranking
FROM
cte_all_joined
ORDER BY
customer_id,
order_date,
product_name;
customer_id | order_date | product_name | price | member_status | ranking |
---|---|---|---|---|---|
A | 2021-01-01 | curry | 15 | N | NULL |
A | 2021-01-01 | sushi | 10 | N | NULL |
A | 2021-01-07 | curry | 15 | Y | 2 |
A | 2021-01-10 | ramen | 12 | Y | 3 |
A | 2021-01-11 | ramen | 12 | Y | 4 |
A | 2021-01-11 | ramen | 12 | Y | 4 |
B | 2021-01-01 | curry | 15 | N | NULL |
B | 2021-01-02 | curry | 15 | N | NULL |
B | 2021-01-04 | sushi | 10 | N | NULL |
B | 2021-01-11 | sushi | 10 | Y | 4 |
B | 2021-01-16 | ramen | 12 | Y | 5 |
B | 2021-02-01 | ramen | 12 | Y | 6 |
C | 2021-01-01 | ramen | 12 | N | NULL |
C | 2021-01-01 | ramen | 12 | N | NULL |
C | 2021-01-07 | ramen | 12 | N | NULL |
For a detailed walkthrough of this case study, visit the video on iThinkData YouTube channel Playlist.
I’m Vaibhav Chavan, the creator of iThinkData, a platform dedicated to making data analytics and SQL easy to understand for everyone. Follow along as I dive deep into SQL tutorials, case studies, and data projects on YouTube.
🌟 Stay Connected with iThinkData ! 🌟
🎥 Subscribe to iThinkData : 🔔 Don't miss out on weekly data challenges, tutorials, and expert insights! 💡📈
👨💼 LinkedIn : 📊 Let’s connect professionally and build a powerful data-driven network! 💼🌐
💬 Join My WhatsApp Channel : 📱 Be the first to get exclusive content, project updates, and new videos! 🚀📊