Sasidharan Sarvoy Sathiyamoorthy - Danny's Diner SQL Case Study

Case study solutions were implemented on DB Fiddle platform using PostgreSQL v13.

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 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 favourite. Having this deeper connection with his customers will help him deliver a better and more personalised 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:

sales
menu
members

You can inspect the entity relationship diagram and tables below.

The sales table captures all customer_id level purchases with an corresponding order_date and product_id information for when and what menu items were ordered.

The menu table maps the product_id to the actual product_name and price of each menu item.

The final members table captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program.

Case Study Questions

Each of the following case study questions can be answered using a single SQL statement:

What is the total amount each customer spent at the restaurant?
How many days has each customer visited the restaurant?
What was the first item from the menu purchased by each customer?
What is the most purchased item on the menu and how many times was it purchased by all customers?
Which item was the most popular for each customer?
Which item was purchased first by the customer after they became a member?
Which item was purchased just before the customer became a member?
What is the total items and amount spent for each member before they became a member?
If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?
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.

Solutions

Creating the schema and data tables

CREATE SCHEMA dannys_diner;
SET search_path = dannys_diner;

CREATE TABLE sales (
  "customer_id" VARCHAR(1),
  "order_date" DATE,
  "product_id" INTEGER
);

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 TABLE menu (
  "product_id" INTEGER,
  "product_name" VARCHAR(5),
  "price" INTEGER
);

INSERT INTO menu
  ("product_id", "product_name", "price")
VALUES
  ('1', 'sushi', '10'),
  ('2', 'curry', '15'),
  ('3', 'ramen', '12');
  

CREATE TABLE members (
  "customer_id" VARCHAR(1),
  "join_date" DATE
);

INSERT INTO members
  ("customer_id", "join_date")
VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

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

select s.customer_id, sum(m.price)
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id
group by s.customer_id;

Customer A has spent $76, B has spent $74 and C has spent $36.

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

select customer_id, count(distinct order_date)
from dannys_diner.sales
group by customer_id;

Customer B has visited the diner on 6 different days, while customer A has visited on 4 days and customer C has visited on 2 days.

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

with sales_order as

(select s.customer_id, s.order_date, s.product_id, m.product_name, dense_rank() over (partition by s.cutomer_id order by s.order_date asc) as rank
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id)

select customer_id, product_name
from sales_order
where rank = 1
group by customer_id, product_name;

Since the order dates do not have timestamps we cannot differentiate between two products bought on the same day. The first items bought by customer A are curry and sushi, curry and ramen for customers B and C respectively.

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

Ramen is the most purchased item on the menu at the diner, with a total of 8 purchases by all customers.

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

with item_rank as

(select s.customer_id, m.product_name, count(s.product_id), dense_rank() over (partition by s.customer_id order by count(s.product_id) desc) as rank
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id
group by s.customer_id, m.product_name)

select customer_id, product_name, count
from item_rank
where rank = 1;

Ramen is the most popular item for both customers A and C, while customer B likes ramen, curry and sushi equally.

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

with purchase_rank as

(select s.customer_id, s.product_id, m.product_name, s.order_date, j.join_date, dense_rank() over (partition by s.customer_id order by s.order_date asc) as rank
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id
inner join dannys_diner.members j
on s.customer_id = j.customer_id
where s.order_date >= j.join_date)

select customer_id, product_name
from purchase_rank
where rank = 1;

Customer A purchased curry after becoming a member and customer B purhased sushi. Customer C was not a member.

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

with purchase_rank as

(select s.customer_id, s.product_id, m.product_name, s.order_date, j.join_date, dense_rank() over (partition by s.customer_id order by s.order_date asc) as rank
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id
inner join dannys_diner.members j
on s.customer_id = j.customer_id
where s.order_date < j.join_date)

select customer_id, product_name
from purchase_rank
where rank = 1;

Customer A purchased sushi and curry just before they became a member, while customer B purchased sushi.

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

select s.customer_id, count(distinct s.product_id) as total_items, sum(m.price) as total_purchase_amt
from dannys_diner.sales s
inner join dannys_diner.menu m
on s.product_id = m.product_id
inner join dannys_diner.members j
on s.customer_id = j.customer_id
where s.order_date < join_date
group by s.customer_id;

Customer A purchased 2 different items for a total value of $25, while customer B purchased 2 items for a value of $40.

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

with points_earned as 

(select s.customer_id, case
when m.product_name = 'sushi' then m.price*2*10
else m.price*10
end as points
from dannys_diner.sales s
inner join dannys_diner.meanu m
on s.product_id = m.product_id)

select customer_id, sum(points)
from points_earned
group by customer_id
order by custoemer_id;

Customer B has the most accumulated points with 940, while customers A and C have 860 and 360 points respectively.

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 as

(select *, join_date + 6 as promo_end, date '2021-01-31' as end_of_month
from dannys_diner.members)

select s.customer_id, sum(case
when m.product_name = 'sushi' then m.price*2*10
when s.order_date between d.join_date and d.promo_end then m.price*2*10
else m.price*10 end) as points

from dannys_diner.sales s
inner join dates d
on s.customer_id = d.customer_id
inner join dannys_diner.menu m
on s.product_id = m.product_id

where s.order_date <= end_of_month
group by s.customer_id;

With the first week promo bonus after becoming a member included, customer A has 1370 points and customer B has 820 points at the end of January.

------------------------------------------------------------------------------------

Danny's Diner SQL Case Study

Follow this website