![](https://crypto4nerd.com/wp-content/uploads/2023/06/0nRhmFF_AXlgrrWPb-1024x1024.png)
Solutions for Case Study Questions:
[1] What is the total amount each customer spent at the restaurant?
SELECT customer_id,SUM(price)AS Total
FROM sales
JOIN menu
ON sales.product_id = menu.product_id
GROUP BY customer_id;
The query returns the Total Amount each customer has spent. The query first joins the sales
and menu
tables on the product_id
column. Then the SUM() calculates the total amount and the GROUP BY() function aggregates the total amount customer-wise.
[2] How many days has each customer visited the restaurant?
SELECT customer_id,COUNT(DISTINCT order_date) AS Visits
FROM sales
GROUP BY customer_id;
The query will first select the customer ID and the order date from the sales table. The COUNT()
function will then be used to count the number of distinct order dates for each customer. The results will then be grouped by customer ID and the query will return the results.
[3] What was the first item from the menu purchased by each customer?
SELECT customer_id, MIN(order_date) AS First_Purchase_Date,
product_name AS Product
FROM sales
JOIN menu ON sales.product_id = menu.product_id
GROUP BY customer_id;
The query will join sales
and menu
on product_id
column. It will then group the results by the customer_id
column and select the minimum order_date
for each customer_id
.
[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(*) AS num_purchased
FROM sales s
JOIN menu m ON s.product_id = m.product_id
GROUP BY m.product_name
ORDER BY num_purchased DESC
LIMIT 1;
The query will join sales
and menu
on product_id
column. It will then group the results by product_name
and counts the number of times the product has been purchased and it is arranged in Descending order, finally the LIMIT()
clause filters only the first output which is the number of times the most purchased item was purchased.
[5] Which item was the most popular for each customer?
SELECT s.customer_id AS Customer, m.product_name AS Product,COUNT(s.product_id) AS PopularItem
FROM sales s
JOIN menu m
ON s.product_id=m.product_id
GROUP BY s.customer_id;
The query will join sales
and menu
on product_id
column. It will then group the results by customer_id
, counts the number of times the product has been purchased, and selects the respective product_name
.
[6] Which item was purchased first by the customer after they became a member?
SELECT s.customer_id AS Customer, m.date_of_joining AS Joining_Date, s.order_date, p.product_name AS Product
FROM sales s
JOIN menu p
ON s.product_id = p.product_id
JOIN members m
ON s.customer_id = m.customer_id
WHERE s.order_date>=m.date_of_joining
GROUP BY s.customer_id;
The query will then group the data by the customer ID and return one row for each customer. The Joining Date
column will show the date that the customer joined the company. The Order Date
column will show the date that the customer placed their first order. The Product Name
column will show the name of the product that the customer purchased on their first order.
The WHERE
clause is used to filter the results so that only rows where the order_date
is greater than or equal to the date_of_joining
are returned. This ensures that the query only returns rows for customers who have placed an order after they joined the company.
The GROUP BY
clause is used to group the data by the customer ID. This ensures that the query returns one row for each customer, even if the customer has placed multiple orders.
[7] Which item was purchased just before the customer became a member?
SELECT s.customer_id AS Customer, s.order_date,m.date_of_joining AS Joining_Date, p.product_name AS Product
FROM sales s
JOIN menu p
ON s.product_id = p.product_id
JOIN members m
ON s.customer_id = m.customer_id
WHERE s.order_date<m.date_of_joining
GROUP BY s.customer_id
ORDER BY s.order_date DESC;
The query first joins the sales, menu, and members tables on the customer_id column. This ensures that only rows where the customer ID is the same in all three tables are selected.
The query then uses the WHERE clause to filter out any rows where the order date is less than the joining date. This ensures that only rows where the customer placed an order before they joined the membership program are selected.
The query then uses the GROUP BY clause to group the results by customer ID. This ensures that each customer only appears once in the results, even if they placed multiple orders.
Finally, the query uses the ORDER BY clause to order the results by order date in descending order. This ensures that the results are sorted by the most recent order date first.
[8] What are the total items and amount spent for each member before they became a member?
SELECT s.customer_id AS Customer, COUNT(s.product_id),SUM(p.price)
FROM sales s
JOIN menu p
ON s.product_id=p.product_id
JOIN members m
ON s.customer_id=m.customer_id
WHERE s.order_date<m.date_of_joining
GROUP BY s.customer_id;
The query works by first joining the sales, menu, and members tables together on the customer ID column. This ensures that only rows where the customer ID is the same in all three tables are returned.
The WHERE clause is then used to filter out any rows where the order date is before the date of joining. This ensures that the query only returns information for customers who have made purchases after they joined the membership program.
Finally, the GROUP BY clause is used to group the results by customer ID. This ensures that the number of products purchased and the total amount spent is calculated for each customer individually.
[9] If each $1 spent equates to 10 points and sushi has a 2x points multiplier. How many points would each customer have?
SELECT customer_id AS Customer,SUM(CASE WHEN product_name='sushi' then price*20 ELSE price*10 END) AS Points
FROM sales
JOIN menu
ON sales.product_id=menu.product_id
GROUP BY customer_id;
The query works by first joining the sales and menu tables together on the product ID column. This ensures that only rows where the product ID is the same in both tables are returned.
The CASE statement is then used to calculate the total points earned for each customer. If the product name is sushi, then the price is multiplied by 20. Otherwise, the price is multiplied by 10. The results are then grouped by 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. How many points do customers A and B have at the end of January?
SELECT
members.customer_id,
SUM(CASE
WHEN sales.order_date >= members.date_of_joining AND sales.order_date < DATE_ADD(members.date_of_joining, INTERVAL 7 DAY) THEN price * 20
WHEN product_name = 'sushi' THEN price * 20
ELSE price * 10
END) AS points
FROM members
LEFT JOIN sales
ON members.customer_id = sales.customer_id
LEFT JOIN menu
ON sales.product_id = menu.product_id
GROUP BY members.customer_id;
The query works by first joining the members, sales, and menu tables together on the customer ID column. This ensures that only rows where the customer ID is the same in all three tables are returned.
The CASE statement is then used to calculate the total points earned for each customer. If the order date is within 7 days of the date of joining and the product name is not sushi, then the price is multiplied by 20. If the product name is sushi, then the price is multiplied by 20. Otherwise, the price is multiplied by 10. The results are then grouped by customer ID.