365 Days of Daily Coding: Day 98

I have been working on this SQL challenge for a few days. The challenges here are like real case based scenarios which were totally fun to work on.

At first, I wanted to be a serious SQL student and therefore, run the queries for the challenges in Oracle but I spent nearly a day trying to figure out how to configure my dbeaver to Oracle cloud. I am using Mac and I think that pretty much sums up everything. haha!! Oracle provides free cloud enviroment for anyone wanting to learn it which I think is pretty cool. Unfortunately, at the moment because of setup issues I can’t use it.

The site does not provide the solution so I am thinking of using excel tomorrow to verify my answers from the queries.

Below is my solution for Week 1 challenge.

-- Query 1: Total amount each customer spent at the restaurant
SELECT s.customer_id, SUM(m.price) AS amount_spent 
FROM sales s LEFT JOIN menu m 
ON s.product_id = m.product_id 
GROUP BY s.customer_id ;

--Query 2: Number of days each customer visited the restaurant
SELECT customer_id, COUNT(order_date)  AS no_days
FROM sales
GROUP BY customer_id;

--Query 3: First item from the menu purchased by each customer
SELECT k.customer_id, m.product_name 
FROM 
	(SELECT * FROM (
		SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) AS row_n
		FROM sales)
		WHERE row_n = 1) k LEFT JOIN menu m 
		ON k.product_id = m.product_id;
		
--Query 4: Most purchased item on the menu and the number of times it was purchased by all customers
SELECT product_name, MAX(count) AS max
FROM(
	SELECT m.product_name, COUNT(m.product_name) AS count
	FROM sales s LEFT JOIN menu m 
	ON s.product_id = m.product_id 
GROUP BY m.product_name);

-- 5. Which item was the most popular for each customer?
SELECT customer_id, product_name
FROM(
	SELECT s.customer_id, m.product_name, COUNT(m.product_name) AS count,
	ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY COUNT(m.product_name) DESC) AS row_n
	FROM sales s LEFT JOIN menu m 
	ON s.product_id = m.product_id 
GROUP BY s.customer_id, m.product_name)
WHERE row_n = 1;

-- 6. Which item was purchased first by the customer after they became a member?
SELECT k.customer_id, m.product_name
FROM(
	SELECT s.customer_id, s.product_id, s.order_date, m.join_date,
	ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) AS row_n
	FROM sales s JOIN members m 
	ON s.customer_id = m.customer_id
	WHERE s.order_date > m.join_date) k 
JOIN menu m
ON k.product_id = m.product_id
WHERE k.row_n = 1;

-- 7. Which item was purchased just before the customer became a member?
SELECT k.customer_id, m.product_name
FROM(
	SELECT s.customer_id, s.product_id, s.order_date, m.join_date,
	ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY s.order_date DESC) AS row_n
	FROM sales s JOIN members m 
	ON s.customer_id = m.customer_id
	WHERE s.order_date < m.join_date) k 
JOIN menu m
ON k.product_id = m.product_id
WHERE k.row_n = 1;

-- 8. What is the total items and amount spent for each member before they became a member?
SELECT k.customer_id, SUM(m.price)
FROM(
	SELECT s.customer_id, s.product_id, s.order_date, m.join_date
	FROM sales s JOIN members m 
	ON s.customer_id = m.customer_id
	WHERE s.order_date < m.join_date) k 
JOIN menu m
ON k.product_id = m.product_id
GROUP BY k.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?
SELECT customer_id, SUM(points) As points
FROM (
	SELECT customer_id, 
	CASE product_name WHEN 'sushi' THEN price * 2
	ELSE price * 1 
	END points
	FROM(
		SELECT k.customer_id, m.product_name, SUM(m.price) AS price
		FROM(
			SELECT s.customer_id, s.product_id, s.order_date, m.join_date
			FROM sales s JOIN members m 
			ON s.customer_id = m.customer_id
			WHERE s.order_date >= m.join_date) k 
		JOIN menu m
		ON k.product_id = m.product_id
		GROUP BY k.customer_id, m.product_name))
GROUP 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 customer A and B have at the end of January?

SELECT customer_id, SUM(new_price) AS points
FROM(
	SELECT customer_id, product_name, price, order_date, join_date, validity,
	CASE 
	WHEN order_date <= validity THEN price * 2
	WHEN order_date > validity THEN 
		CASE product_name WHEN 'sushi' THEN price * 2
		ELSE price
		END
	END new_price
	FROM(
		SELECT k.customer_id, k.order_date, k.join_date, date(k.join_date, '+7 day') as validity, u.product_name, u.price
		FROM(
			(SELECT s.customer_id, s.order_date, s.product_id, m.join_date, strftime('%m', s.order_date) AS join_month
			FROM sales s JOIN members m 
			ON s.customer_id = m.customer_id 
			WHERE s.order_date >= m.join_date) k
			JOIN menu u 
			ON k.product_id = u.product_id)
		WHERE join_month = "01"))
GROUP BY customer_id;