Danny's Dinner

 

                                                                    Danny's Dinner


SQL Challenge - 1


Data set link :

https://8weeksqlchallenge.com/getting-started/

3 key datasets for this case study:



Entity Relationship Diagram



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

create database Dannys_Dinner

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');


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

select * from members

select * from menu

select * from sales

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

Select S.customer_id, Sum(M.price) as Total_Amount_Spent
From Menu m
join Sales s
On m.product_id = s.product_id
group by S.customer_id






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

select s.customer_id, COUNT(distinct(order_date)) as Total_days_Cust_Visit
from sales as s
group by customer_id;





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

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

select customer_id,product_name
from rank
where rank=1





--4.What is the most purchased item on the menu and how many times was it purchased by all customers?

select top 1 m.product_name,count(s.product_id) as most_purc_item
from menu as m
join sales as s
on m.product_id = s.product_id
group by m.product_name
order by count(s.product_id) desc;







--5.Which item was the most popular for each customer?
with rank as ( 
 select s.customer_id,
m.product_name,
count 
(s.product_id) as count,
dense_rank () over (partition by s.customer_id order by count(s.product_id) desc ) as rank
from menu as m
join sales as s
on m.product_id= s.product_id
group by s.customer_id,s.product_id,m.product_name
)
select customer_id,product_name,count
from rank
where rank = 1;



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

With Rank as
(
Select  S.customer_id,
        M.product_name,
Dense_rank() OVER (Partition by S.Customer_id Order by S.Order_date) as Rank
From Sales S
Join Menu M
ON m.product_id = s.product_id
JOIN Members Mem
ON Mem.Customer_id = S.customer_id
Where S.order_date >= Mem.join_date  
)
Select *
From Rank
Where Rank = 1



--7.Which item was purchased just before the customer became a member?
With Rank as
(
Select  S.customer_id,
        M.product_name,
Dense_rank() OVER (Partition by S.Customer_id Order by S.Order_date) as Rank
From Sales S
Join Menu M
ON m.product_id = s.product_id
JOIN Members Mem
ON Mem.Customer_id = S.customer_id
Where S.order_date < Mem.join_date  
)
Select customer_ID, Product_name
From Rank
Where Rank = 1





--8.What is the total items and amount spent for each member before they became a member?
Select S.customer_id,count(S.product_id ) as quantity ,Sum(M.price) as total_sales
From Sales S
Join Menu M
ON m.product_id = s.product_id
JOIN Members Mem
ON Mem.Customer_id = S.customer_id
Where S.order_date < Mem.join_date
Group by S.customer_id



--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 as
(
Select *, Case When product_id = 1 THEN price*20
               Else price*10
       End as Points
From Menu
)
Select S.customer_id, Sum(P.points) as Points
From Sales S
Join Points p
On p.product_id = S.product_id
Group by S.customer_id





--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 - 
Select
        s.customer_id
,Sum(CASE
                 When (DATEDIFF(DAY, me.join_date, s.order_date) between 0 and 7) or (m.product_ID = 1) Then m.price * 20
                 Else m.price * 10
              END) As Points
From members as me
    Inner Join sales as s on s.customer_id = me.customer_id
    Inner Join menu as m on m.product_id = s.product_id
where s.order_date >= me.join_date and s.order_date <= CAST('2021-01-31' AS DATE)
Group by s.customer_id




Thanks

Do follow for more SQL related updates.









Comments

Post a Comment

Popular posts from this blog

SQL Case Study Finance Analysis

Image
                                                                    Finance Analysis SQL Challenge 4 - Challenge 4 - Finance Analysis   create database Finance_Analysis -- Create the Customers table CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, City VARCHAR(50) NOT NULL, State VARCHAR(2) NOT NULL ); -------------------- -- Populate the Customers table INSERT INTO Customers (CustomerID, FirstName, LastName, City, State) VALUES (1, 'John', 'Doe', 'New York', 'NY'), (2, 'Jane', 'Doe', 'New York', 'NY'), (3, 'Bob', 'Smith', 'San Francisco', 'CA'), (4, 'Alice', 'Johnson', 'San Francisco', 'CA'), (5, 'Michael', 'Lee', 'Los Angeles', 'CA'), (6, 'Jennifer', 'Wang', 'Los Angel...
Read more

SQL Query Order of Execution

Image
                                                  SQL Query Order of Execution SQL SQL - ( Structured Query Language ) is the most wanted tool for everyone who is living in IT Industry ,it like breathing air and water -this both can be like EXCEL & SQL without this 2 we can't do any projects. Lets learn about Order of Execution in SQL Why the order of SQL is Important ? Each and every task there will be an ORDER to reach like If we want to land in GOOD IT JOB with the proper role as Business Analyst or Data Analyst  should be good to have an education criteria with IT KINGS- EXCEL , SQL , POWERBI / TABLEAU / MICRO STRATEGY / STATISTICS / STORY TELLING (communication). As like this same way there will be an ORDER in SQL queries SQL Query Order of Execution Why Order of Execution is Important ? Helps to understand the concept easily Can able to debug query...
Read more

A Day in the Life of a Data Analyst Interviewee

                                                            Data Analyst⌛ Am sharing my interview experience held on  Nov:4:2023✅ As usual : Tell me about your previous project work experience and tools used. ☝SQL : Joins - Left join ,Right join, Inner Join, Outer join Like operators - Like 'A%; What is a Stored Procedure?  A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. Different Types of Indexes in SQL Server  There are various types of indexes in SQL server:  Clustered Index  Non-Clustered Index  Column Store Index  Filtered Index  Hash Index  Unique Index  ☺Power BI - ...
Read more