-
Notifications
You must be signed in to change notification settings - Fork 0
/
02_rank.sql
108 lines (50 loc) · 1.58 KB
/
02_rank.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
-- region Real example) Employee progress - ranking
WITH employee_perf AS (
SELECT
fullname,
fiscalyear,
salestotal - lag(salestotal) OVER (PARTITION BY fullname ORDER BY fiscalyear) AS year_progress,
salestotal - avg(salestotal) OVER (PARTITION BY fiscalyear) AS diff_from_avg
FROM sales.vsalespersonsalesbyfiscalyearsdata
ORDER BY fullname, fiscalyear
)
SELECT
perf.*,
rank() OVER (PARTITION BY fiscalyear ORDER BY diff_from_avg DESC) AS year_avg_rank,
percent_rank() OVER (PARTITION BY fiscalyear ORDER BY diff_from_avg DESC)
FROM employee_perf perf
ORDER BY year_avg_rank, perf.fiscalyear;
-- endregion
-- region Rank functions - compare
SELECT
letter,
RANK() OVER (ORDER BY letter),
DENSE_RANK() OVER (ORDER BY letter),
NTILE(3) OVER (ORDER BY letter)
FROM regexp_split_to_table('a a a b c c d e', E'\\s+') letter;
-- region refactor
SELECT
letter,
RANK() OVER (w),
DENSE_RANK() OVER (w),
NTILE(3) OVER (w)
FROM regexp_split_to_table('a a a b c c d e', E'\\s+') letter
WINDOW w AS (ORDER BY letter);
-- endregion
-- endregion
-- region NTILE ~~> Different call-center scripts for different customers
WITH Sales AS (
SELECT
soh.customerid,
COUNT(*),
SUM(soh.totaldue) AS total_sales
FROM sales.salesorderheader AS soh
JOIN sales.salesorderdetail sod ON soh.salesorderid = sod.salesorderid
GROUP BY customerid
)
SELECT
o.*,
NTILE(4) OVER (ORDER BY o.total_sales ) AS bucket
FROM Sales o
ORDER BY o.total_sales DESC;
-- endregion