SELECT *
FROM 'sales.csv'
SELECT *
FROM 'menu.csv'
SELECT *
FROM 'members.csv'
SELECT customer_id,sum(menu.price) as Total_Spent
FROM sales
Inner join menu
ON sales.product_id = menu.product_id
GROUP BY customer_id;
SELECT sales.customer_id,
count(distinct sales.order_date) as Total_days
FROM sales
GROUP BY sales.customer_id;
WITH PURCHASED_RANK AS (
SELECT customer_id,
order_date,
product_name,
DENSE_RANK() OVER(ORDER BY order_date ) AS RANK
FROM sales
LEFT JOIN menu
ON sales.product_id = menu.product_id
)
SELECT distinct customer_id,product_name
FROM PURCHASED_RANK
WHERE RANK =1
SELECT product_name, (COUNT(s.product_id)) AS most_purchased
FROM sales AS s
JOIN menu AS m
ON s.product_id = m.product_id
GROUP BY s.product_id, product_name
ORDER BY most_purchased DESC
LIMIT 1
WITH rank_favorite_item AS (
SELECT customer_id,
product_name,
COUNT(sales.product_id) AS number_order,
rank() OVER(PARTITION BY customer_id
ORDER BY COUNT(sales.product_id) DESC) AS RANK
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
GROUP BY customer_id,product_name
)
SELECT customer_id,product_name,number_order
FROM rank_favorite_item
WHERE RANK =1;
WITH rank_purchase_item AS(
SELECT sales.customer_id,
product_name,
order_date,
join_date,
DENSE_RANK() OVER(Partition by sales.customer_id ORDER BY order_date) AS RANK
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN members
ON sales.customer_id = members.customer_id
WHERE sales.order_date >= members.join_date
)
SELECT *
FROM rank_purchase_item
WHERE RANK =1;
WITH rank_purchase_order AS (
SELECT sales.customer_id,
product_name,
order_date,
join_date,
DENSE_RANK() OVER(Partition by sales.customer_id ORDER BY order_date DESC) AS RANK
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN members
ON sales.customer_id = members.customer_id
WHERE sales.order_date < members.join_date
)
SELECT *
FROM rank_purchase_order
WHERE RANK =1
SELECT sales.customer_id,
count(product_name) as total_item,
SUM(price) as Total_amount_spent
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN members
ON sales.customer_id = members.customer_id
WHERE sales.order_date < members.join_date
GROUP BY sales.customer_id;
WITH point_table AS(
SELECT product_id,
product_name,
price,
CASE WHEN product_name = 'sushi' THEN price *20
ELSE price * 10 END AS point
FROM menu
)
SELECT customer_id, sum(point) as total_point
FROM sales as s
INNER JOIN point_table as p
ON s.product_id = p.product_id
GROUP BY customer_id;
WITH dates_cte AS(
SELECT *,
DATEADD(DAY, 6, join_date) AS valid_date,
EOMONTH('2021-01-1') AS last_date
FROM members
)
SELECT s.customer_id,
sum(CASE WHEN s.product_id = 1 THEN price*20
WHEN s.order_date between d.join_date and d.valid_date THEN price*20
ELSE price*10
END) as total_points
FROM dates_cte as d,
sales as s,
menu as m
WHERE d.customer_id = s.customer_id and
s.order_date <= d.last_date
GROUP BY s.customer_id;
WITH member_table as (
SELECT s.customer_id,
s.order_date,m.product_name,m.price,
CASE WHEN s.order_date >= me.join_date THEN 'Y'
ELSE 'N' END AS member
FROM sales as s
INNER JOIN menu as m ON s.product_id = m.product_id
LEFT JOIN members as me ON s.customer_id = me.customer_id
)
SELECT *
FROM member_table
WITH member_table as (
SELECT s.customer_id,
s.order_date,m.product_name,m.price,
CASE WHEN s.order_date >= me.join_date THEN 'Y'
ELSE 'N' END AS member
FROM sales as s
INNER JOIN menu as m ON s.product_id = m.product_id
LEFT JOIN members as me ON s.customer_id = me.customer_id
)
SELECT customer_id,
order_date,
product_name,
price,
member,
CASE WHEN member ='N' THEN NULL
ELSE RANK() OVER(PARTITION BY customer_id, member ORDER BY order_date) END AS ranking
FROM member_table