SQL-Practice-Queries/08_Window_Functions.sql at main · Pranjalitech26/SQL-Practice-Queries · GitHub

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
-- ===========================================
-- Window Functions
-- ===========================================


-- ===========================================
-- ROW_NUMBER()
-- ===========================================

-- Query 1 - Assign Row Number to Employees by Salary

SELECT
employee_id,
first_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS Row_Number
FROM employees;


-- ===========================================
-- RANK()
-- ===========================================

-- Query 2 - Rank Employees by Salary

SELECT
employee_id,
first_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS Salary_Rank
FROM employees;


-- ===========================================
-- DENSE_RANK()
-- ===========================================


-- Query 3 - Dense Rank Employees by Salary

SELECT
employee_id,
first_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS Dense_Rank
FROM employees;


-- ===========================================
-- LAG()
-- ===========================================

-- Query 4 - Display Previous Employee Salary

SELECT
employee_id,
first_name,
salary,
LAG(salary) OVER (ORDER BY salary) AS Previous_Salary
FROM employees;


-- ===========================================
-- LEAD()
-- ===========================================

-- Query 5 - Display Next Employee Salary

SELECT
employee_id,
first_name,
salary,
LEAD(salary) OVER (ORDER BY salary) AS Next_Salary
FROM employees;


-- ===========================================
-- RUNNING TOTAL
-- ===========================================

-- Query 6 - Calculate Running Total of Sales

SELECT
sale_id,
total_amount,
SUM(total_amount)
OVER (ORDER BY sale_id) AS Running_Total
FROM sales;


-- ===========================================
-- PARTITION BY
-- ===========================================

-- Query 7 - Average Salary by Department

SELECT
employee_id,
first_name,
department_id,
salary,
AVG(salary)
OVER (PARTITION BY department_id) AS Department_Average_Salary
FROM employees;