-
Notifications
You must be signed in to change notification settings - Fork 0
/
solution.sql
143 lines (133 loc) · 3.92 KB
/
solution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
--1
SELECT customer_id, SUM(price) AS TotalAmount
FROM sales s INNER JOIN menu m ON s.product_id = m.product_id
GROUP BY customer_id
--2
SELECT customer_id, COUNT(DISTINCT order_date) AS DaysCount
FROM sales
GROUP BY customer_id
--3
SELECT DISTINCT s.customer_id,
m.product_name
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
INNER JOIN
(SELECT customer_id,
MIN(order_date) AS MinDate
FROM sales
GROUP BY customer_id) o ON s.customer_id = o.customer_id
WHERE s.order_date = o.MinDate
--4
SELECT TOP 1 s.product_id,
m.product_name,
COUNT(*) AS ProductCount
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
GROUP BY s.product_id,
m.product_name
ORDER BY ProductCount DESC
--5
WITH CTE AS
(SELECT s.customer_id,
m.product_name,
COUNT(s.product_id) AS ProductCount,
DENSE_RANK() OVER (PARTITION BY s.customer_id
ORDER BY COUNT(s.product_id) DESC) AS ProductRank
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
GROUP BY s.customer_id,
m.product_name)
SELECT customer_id,
product_name,
ProductCount
FROM CTE
WHERE ProductRank = 1
ORDER BY customer_id,
product_name
--6
WITH CTE AS
(SELECT s.customer_id,
mn.product_name,
COUNT(s.product_id) AS ProductCount,
DENSE_RANK() OVER (PARTITION BY s.customer_id
ORDER BY s.order_date) AS ProductRank
FROM sales s
INNER JOIN members m ON s.customer_id = m.customer_id
INNER JOIN menu mn ON s.product_id = mn.product_id
WHERE s.order_date >= m.join_date
GROUP BY s.customer_id,
mn.product_name,
s.order_date)
SELECT customer_id,
product_name,
ProductRank
FROM CTE
WHERE ProductRank = 1
--7
WITH CTE AS
(SELECT s.customer_id,
mn.product_name,
COUNT(s.product_id) AS ProductCount,
DENSE_RANK() OVER (PARTITION BY s.customer_id
ORDER BY s.order_date DESC) AS ProductRank
FROM sales s
INNER JOIN members m ON s.customer_id = m.customer_id
INNER JOIN menu mn ON s.product_id = mn.product_id
WHERE s.order_date < m.join_date
GROUP BY s.customer_id,
mn.product_name,
s.order_date)
SELECT customer_id,
product_name,
ProductRank
FROM CTE
WHERE ProductRank = 1
--8
SELECT s.customer_id,
COUNT(s.product_id) TotalItems,
SUM(mn.price) AS AmountSpent
FROM sales s
INNER JOIN members m ON s.order_date < m.join_date
INNER JOIN menu mn ON s.product_id = mn.product_id
WHERE s.customer_id = m.customer_id
GROUP BY s.customer_id
--9
WITH CTE AS
(SELECT s.customer_id,
m.product_name,
CASE
WHEN m.product_name = 'sushi' THEN SUM(m.price) * 20
ELSE SUM(m.price) * 10
END AS Points
FROM sales s
INNER JOIN menu m ON s.product_id = m.product_id
GROUP BY s.customer_id,
m.product_name)
SELECT customer_id,
SUM(Points) AS TotalPoints
FROM CTE
GROUP BY customer_id
--10
WITH CTE AS
(SELECT o.customer_id,
product_name,
(CASE
WHEN product_name = 'sushi'
OR (OrderJoinDateDiff > -1
AND OrderJoinDateDiff < 8) THEN price * 20
ELSE price * 10
END) AS Points
FROM
(SELECT s.customer_id,
s.product_id,
mn.product_name,
mn.price,
DATEDIFF(DAY, join_date, order_date) AS OrderJoinDateDiff
FROM sales s
INNER JOIN members m ON s.customer_id = m.customer_id
INNER JOIN menu mn ON s.product_id = mn.product_id
WHERE order_date <= '01/31/2021') o)
SELECT customer_id,
SUM(Points) AS TotalPoints
FROM CTE
GROUP BY customer_id