1
00:00.240 --> 00:02.240
Let's see another SQL problem.

2
00:03.240 --> 00:07.360
Find the top three highest paid employees per department.

3
00:08.080 --> 00:10.200
So below is the problem statement.

4
00:11.640 --> 00:18.520
Given a table of employees with their salaries and department information, write a query to find the

5
00:18.520 --> 00:22.440
top three highest paid employees in each department.

6
00:22.920 --> 00:26.320
So we are having this table in data.

7
00:26.880 --> 00:31.800
So let me copy this table in data.

8
00:31.960 --> 00:33.760
And let me ask the ChatGPT.

9
00:34.280 --> 00:39.160
So I am having below table.

10
00:41.640 --> 00:45.800
Structure and data with me.

11
00:47.080 --> 00:47.600
Okay.

12
00:49.520 --> 00:51.840
Please share your table structure and sample data.

13
00:52.480 --> 00:53.360
Okay fine.

14
00:54.920 --> 00:58.640
So I am sharing my same table structure and sample data.

15
00:59.800 --> 01:00.000
Okay.

16
01:00.000 --> 01:07.760
Now it has organized the table in the tabular format right data in the tabular format.

17
01:08.320 --> 01:09.320
Now let's see.

18
01:12.000 --> 01:13.240
What is my requirement.

19
01:13.240 --> 01:19.040
Write a query to find the top three highest paid employee in each department.

20
01:19.160 --> 01:25.120
So let me again ask the ChatGPT the same thing and let's see what it is giving.

21
01:25.160 --> 01:29.360
Okay, this is a classic ranking problem using window functions.

22
01:30.800 --> 01:34.040
So let's solve it step by step for your table.

23
01:36.600 --> 01:41.800
Okay, now find the top three highest paid employees in each department.

24
01:42.280 --> 01:44.360
They have provided this solution.

25
01:45.040 --> 01:45.640
Okay.

26
01:45.680 --> 01:48.120
This is the another alternative solution.

27
01:48.840 --> 01:52.600
So for older versions without window functions okay fine.

28
01:53.120 --> 01:55.960
So they have provided two different solutions for the same.

29
01:55.960 --> 01:56.360
Right.

30
01:57.880 --> 01:58.280
So.

31
02:02.160 --> 02:03.320
Solution.

32
02:03.320 --> 02:10.120
With GPT, we can say right now, see what it has done over here.

33
02:11.600 --> 02:17.880
It is writing the derived table over here and in the inner query we have used.

34
02:17.920 --> 02:19.640
It has used the dense rank function.

35
02:19.640 --> 02:20.120
Right.

36
02:20.560 --> 02:22.160
So what will dense rank do?

37
02:22.840 --> 02:31.360
Dense rank will generate the rank based upon the partition by and based upon the order by salary descending.

38
02:31.360 --> 02:32.560
It has done over here.

39
02:32.880 --> 02:38.880
So it means wherever the highest salary for any particular department, it will give rank one.

40
02:39.200 --> 02:43.560
For the second highest salary, it will give rank two for the third highest salary.

41
02:43.560 --> 02:45.840
It will give rank three like that.

42
02:45.840 --> 02:46.280
Right?

43
02:46.520 --> 02:51.240
And whenever the department is changed again, the rank will starting from one again.

44
02:51.280 --> 02:58.760
See for HR department again, rank will starting from one for sales department again rank will starting

45
02:58.760 --> 02:59.480
from one.

46
02:59.760 --> 03:03.090
And because of it is a similar salary over here.

47
03:03.410 --> 03:05.850
It has generated one one over here two times.

48
03:05.850 --> 03:06.250
Right.

49
03:06.890 --> 03:13.810
And now our requirement is we want top three highest salary for each department.

50
03:13.970 --> 03:21.690
So what we have to do, it is written this query in the inner query and outside the query.

51
03:21.730 --> 03:22.930
It has done the filter.

52
03:23.410 --> 03:29.530
Wherever the ark or where wherever the salary rank less than or equal to three.

53
03:29.570 --> 03:31.970
It has given me all that record, right.

54
03:32.810 --> 03:35.090
So it will eliminate this particular record.

55
03:35.090 --> 03:35.570
Right.

56
03:37.050 --> 03:37.930
And let's see.

57
03:38.290 --> 03:40.730
Let me execute this entire query now.

58
03:43.610 --> 03:45.210
So see one lakh 20,000.

59
03:45.250 --> 03:47.650
It is giving me two two times rest.

60
03:48.050 --> 03:50.050
So that is perfect one right.

61
03:50.490 --> 03:53.250
So it has given the data as per our requirement.

62
03:53.610 --> 03:55.610
And what I have done in the above query.

63
03:55.650 --> 03:56.130
Okay.

64
03:56.810 --> 04:03.770
In the above query I have used the same thing, but this time I have used with the common table expression.

65
04:05.010 --> 04:05.290
Right.

66
04:05.290 --> 04:12.530
So what I am doing in the common table expression, I am writing the first query in which I am calculating

67
04:12.530 --> 04:17.530
the rank for each department using the density function and then outside the.

68
04:20.330 --> 04:25.250
Common table expression I am writing the query.

69
04:25.330 --> 04:29.290
I am selecting the data from the city and doing the same thing.

70
04:29.330 --> 04:33.730
Doing the filter based upon the k less than or equal to three, right?

71
04:34.290 --> 04:35.930
So almost this.

72
04:36.090 --> 04:39.890
Both of the two query will give me the same result, right?

73
04:39.930 --> 04:42.530
Both of these query will give me the same result.

74
04:43.050 --> 04:46.530
Only the column sequence is different.

75
04:46.530 --> 04:52.290
Right now ChatGPT has provided us the another solution.

76
04:53.050 --> 04:54.810
And let's see.

77
04:54.850 --> 04:58.970
So see for older SQL versions without window functions.

78
04:59.290 --> 05:03.930
So window functions Just previously was not available in SQL.

79
05:03.970 --> 05:04.410
Right.

80
05:04.650 --> 05:07.690
So this solution is based upon that one.

81
05:09.130 --> 05:10.850
So let me execute okay.

82
05:11.250 --> 05:13.850
This is also giving me the same same thing right.

83
05:13.890 --> 05:15.170
Same output.

84
05:16.850 --> 05:21.010
So both of the solution both of the query is correct.

85
05:21.010 --> 05:24.650
Both of the queries given me the same output.

86
05:25.050 --> 05:33.330
But as per the requirement you can use which query is suitable for you right in your project.

87
05:34.010 --> 05:37.690
So nowadays this window functions are in high demand.

88
05:37.970 --> 05:45.170
So that using a window function, if you are aware about the window function then using a window function

89
05:45.170 --> 05:48.970
is more easy and approachable way.

90
05:49.010 --> 05:50.330
We can see right.

91
05:51.730 --> 05:54.450
Let me check what else it is saying.

92
05:54.970 --> 05:56.130
This works everywhere.

93
05:56.170 --> 06:02.650
It counts how many salaries are higher in each department Apartment and keeps only the top three.

94
06:02.850 --> 06:04.090
Okay, perfect.

95
06:08.810 --> 06:13.130
Would you like me to modify it to show ranks along with the salaries?

96
06:14.450 --> 06:16.410
Okay, let me say yes.

97
06:19.010 --> 06:21.010
Like rank one, rank two, rank three.

98
06:21.050 --> 06:21.730
Like that.

99
06:21.730 --> 06:22.250
Right.

100
06:22.890 --> 06:27.130
So let me see what it is.

101
06:27.130 --> 06:27.890
Retrieving.

102
06:29.370 --> 06:29.930
Okay.

103
06:29.970 --> 06:31.530
Data will be the same thing.

104
06:35.370 --> 06:35.570
Okay.

105
06:35.610 --> 06:37.010
This is the correct query.

106
06:37.810 --> 06:38.370
Okay.

107
06:38.730 --> 06:41.210
So now it will give the rank as well right.

108
06:41.650 --> 06:45.610
So let me copy paste this query over here and let's see the result.

109
06:49.010 --> 06:49.810
Perfect.

110
06:50.130 --> 06:52.570
It is showing rank over here as well right.

111
06:53.770 --> 06:55.810
For all the top three salaries.

112
06:57.810 --> 06:58.370
Perfect.

113
06:58.410 --> 07:00.490
Now whether it is asking anything else.

114
07:02.980 --> 07:07.140
Would you like me to show a version that also includes average salary per department?

115
07:07.180 --> 07:09.020
Next to each record.

116
07:09.340 --> 07:10.100
So no.

117
07:10.780 --> 07:12.700
As of now, we don't require that thing.

118
07:12.700 --> 07:13.180
Right.

119
07:13.660 --> 07:19.780
So over here it has explained also everything like days rank assigns a rank within each department.

120
07:19.940 --> 07:21.420
Partition by department.

121
07:21.420 --> 07:22.140
Perfect.

122
07:22.260 --> 07:27.060
So order by salary descending ensures the highest salary will have rank one.

123
07:27.620 --> 07:29.380
The second highest will have rank two.

124
07:29.420 --> 07:29.940
Like that.

125
07:29.940 --> 07:30.340
Right?

126
07:30.580 --> 07:37.460
And when we then filter to keep only ranks less than or equal to three in order by department, salary

127
07:37.460 --> 07:42.820
rank neatly lists results by department and rank.

128
07:42.980 --> 07:43.740
Perfect.

129
07:44.820 --> 07:51.100
So this is how the ChatGPT is smart and very useful.

130
07:52.780 --> 07:55.500
Whenever it is required, we can use it, right?

131
07:56.060 --> 08:00.060
So we will see the next question into the next lecture.

132
08:00.060 --> 08:00.700
Thank you.