forked from DaveOnData/SQLForExcelUsers
-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLForExcelUsersPart11.sql
156 lines (138 loc) · 3.59 KB
/
SQLForExcelUsersPart11.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
144
145
146
147
148
149
150
151
152
153
154
155
156
--
-- Get a sense of FactInternetSales data by
-- looking at a single customer.
--
SELECT *
FROM FactInternetSales FIS
WHERE FIS.CustomerKey = 19766
ORDER BY FIS.CurrencyKey, FIS.SalesOrderNumber
--
-- Use a CTE to group customer sales orders.
--
WITH CustomerSalesOrders AS
(
SELECT FIS.CustomerKey
,FIS.SalesOrderNumber
,SUM(SalesAmount) AS SalesAmount
FROM FactInternetSales FIS
GROUP BY FIS.CustomerKey, FIS.SalesOrderNumber
)
SELECT *
FROM CustomerSalesOrders CSO
ORDER BY CSO.CustomerKey;
--
-- By using a CTE, we can work directly with the
-- named virtual table. Aggregate sales orders
-- by customer.
--
WITH CustomerSalesOrders AS
(
SELECT FIS.CustomerKey
,FIS.SalesOrderNumber
,SUM(SalesAmount) AS SalesAmount
FROM FactInternetSales FIS
GROUP BY FIS.CustomerKey, FIS.SalesOrderNumber
)
SELECT CSO.CustomerKey
,COUNT(*) AS SalesOrderCount
,SUM(CSO.SalesAmount) AS SalesAmount
FROM CustomerSalesOrders CSO
GROUP BY CSO.CustomerKey
ORDER BY CSO.CustomerKey;
--
-- SQL supports defining multiple CTEs! This allows all
-- kinds of analytics goodness as we shall see.
/*
WITH <CTE name #1 /> AS
(
<CTE query />
),
<CTE name #2 /> AS
(
<CTE query />
),
<CTE name X /> AS
(
<CTE query />
)
SELECT <CTE columns />
FROM <CTE name />;
*/
--
-- Rewrite the last query using multiple CTEs.
--
WITH CustomerSalesOrders AS
(
SELECT FIS.CustomerKey
,FIS.SalesOrderNumber
,SUM(SalesAmount) AS SalesAmount
FROM FactInternetSales FIS
GROUP BY FIS.CustomerKey, FIS.SalesOrderNumber
),
CustomerSalesOrderHistory AS
(
SELECT CSO.CustomerKey
,COUNT(*) AS SalesOrderCount
,SUM(CSO.SalesAmount) AS SalesAmount
FROM CustomerSalesOrders CSO
GROUP BY CSO.CustomerKey
)
SELECT *
FROM CustomerSalesOrderHistory CSOH
ORDER BY CSOH.CustomerKey;
--
-- Implement the "FM analysis" using the mighty
-- NTILE window function. The easiest way to
-- understand NTILE is to see it in action.
--
WITH CustomerSalesOrders AS
(
SELECT FIS.CustomerKey
,FIS.SalesOrderNumber
,SUM(SalesAmount) AS SalesAmount
FROM FactInternetSales FIS
GROUP BY FIS.CustomerKey, FIS.SalesOrderNumber
),
CustomerSalesOrderHistory AS
(
SELECT CSO.CustomerKey
,COUNT(*) AS SalesOrderCount
,SUM(CSO.SalesAmount) AS SalesAmount
FROM CustomerSalesOrders CSO
GROUP BY CSO.CustomerKey
)
SELECT CSOH.CustomerKey
,NTILE(10) OVER (ORDER BY CSOH.SalesOrderCount ASC) AS FrequencyScore
,NTILE(10) OVER (ORDER BY CSOH.SalesAmount ASC) AS MonetaryScore
FROM CustomerSalesOrderHistory CSOH
ORDER BY CSOH.CustomerKey;
--
-- Let's say I was just interested in the 10-10 customers.
--
WITH CustomerSalesOrders AS
(
SELECT FIS.CustomerKey
,FIS.SalesOrderNumber
,SUM(SalesAmount) AS SalesAmount
FROM FactInternetSales FIS
GROUP BY FIS.CustomerKey, FIS.SalesOrderNumber
),
CustomerSalesOrderHistory AS
(
SELECT CSO.CustomerKey
,COUNT(*) AS SalesOrderCount
,SUM(CSO.SalesAmount) AS SalesAmount
FROM CustomerSalesOrders CSO
GROUP BY CSO.CustomerKey
),
RFM AS
(
SELECT CSOH.CustomerKey
,NTILE(10) OVER (ORDER BY CSOH.SalesOrderCount ASC) AS FrequencyScore
,NTILE(10) OVER (ORDER BY CSOH.SalesAmount ASC) AS MonetaryScore
FROM CustomerSalesOrderHistory CSOH
)
SELECT *
FROM RFM FM
WHERE FM.FrequencyScore = 10 AND FM.MonetaryScore = 10
ORDER BY FM.CustomerKey