Case Study #1 - Danny's Diner

Please note that the case study information provided below has been sourced from the following link: https://8weeksqlchallenge.com/case-study-1/

Table of Contents

Case Study Introduction Problem Statement Table Relationship Question and Solution Database Creation & Table Populating Question-1 Question-2 Question-3 Question-4 Question-5 Question-6 Question-7 Question-8 Question-9 Question-10 Bonus Questions Join All The Things Rank All The Things 2 Cents

Case Study 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 dive into his customer data to uncover key insights: their visit patterns, spending habits, and favorite menu items. This will help him enhance his customer experience and decide whether to expand his loyalty program.

To make this process smoother, he needs some basic datasets and SQL queries to analyze the data without complex SQL skills. Danny has provided sample data for privacy reasons and expects these examples to be enough to craft effective queries.

You have three key datasets to work with:

sales
menu
members.

Table Relationship

Question and Solution

I am going to use MySQL Workbench to solve these queries.

Database Creation & Table Populating


-- Create the schema (in MySQL, you can use a database instead of schema)
CREATE DATABASE dannys_diner;
USE dannys_diner;

-- Create the sales table
CREATE TABLE sales (
  customer_id VARCHAR(1),
  order_date DATE,
  product_id INT
);

-- Insert data 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);

-- Create the menu table
CREATE TABLE menu (
  product_id INT,
  product_name VARCHAR(5),
  price INT
);

-- Insert data into menu table
INSERT INTO menu (product_id, product_name, price) VALUES
  (1, 'sushi', 10),
  (2, 'curry', 15),
  (3, 'ramen', 12);

-- Create the members table
CREATE TABLE members (
  customer_id VARCHAR(1),
  join_date DATE
);

-- Insert data into members table
INSERT INTO members (customer_id, join_date) VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

Question-1

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

To determine the total amount spent by each customer, perform a LEFT JOIN between the sales and menu tables on the product_id field.

Then, aggregate the total spending using the SUM() function and group the results by customer_id.


select
    s.customer_id, SUM(m.price) as total_sales
from
    sales s
        left join 
    menu m ON s.product_id = m.product_id
group by s.customer_id
order by s.customer_id;

Answer:

Customer A spent $76.
Customer B spent $74.
Customer C spent $36.

Question-2

How many days has each customer visited the restaurant?

To find the number of unique visit days for each customer, use COUNT(DISTINCT order_date) to avoid counting duplicate visits on the same day. Group the results by customer_id to get the visit count for each customer.


select
    customer_id, count(distinct order_date) as cust_visit
from
    sales
group by customer_id;

Answer:

Customer A visited 4 times.

Customer B visited 6 times.

Customer C visited 2 times.

Now that we're aligned on the approach, I'll provide code and output only for upcoming questions. If you notice any errors or have doubts, please feel free to reach out to me on LinkedIn or through my contact form.

Question-3

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


with cte as (
		select s.customer_id, s.order_date, m.product_name,
        dense_rank() over(partition by s.customer_id order by s.order_date) as rnk
	  from sales s
	  left join menu m on s.product_id = m.product_id
	)
	select customer_id, product_name
	from cte
	WHERE rnk = 1
    group by customer_id, product_name;

Question-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(m.product_name) as total_count
from sales s 
left join menu m
on s.product_id = m.product_id
group by m.product_name
order by total_count desc
limit 1;

Question-5

Which item was the most popular for each customer?


with cte as
(
select s.customer_id, m.product_name,count(m.product_id) as order_count,
dense_rank() over(partition by s.customer_id order by count(s.customer_id) desc) as rnk
from sales s
left join menu m on s.product_id = m.product_id
group by s.customer_id, m.product_name
)
select customer_id, product_name, order_count 
from cte where rnk = 1;

Question-6

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


with cte as 
(
select s.customer_id, s.order_date, me.product_name, m.join_date,
row_number() over(partition by s.customer_id order by s.order_date) as rn
from members m 
inner join sales s
on m.customer_id = s.customer_id and s.order_date > m.join_date
inner join menu me
on s.product_id = me.product_id
)
select customer_id, product_name from cte
where rn =1
order by customer_id;

Question-7

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


with cte as (
select s.customer_id, s.order_date, me.product_name, m.join_date,
dense_rank() over(partition by s.customer_id order by s.order_date desc) as rn
from sales s
join members m
on s.customer_id = m.customer_id and s.order_date < m.join_date
join menu me
on s.product_id = me.product_id)

select customer_id, group_concat(product_name order by product_name) as last_order 
from cte
where rn =1
group by customer_id;

Question-8

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


with cte as 
(
select s.customer_id, m.join_date, s.order_date, me.product_id, me.product_name, me.price
from sales s
join members m
on s.customer_id = m.customer_id and s.order_date < m.join_date
join menu me
on s.product_id = me.product_id
)
select customer_id, count(product_name) as item_purchased, sum(price) as total_spend
from cte
group by customer_id
order by customer_id;

Question-9

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

Let’s breakdown the population

Point Calculation:

Base points: Every $1 spent earns 10 points.
Bonus points: Product_id 1 (sushi) earns double points (20 points per $1).

Calculation:

Conditional points:

If the product is sushi (product_id 1):

Earn 20 points per $1.

If the product is not sushi:

Earn 10 points per $1.

Total points: Calculate the total points for each customer based on their purchases and the respective point values.


select 
  s.customer_id,
  sum(
    case
      when m.product_name = 'sushi' then m.price * 20 
      else m.price * 10 
    end
  ) as total_points
from sales s
left join menu m on s.product_id = m.product_id
group by s.customer_id;

Question-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 customer A and B have at the end of January?

Assumptions:

In the first week after a customer joins the program (including their join date), each $1 spent earns 20 points.
From Day 8 to the end of January 2021, each $1 spent earns 10 points.

Steps:

Join the sales, members, and menu tables:

Ensure that the order_date is not before the join_date of the customer.
Join the tables based on customer_id and product_id.

Calculate points using a CASE statement:

For orders placed within the first week of membership (from join_date to 6 days after):

Multiply the item price by 20.

For orders placed after the first week but within January 2021:

Multiply the item price by 10.

Sum the points for each customer and filter results up to January 31, 2021.


select s.customer_id,
sum(
  case 
    when s.order_date between m.join_date and date_add(m.join_date, interval 6 day) 
    then me.price * 20
    else me.price * 10
  end
) as total_points
from sales s
join members m on s.customer_id = m.customer_id and s.order_date >= m.join_date
join menu me on s.product_id = me.product_id
where s.order_date <= '2021-01-31' 
group by s.customer_id
order by s.customer_id;

Bonus Questions

Join All The Things

Create basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL. Fill Member column as 'N' if the purchase was made before becoming a member and 'Y' if the after is amde after joining the membership.


select
  s.customer_id, s.order_date, m.product_name, m.price,
  case 
    when mem.join_date is not null and s.order_date >= mem.join_date then 'Y' 
    else 'N' 
  end as 'Member'
from sales s
left join menu m on s.product_id = m.product_id
left join members mem on s.customer_id = mem.customer_id
order by s.customer_id, s.order_date;

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 as (
select
  s.customer_id,
  s.order_date,
  m.product_name,
  m.price,
  case 
    when mem.join_date is not null and s.order_date >= mem.join_date then 'Y' 
    else 'N' 
  end as Member
from sales s
left join menu m on s.product_id = m.product_id
left join members mem on s.customer_id = mem.customer_id)

select * ,
case 
	when Member = 'Y' then 
	dense_rank() over(partition by customer_id, Member order by order_date)
    else
	null
	end as 'ranking'
from cte;

2 Cents

Three simple tables, sales, menu, membership. Sounded straightforward going in. Took a bit longer than expected to actually get something useful out of it.

First thing that stood out: Customer A spent the most, $76, but Customer B was almost right behind at $74 while visiting nearly twice as often. That distinction mattered more than I initially gave it credit for, spending tells you value today, visit frequency tells you something closer to loyalty. Treating them as the same metric would've missed half the story.

Ramen won, clearly, 8 purchases across everyone. But breaking it down by customer got more interesting, A and C both leaned hard into ramen, B was scattered across sushi, curry, and ramen fairly evenly. Small dataset, but enough to see that "favourite item" isn't the same question as "favourite item for this specific person."

The membership angle is where things got genuinely useful. A and B joined partway through January, and both had meaningful spending before they signed up, which made me realize Danny could've been targeting these two as members earlier instead of waiting for them to opt in on their own. Comparing behaviour before and after membership also opened up a way to ask whether the loyalty programme was actually doing anything, more engagement, more spend, more variety, or just a badge with no real effect.

Points ended up being their own small story too, A, B, and C accumulated 860, 940, and 360 points respectively, and it lined up almost exactly with the spend-versus-visits pattern already showing up elsewhere. B's higher visit count pushed points up despite lower total spend, which felt like a nice confirmation that the earlier read on the data wasn't a fluke.

By the end, three customers turned into three fairly distinct profiles, A as high-value, B as high-frequency, C as lower-frequency but clear on preference. Nothing revolutionary, but enough to move from "here's some sales data" to "here's who Danny should actually be targeting and how."

Biggest takeaway, and I keep landing on some version of this every time, SQL gives you the answer. Understanding the business question well enough to ask the right one is the actual skill. The bonus questions reinforced this too, building a combined dataset instead of repeatedly querying raw tables felt like the first real taste of thinking in terms of reusable analysis rather than one-off queries.

Small dataset, but it touched customer segmentation, revenue analysis, loyalty programmes, ranking, and data prep, more ground than three tables should reasonably be able to cover.