-- Drop Danny's Dinner Database If Exists
DROP DATABASE IF EXISTS dannys_diner;1 Introduction
I wanted to advance my SQL skills while steering clear of tutorials and certifications, and what better way to do this than through a real-world style case study? That’s why I decided to take on the 8 Weeks SQL Challenge by Danny Ma.
The first case study, Danny’s Diner, tells the story of Danny, who loves Japanese food and, at the beginning of 2021, opened a small restaurant serving his three favorite dishes: sushi, curry, and ramen. Like many new business owners, Danny has been collecting basic sales data but doesn’t know how to use it effectively to guide his decisions.
My task in this case study is to help Danny analyze his customers’ behavior—how often they visit, how much they spend, and which menu items are their favorites. These insights will not only help Danny strengthen his connection with loyal customers but also guide his decision on whether to expand the diner’s customer loyalty program. Additionally, part of the challenge involves generating simple datasets that Danny’s team can use without needing SQL knowledge.
For privacy reasons, Danny provided a small sample of customer data, but it’s enough to write fully functioning SQL queries to answer his business questions.
2 Database Setup
Before jumping into the analysis, I first set up the dannys_diner database along with its three core tables—members, menu, and sales. This setup forms the foundation for exploring Danny’s data and answering the business questions.
2.0.0.1 Create Database
I started by creating the dannys_diner database. To make sure there weren’t any conflicts, I dropped any existing version before recreating it fresh.
-- Create Database Danny's Dinner
CREATE DATABASE dannys_diner;2.0.0.2 Create Members Table
The first table I created was members, which records when each customer joined the loyalty program. This will later allow me to compare customer behavior before and after signing up.
-- Drop Table Members If Exists
DROP TABLE IF EXISTS members;-- Create Members Table
CREATE TABLE members (
customer_id VARCHAR(1),
join_date DATE
);-- Insert into Members Table
INSERT INTO members (customer_id, join_date)
VALUES ('A', '2021-01-07'),
('B', '2021-01-09');-- Query Members Table
SELECT *
FROM members m;2.0.0.4 Create Sales Table
Finally, I created the sales table, which records every customer purchase with the customer_id, order_date, and product_id. This dataset is key—it shows exactly what customers bought and when, letting me analyze patterns in spending and loyalty.
-- Drop Table Sales If Exists
DROP TABLE IF EXISTS sales;-- Create Sales Table
CREATE TABLE sales (
customer_id VARCHAR(1),
order_date DATE,
product_id INTEGER
);-- Insert into Sales Table
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');-- Query the Sales Table
SELECT *
FROM sales s;With the database and all three tables in place, I was ready to move on to the fun part: querying the data to uncover insights about Danny’s customers.
3 Questions
Each of the following case study questions can be answered using a single SQL statement:
1. What is the total amount each customer spent at the restaurant?
-- Total Amount Spent by Each Customer
SELECT
s.customer_id,
SUM(m.price) AS total_amount_spent
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
GROUP BY s.customer_id
ORDER BY s.customer_id;Looking at the numbers, it’s easy to see who’s really hooked on Danny’s Diner. Customer A has racked up $76, making them the diner’s biggest fan so far. Close behind, Customer B has spent $74, showing they’re practically a regular. Customer C, with $36, is dipping their toes in—maybe still deciding which dish to fall in love with. These patterns give a clear snapshot of who’s most invested in the diner and who might need a little extra nudge to come back more often.
2. How many days has each customer visited the restaurant?
-- Days Each Customer Visited the Restaurant
SELECT
s.customer_id,
COUNT(DISTINCT order_date) AS visits
FROM sales s
GROUP BY s.customer_id;Customer B clearly can’t get enough of Danny’s Diner, showing up on 6 different days—definitely a regular. Customer A follows with 4 visits, keeping a steady presence, while Customer C has only popped in twice, still exploring the menu. These visit patterns highlight who the loyal fans are and hint at opportunities to tempt the less frequent diners back for more.
3. What was the first item from the menu purchased by each customer?
-- First Item Purchased by Each Customer
SELECT DISTINCT
customer_id,
product_name,
order_date
FROM (
SELECT
s.customer_id,
s.order_date,
m.product_name,
DENSE_RANK() OVER w AS ranking
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
WINDOW w AS (PARTITION BY s.customer_id ORDER BY s.order_date)
)x
where x.ranking = 1;The first bites can say a lot about a customer’s tastes. Customer A kicked things off with curry (and sushi on the same day!), setting the stage for their love of the menu. Customer B also started with curry, while Customer C jumped straight into ramen. These early choices give a peek into what drew each customer in and hint at the flavors that keep them coming back.
4. What is the most purchased item on the menu and how many times was it purchased by all customers?
-- Most purchased items & how many times it was purchased by all customers
SELECT
m.product_name,
COUNT(*) AS purchases
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
GROUP BY m.product_name
ORDER BY purchases DESC;Ramen is clearly the star of the menu, racking up 8 orders and claiming the top spot among all dishes. Curry comes in second with 4 orders, and sushi rounds out the list with 3. It’s obvious that ramen has captured the hearts (and appetites) of Danny’s customers, making it a key player for promotions or special deals.
5. Which item was the most popular for each customer?
-- Most popular item for each customer
SELECT
customer_id,
product_name,
popularity
FROM (
SELECT
s.customer_id,
m.product_name,
COUNT(*) AS popularity,
DENSE_RANK() OVER w AS ranking
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
GROUP BY s.customer_id, m.product_name
WINDOW w AS (PARTITION BY s.customer_id ORDER BY COUNT(*) DESC)
)x
WHERE x.ranking = 1;When it comes to personal favorites, Customer A can’t get enough of ramen, ordering it 3 times. Customer B is a bit of a foodie explorer, splitting their love between sushi, curry, and ramen—each ordered twice. Customer C is a ramen devotee, sticking to it every time with 3 orders. Understanding these individual cravings can help Danny tailor recommendations or special offers to keep his customers coming back for their favorites.
6. Which item was purchased first by each customer after they became a member?
-- Item purchased first after becoming a member
SELECT
customer_id,
join_date,
order_date,
product_name
FROM (
SELECT
s.customer_id,
ms.product_name,
m.join_date,
s.order_date,
DENSE_RANK() OVER w AS ranking
FROM sales s
JOIN members m
ON s.customer_id = m.customer_id
JOIN menu ms
ON s.product_id = ms.product_id
WHERE s.order_date >= m.join_date
WINDOW w AS (PARTITION BY s.customer_id ORDER BY s.order_date)
)x
WHERE x.ranking = 1;Joining the loyalty program seems to have sparked some interesting choices. Customer A jumped in and tried curry on the very day they became a member—maybe feeling a bit adventurous. Customer B, on the other hand, stuck with sushi for their first post-membership order, showing loyalty to a favorite. These early post-membership orders hint at how the program might influence customers to either explore new dishes or double down on what they already love.
7. Which items was purchased by each customer just before they became a member?
-- Item purchased first before becoming a member
SELECT
customer_id,
order_date,
join_date,
product_name
FROM (
SELECT
s.customer_id,
ms.product_name,
m.join_date,
s.order_date,
DENSE_RANK() OVER w AS ranking
FROM sales s
JOIN members m
ON s.customer_id = m.customer_id
JOIN menu ms
ON s.product_id = ms.product_id
WHERE s.order_date <= m.join_date
WINDOW w AS (PARTITION BY s.customer_id ORDER BY s.order_date DESC)
)x
WHERE x.ranking = 1;Right before signing up for the loyalty program, we can see what customers were gravitating toward. Customer A ended their pre-membership visits with curry, while Customer B wrapped up with sushi. These final pre-membership orders give a snapshot of their go-to favorites and suggest what drew them in enough to become members.
8. What is the total number of items bought and amount spent by each member just before they became a member?
-- Total number of items bought and total amount spent before becoming a member
SELECT
s.customer_id,
COUNT(s.product_id) AS total_items,
SUM(ms.price) AS amount_spent
FROM sales s
JOIN members m
ON s.customer_id = m.customer_id
JOIN menu ms
ON s.product_id = ms.product_id
WHERE s.order_date <= m.join_date
GROUP BY s.customer_id
ORDER BY s.customer_id;Looking at the numbers before membership, both Customer A and Customer B had already bought 3 items each, spending $40 in total. It’s clear they were already regular patrons, and joining the loyalty program was just the next step in their ongoing relationship with Danny’s Diner.
9. If each $1 spent equates 10 points and sushi has a 2x points multiplier, how many points would each customer have gained by now?
-- Calculate Customer Points
SELECT
s.customer_id,
SUM(
CASE
WHEN m.product_name ILIKE '%sushi%' THEN (m.price * 10 * 2)
ELSE (m.price * 10)
END
) AS total_points
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
GROUP BY s.customer_id
ORDER BY s.customer_id;When it comes to loyalty points, Customer B takes the lead with 940 points, followed by Customer A with 860. Customer C trails with 360 points. The points system really shows how spending translates into rewards, giving customers an extra nudge to keep coming back and try more of Danny’s delicious menu.
10. In the first week after a customer joins the program (including the 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?
-- Calculate Customer Points before end of January
SELECT
s.customer_id,
SUM(
CASE
WHEN s.order_date BETWEEN m.join_date AND m.join_date + INTERVAL '7 DAYS' THEN (me.price * 10 * 2)
ELSE
CASE
WHEN me.product_name ILIKE '%sushi%' THEN (me.price * 10 * 2)
ELSE (me.price * 10)
END
END
) AS points
FROM sales s
JOIN members m
ON s.customer_id = m.customer_id
JOIN menu me
ON s.product_id = me.product_id
WHERE s.order_date <= '2021-01-31'
GROUP BY s.customer_id;With the first-week double points boost in play, Customer A’s points jumped to 1,370, while Customer B’s points remained at 940. This early reward perk clearly gives new members an exciting reason to engage more with the diner, making them feel appreciated and eager to explore the menu.
4 Bonus
4.0.0.1 Join All Things
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.
-- Join All Tables
SELECT
s.customer_id,
s.order_date,
m.product_name,
m.price,
CASE
WHEN s.customer_id IN (
SELECT DISTINCT
m.customer_id
FROM members m
) AND s.order_date < ms.join_date THEN 'N'
WHEN s.customer_id NOT IN (
SELECT DISTINCT
m.customer_id
FROM members m
) THEN 'N'
ELSE 'Y'
END AS member
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
LEFT JOIN members ms
ON s.customer_id = ms.customer_id
ORDER BY s.customer_id, s.order_date ;Looking at all the customer activity together, we can see Customer A really dives into Danny’s menu, alternating between curry and ramen, while Customer B seems to enjoy a mix of sushi and ramen. Mapping their purchases alongside membership status paints a full picture of each customer’s journey, helping Danny’s Diner understand not just what people buy, but how their loyalty evolves over time.
4.0.0.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.
-- Rank All Things
SELECT
s.customer_id,
s.order_date,
m.product_name,
m.price,
CASE
WHEN s.customer_id IN (
SELECT DISTINCT
m.customer_id
FROM members m
) AND s.order_date < ms.join_date THEN 'N'
WHEN s.customer_id NOT IN (
SELECT DISTINCT
m.customer_id
FROM members m
) THEN 'N'
ELSE 'Y'
END AS member,
CASE
WHEN
CASE
WHEN s.customer_id IN (
SELECT DISTINCT
m.customer_id
FROM members m
) AND s.order_date < ms.join_date THEN 'N'
WHEN s.customer_id NOT IN (
SELECT DISTINCT
m.customer_id
FROM members m
) THEN 'N'
ELSE 'Y'
END ILIKE 'N' THEN NULL
ELSE DENSE_RANK() OVER(
PARTITION BY
s.customer_id,
CASE
WHEN s.customer_id IN (
SELECT DISTINCT
m.customer_id
FROM members m
) AND s.order_date < ms.join_date THEN 'N'
WHEN s.customer_id NOT IN (
SELECT DISTINCT
m.customer_id
FROM members m
) THEN 'N'
ELSE 'Y'
END
ORDER BY s.customer_id, s.order_date)
END AS ranking
FROM sales s
JOIN menu m
ON s.product_id = m.product_id
LEFT JOIN members ms
ON s.customer_id = ms.customer_id;By ranking the purchases of members, we can clearly see each customer’s favorites over time. Customer A shows a clear preference for ramen, while Customer B spreads their love across multiple dishes, especially sushi. This ranking not only highlights their top picks but also shows how joining the loyalty program shapes their choices, giving Danny a better understanding of both favorite items and evolving tastes.
5 Conclusion
Working through the Danny’s Diner case study was a hands-on way to sharpen my SQL skills while exploring real-world business questions. By creating the database and its tables, analyzing customer behavior, and calculating spending, visits, and loyalty points, I was able to uncover meaningful insights about how customers interact with the diner.
I found that customers show distinct preferences—some are consistent fans of certain dishes, while others explore the menu more broadly. Membership clearly influences ordering behavior, encouraging both loyalty and experimentation. Creating joined and ranked datasets also highlighted how data can be structured for easy access, enabling Danny and his team to make faster, smarter decisions without diving into complex SQL queries every time.
Overall, this project reinforced the importance of combining SQL with business thinking: it’s not just about writing queries, but about turning data into actionable insights that can guide strategy and enhance the customer experience.