FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
sql-scripts-remember/scripts/16_Window_Ranking.sql at main · DishaniD/sql-scripts-remember · GitHub
DishaniD
/
sql-scripts-remember
Public
forked from
DataWithBaraa/sql-ultimate-course
Notifications
You must be signed in to change notification settings
Fork
0
Star
0
Code
Pull requests
0
Actions
Projects
Security and quality
0
Insights
Additional navigation options
Code
Pull requests
Actions
Projects
Security and quality
Insights
Expand file tree
Breadcrumbs
sql-scripts-remember
/
scripts
/
16_Window_Ranking.sql
Copy path
More file actions
More file actions
Latest commit
History
History
History
147 lines (134 loc) · 4.02 KB
Breadcrumbs
sql-scripts-remember
/
scripts
/
16_Window_Ranking.sql
Copy path
File metadata and controls
147 lines (134 loc) · 4.02 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
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
/*
==============================================================================
SQL Window Ranking Functions
-------------------------------------------------------------------------------
These functions allow you to rank and order rows within a result set
without the need for complex joins or subqueries. They enable you to assign
unique or non-unique rankings, group rows into buckets, and analyze data
distributions on ordered data.
Table of Contents:
1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. NTILE
5. CUME_DIST
=================================================================================
*/
/*
============================================================
SQL WINDOW RANKING | ROW_NUMBER, RANK, DENSE_RANK
============================================================
*/
/*
TASK 1:
Rank Orders Based on Sales from Highest to Lowest
*/
SELECT
OrderID,
ProductID,
Sales,
ROW_NUMBER() OVER (
ORDER BY
Sales
DESC
)
AS
SalesRank_Row,
RANK() OVER (
ORDER BY
Sales
DESC
)
AS
SalesRank_Rank,
DENSE_RANK() OVER (
ORDER BY
Sales
DESC
)
AS
SalesRank_Dense
FROM
Sales
.
Orders
;
/*
TASK 2:
Use Case | Top-N Analysis: Find the Highest Sale for Each Product
*/
SELECT
*
FROM
(
SELECT
OrderID,
ProductID,
Sales,
ROW_NUMBER() OVER (PARTITION BY ProductID
ORDER BY
Sales
DESC
)
AS
RankByProduct
FROM
Sales
.
Orders
)
AS
TopProductSales
WHERE
RankByProduct
=
1
;
/*
TASK 3:
Use Case | Bottom-N Analysis: Find the Lowest 2 Customers Based on Their Total Sales
*/
SELECT
*
FROM
(
SELECT
CustomerID,
SUM
(Sales)
AS
TotalSales,
ROW_NUMBER() OVER (
ORDER BY
SUM
(Sales))
AS
RankCustomers
FROM
Sales
.
Orders
GROUP BY
CustomerID
)
AS
BottomCustomerSales
WHERE
RankCustomers
<=
2
;
/*
TASK 4:
Use Case | Assign Unique IDs to the Rows of the 'Order Archive'
*/
SELECT
ROW_NUMBER() OVER (
ORDER BY
OrderID, OrderDate)
AS
UniqueID,
*
FROM
Sales
.
OrdersArchive
;
/*
TASK 5:
Use Case | Identify Duplicates:
Identify Duplicate Rows in 'Order Archive' and return a clean result without any duplicates
*/
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY OrderID
ORDER BY
CreationTime
DESC
)
AS
rn,
*
FROM
Sales
.
OrdersArchive
)
AS
UniqueOrdersArchive
WHERE
rn
=
1
;
/*
============================================================
SQL WINDOW RANKING | NTILE
============================================================
*/
/*
TASK 6:
Divide Orders into Groups Based on Sales
*/
SELECT
OrderID,
Sales,
NTILE(
1
) OVER (
ORDER BY
Sales)
AS
OneBucket,
NTILE(
2
) OVER (
ORDER BY
Sales)
AS
TwoBuckets,
NTILE(
3
) OVER (
ORDER BY
Sales)
AS
ThreeBuckets,
NTILE(
4
) OVER (
ORDER BY
Sales)
AS
FourBuckets,
NTILE(
2
) OVER (PARTITION BY ProductID
ORDER BY
Sales)
AS
TwoBucketByProducts
FROM
Sales
.
Orders
;
/*
TASK 7:
Segment all Orders into 3 Categories: High, Medium, and Low Sales.
*/
SELECT
OrderID,
Sales,
Buckets,
CASE
WHEN Buckets
=
1
THEN
'
High
'
WHEN Buckets
=
2
THEN
'
Medium
'
WHEN Buckets
=
3
THEN
'
Low
'
END
AS
SalesSegmentations
FROM
(
SELECT
OrderID,
Sales,
NTILE(
3
) OVER (
ORDER BY
Sales
DESC
)
AS
Buckets
FROM
Sales
.
Orders
)
AS
SalesBuckets;
/*
TASK 8:
Divide Orders into Groups for Processing
*/
SELECT
NTILE(
5
) OVER (
ORDER BY
OrderID)
AS
Buckets,
*
FROM
Sales
.
Orders
;
/*
============================================================
SQL WINDOW RANKING | CUME_DIST
============================================================
*/
/*
TASK 9:
Find Products that Fall Within the Highest 40% of the Prices
*/
SELECT
Product,
Price,
DistRank,
CONCAT(DistRank
*
100
,
'
%
'
)
AS
DistRankPerc
FROM
(
SELECT
Product,
Price,
CUME_DIST() OVER (
ORDER BY
Price
DESC
)
AS
DistRank
FROM
Sales
.
Products
)
AS
PriceDistribution
WHERE
DistRank
<=
0
.
4
;
Back
|
FazBrowse Home
|
New Git URL