sql-practice-scripts/sql-practice-file-3.sql at main · MSreekari/sql-practice-scripts · 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
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
-- LEAD()/LAG() window functions
-- 1. Compare salary with previous salary in each department
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by department_name order by salary
    ) AS prev_salary
    from employees AS e
) AS emp;

-- 2. Find out salary differences fom previous employee
SELECT *
from (
	SELECT e.*,
    salary - LAG(salary) OVER(
		partition by department_name order by salary
    ) AS diff_salary
    from employees AS e
) AS e;

-- 3. Salary increase detection
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary,
    salary - LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS diff_salary
    from employees AS e
) AS emp;

-- 4. Identify employees whose salary increased in the next records
SELECT *
from (
	SELECT e.*,
    LEAD(salary) OVER(
		partition by employee_id order by date_joined
    ) AS next_salary
    from employees AS e
) AS emp
WHERE next_salary > salary;

-- 5. Identify employees where salary is greater than previous salary and salary is less than next salary
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary,
    LEAD(salary) OVER(
		partition by employee_id order by date_joined
    ) AS next_salary
    from employees
    AS e
) emp
WHERE prev_salary < salary
AND salary < next_salary;

-- 6. Identify employees whose salary decreased from previous salary and then increases in the next salary
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary,
    LEAD(salary) OVER(
		partition by employee_id order by date_joined
    ) AS next_salary
    from employees AS e
) AS emp
WHERE salary < prev_salary
AND salary < next_salary;

-- 7. Identify employees where salary is greater than the previous salary and next salary is lesser than the current salary
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary,
    LEAD(salary) OVER(
		partition by employee_id order by date_joined
    ) AS next_salary
    from employees AS e
) AS emp
WHERE salary > prev_salary
AND next_salary < salary;

-- 8. Identify employees whose salary stayed stagnant
SELECT *
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary,
    LEAD(salary) OVER(
		partition by employee_id order by date_joined
    ) AS next_salary
    from employees AS e
) AS emp
WHERE salary = prev_salary
AND next_salary = salary;

-- 9. Find employees whose salary never decreased over time
SELECT employee_id
from (
SELECT employee_id,
SUM(decrease_flag) AS decreased_flag
from (
SELECT *,
	CASE
		WHEN salary IS NOT NULL AND salary < prev_salary
        THEN 1
        ELSE 0
	END AS decrease_flag
from (
	SELECT e.*,
    LAG(salary) OVER(
		partition by employee_id order by date_joined
    ) AS prev_salary
    from employees AS e
) AS emp
) AS flagged_emp
GROUP BY employee_id
) AS summary
WHERE total_decreases = 0;