SQL
Saved to variable
salesSELECT *
FROM 'sales.csv'
customer_idobject
A40%
B40%
C20%
order_dateobject
2021-01-0133.3%
2021-01-1120%
6 others46.7%
0
A
2021-01-01
1
A
2021-01-01
2
A
2021-01-07
3
A
2021-01-10
4
A
2021-01-11
5
A
2021-01-11
6
B
2021-01-01
7
B
2021-01-02
8
B
2021-01-04
9
B
2021-01-11
10
B
2021-01-16
11
B
2021-02-01
12
C
2021-01-01
13
C
2021-01-01
14
C
2021-01-07
SQL
Saved to variable
menuSELECT *
FROM 'menu.csv'
product_idint64
product_nameobject
0
1
sushi
1
2
curry
2
3
ramen
SQL
Saved to variable
membersSELECT *
FROM 'members.csv'
customer_idobject
join_dateobject
0
A
2021-01-07
1
B
2021-01-09
SQL
Saved to variable
df_3
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;
customer_idobject
Total_Spentint64
0
A
76
1
B
74
2
C
36
SQL
Saved to variable
df_5SELECT sales.customer_id,
count(distinct sales.order_date) as Total_days
FROM sales
GROUP BY sales.customer_id;
customer_idobject
Total_daysint64
0
A
4
1
B
6
2
C
2
SQL
Saved to variable
df_6WITH 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
customer_idobject
product_nameobject
0
A
sushi
1
A
curry
2
B
curry
3
C
ramen
SQL
Saved to variable
df_7SELECT 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
product_nameobject
most_purchasedint64
0
ramen
8
SQL
Saved to variable
df_8WITH 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;
customer_idobject
product_nameobject
0
C
ramen
1
B
curry
2
B
sushi
3
B
ramen
4
A
ramen
SQL
Saved to variable
df_9WITH 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;
customer_idobject
product_nameobject
0
B
sushi
1
A
curry
SQL
Saved to variable
df_10WITH 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
customer_idobject
product_nameobject
0
B
sushi
1
A
sushi
2
A
curry
SQL
Saved to variable
df_11SELECT 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;
customer_idobject
total_itemint64
0
A
2
1
B
3
SQL
Saved to variable
df_12WITH 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;
customer_idobject
total_pointint64
0
A
860
1
B
940
2
C
360
SQL
Saved to variable
df_13WITH 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;
DBAPIError: (builtins.RuntimeError) Catalog Error: Scalar Function with name dateadd does not exist!
Did you mean "datepart"?
[SQL: 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;
/* {"app":"deepnote","submitted_by":"user","user_email":"nguyenphungducanh@gmail.com","workspace_id":"6f69c9f7-d5f8-4166-8638-8ef06e79bdce","project_id":"9729da4c-f1fd-4341-a6d4-28109c0dd1cc","notebook_id":"364e9aeabc574236942c4900d6cc011c","project_url":"https://deepnote.com/project/9729da4c-f1fd-4341-a6d4-28109c0dd1cc"} */]
(Background on this error at: https://sqlalche.m...
SQL
Saved to variable
df_14WITH 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
customer_idobject
A40%
B40%
C20%
order_dateobject
2021-01-0133.3%
2021-01-1120%
6 others46.7%
0
A
2021-01-01
1
A
2021-01-01
2
A
2021-01-07
3
A
2021-01-10
4
A
2021-01-11
5
A
2021-01-11
6
B
2021-01-01
7
B
2021-01-02
8
B
2021-01-04
9
B
2021-01-11
10
B
2021-01-16
11
B
2021-02-01
12
C
2021-01-01
13
C
2021-01-01
14
C
2021-01-07
SQL
Saved to variable
df_15WITH 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
customer_idobject
A40%
B40%
C20%
order_dateobject
2021-01-0133.3%
2021-01-1120%
6 others46.7%
0
A
2021-01-07
1
A
2021-01-10
2
A
2021-01-11
3
A
2021-01-11
4
A
2021-01-01
5
A
2021-01-01
6
B
2021-01-11
7
B
2021-01-16
8
B
2021-02-01
9
B
2021-01-01
10
B
2021-01-02
11
B
2021-01-04
12
C
2021-01-01
13
C
2021-01-01
14
C
2021-01-07