1 00:00.620 --> 00:05.540 So today we are going to start the different windows analytical functions. 2 00:05.750 --> 00:14.870 Laclede first value and last value as well as cumulative distinct percent rank and percentile count. 3 00:14.900 --> 00:24.140 So mostly on the day to day basis these four functions are very much in use rather than this last three. 4 00:24.350 --> 00:27.740 So let's start with the lag function first. 5 00:27.890 --> 00:37.130 So whenever we are defining the lag function the default value will be one if we do not specify anything. 6 00:37.250 --> 00:44.660 And if we specify uh like two then offset value of uh default will be two. 7 00:44.900 --> 00:48.530 If we specify three then offset value will be three. 8 00:48.560 --> 00:54.350 But if we do not specify anything then it will be one only. 9 00:55.070 --> 01:03.250 And default indicates what value to return if Default indicates what value to return if demanded. 10 01:03.250 --> 01:04.990 Row does not exist. 11 01:06.250 --> 01:15.100 So over here one default parameter is there and if we do not specify then it will be. 12 01:15.220 --> 01:17.470 It will be returning null in. 13 01:17.500 --> 01:25.540 Otherwise it will uh, return whatever the default value we will specify and if not pass will return 14 01:25.540 --> 01:26.980 null if demanded. 15 01:28.450 --> 01:30.940 If demanded, row does not exist, right? 16 01:31.240 --> 01:34.570 So now let's see directly with the example. 17 01:42.850 --> 01:45.520 So legend lead supports. 18 01:45.520 --> 01:47.980 So over here one more thing is there. 19 01:48.220 --> 01:54.820 Legend lead supports partition by clause as well as order by clause is mandatory, but does not support 20 01:54.820 --> 01:56.140 the framing clause. 21 01:56.230 --> 02:04.780 Right that we have seen like a current row, one row preceding and one row following, so that are called 22 02:04.780 --> 02:08.860 as a framing clause which is not supported over here. 23 02:09.340 --> 02:11.800 Now let us start with the example. 24 02:11.800 --> 02:14.350 So you will have the more idea. 25 02:15.670 --> 02:18.370 So we have this employee table with us right. 26 02:18.400 --> 02:20.020 Select star from employee. 27 02:21.910 --> 02:27.520 And now suppose if I want to see the salary one new column will be there. 28 02:27.580 --> 02:34.540 And if I want to see the salary like okay let me copy this data into the Excel file. 29 02:45.220 --> 02:45.580 Okay. 30 02:45.580 --> 02:48.490 Now in suppose in the new column I want to see. 31 02:48.520 --> 02:49.660 See like this. 32 02:55.330 --> 02:59.370 Firstly we are using the leg function like right leg of salary. 33 02:59.400 --> 03:05.730 So whenever we are writing the this syntax, firstly it should be a scalar expression. 34 03:05.970 --> 03:08.550 So leg of salary we have written. 35 03:08.550 --> 03:13.800 Then over over here we have directly used the order by clause. 36 03:16.560 --> 03:19.260 We have directly used the order by clause. 37 03:19.260 --> 03:25.680 So order by salary we have mentioned and leg leg of salary. 38 03:25.890 --> 03:28.560 So over here what it will return. 39 03:28.590 --> 03:30.030 It will return. 40 03:30.720 --> 03:32.400 What is the previous value. 41 03:32.430 --> 03:34.290 Value of this salary column. 42 03:34.290 --> 03:35.730 We have arranged this. 43 03:36.300 --> 03:40.980 Let me arrange this salary into the order by first. 44 03:43.620 --> 03:46.170 Order by salary column means J. 45 03:48.960 --> 03:50.760 Okay, now it is order, right? 46 03:57.350 --> 04:00.800 Now previous value of the salary column will be null, right? 47 04:00.830 --> 04:04.220 There is no previous value, so it will be over here. 48 04:04.220 --> 04:05.270 It will be null. 49 04:05.300 --> 04:07.730 Then over here it will be 800. 50 04:07.760 --> 04:12.230 So everything will be copied from here. 51 04:21.860 --> 04:24.260 Then 950 should be over here. 52 04:24.260 --> 04:29.000 So let me first execute the query and let's compare the result. 53 04:29.180 --> 04:30.770 So let's see. 54 04:36.230 --> 04:37.610 So exactly right. 55 04:37.640 --> 04:39.710 What what we have done over here. 56 04:39.710 --> 04:42.770 We have to copy this this all the row. 57 04:42.890 --> 04:45.500 And we have to copy one row below. 58 04:45.530 --> 04:48.650 And then we need to remove this last row. 59 04:48.680 --> 04:56.690 So whenever we are using the leg function it will give the previous, previous employee or previous, 60 04:56.690 --> 04:59.960 uh, salary based upon the ordering. 61 05:02.210 --> 05:13.010 So this will be the result set of like when we do not, uh, mention when we do not mention any offset 62 05:14.990 --> 05:21.110 when we do not mention any offset. 63 05:21.740 --> 05:24.200 So this will be very good analytical function. 64 05:24.200 --> 05:29.420 This function we will use many a times in your daily life. 65 05:29.450 --> 05:31.430 But now let us see one thing. 66 05:31.460 --> 05:37.070 And as I have seen over here, here we have not mentioned any offset value. 67 05:37.100 --> 05:37.670 Right. 68 05:37.700 --> 05:40.670 We have not mentioned any offset value. 69 05:40.700 --> 05:43.610 So by default it will be one only. 70 05:44.000 --> 05:46.130 By default it will be one only. 71 05:46.130 --> 05:49.250 But over here we have mentioned some offset value right. 72 05:50.180 --> 05:55.030 Offset of two An order by salary ascending. 73 05:55.210 --> 05:56.830 So over here, what we will. 74 05:56.860 --> 05:58.270 It will come over here. 75 05:58.270 --> 05:59.650 It will come, right. 76 06:01.090 --> 06:02.980 Let me remove this one. 77 06:09.820 --> 06:14.470 So over here object we have mentioned uh mentioned two. 78 06:14.500 --> 06:14.950 Right. 79 06:14.950 --> 06:20.710 So it will be null then null, then 800 will be come over here. 80 06:22.420 --> 06:32.830 Then this 950 will be come over here like this and all the rows subsequently. 81 06:32.830 --> 06:35.740 So again this 3000 will not come right. 82 06:36.790 --> 06:46.270 So if you see this one, this 3000 is actually, uh, is value of this 3000 because we have mentioned 83 06:46.300 --> 06:47.680 offset of two. 84 06:56.850 --> 06:58.710 This 800 will become here. 85 06:58.740 --> 07:01.020 Then this 950 will become here. 86 07:02.250 --> 07:03.120 Then 1100. 87 07:03.180 --> 07:04.830 100 will become here. 88 07:06.150 --> 07:09.630 Then two, eight, five zero will become. 89 07:13.860 --> 07:14.280 Okay. 90 07:14.280 --> 07:16.260 We have to ideally copy from this one. 91 07:16.260 --> 07:17.130 One minute. 92 07:19.380 --> 07:20.430 And over here. 93 07:20.430 --> 07:22.710 And then we have to remove these two. 94 07:23.700 --> 07:24.600 Now see. 95 07:24.630 --> 07:28.110 Now this 2952975. 96 07:29.190 --> 07:31.680 And this 2975 has two offset. 97 07:31.710 --> 07:32.310 Right. 98 07:32.550 --> 07:34.890 It has one and two offsets. 99 07:34.890 --> 07:40.950 So this is the result when we are mentioning offset in the offset value as two. 100 07:41.100 --> 07:43.260 So let's see this result set. 101 07:44.520 --> 07:47.310 And let me copy this result set over here. 102 07:49.160 --> 07:50.450 So you can compare. 103 07:50.450 --> 07:50.750 Right? 104 07:50.750 --> 07:55.250 It is exactly what we have mentioned in Excel file, right? 105 07:57.650 --> 07:59.150 Everything is correct. 106 08:00.950 --> 08:08.420 Now suppose in this particular case, in this particular case I want to mention in place of null. 107 08:08.480 --> 08:12.200 In place of null I want to mention like not available. 108 08:12.230 --> 08:15.830 So in that particular case what we have we can do. 109 08:15.950 --> 08:19.580 We have to do casting. 110 08:19.610 --> 08:22.700 Casting of salary as varchar right. 111 08:22.700 --> 08:24.350 Casting of salary as varchar. 112 08:24.380 --> 08:28.310 Because if we do not do the cast then what? 113 08:28.310 --> 08:30.050 It will come. 114 08:35.090 --> 08:41.450 If we do not do the cast, then it will come like it will give me some error, right? 115 08:44.090 --> 08:44.570 Okay. 116 08:44.600 --> 08:45.440 One minute. 117 09:00.830 --> 09:08.600 Obviously it will give some error which is regarding to salary is integer data type and n a is a worker 118 09:08.600 --> 09:09.110 data type. 119 09:09.110 --> 09:11.870 So that is why we have done the casting over here. 120 09:13.760 --> 09:20.300 Simply that you can understand if we both the data types are different. 121 09:20.300 --> 09:23.000 So that is why we have done the cast over here. 122 09:23.270 --> 09:25.040 Now let's see one more. 123 09:25.070 --> 09:27.680 Now see the example of lead function. 124 09:28.610 --> 09:30.290 So what lead will do. 125 09:30.320 --> 09:35.690 Lead will give us the next or next salary. 126 09:35.690 --> 09:40.130 So let me copy this data into some new sheet. 127 09:45.850 --> 09:50.200 So over here, over here in in terms of leg it will give me null. 128 09:50.230 --> 09:55.300 But over here when we use the lid then it will give me like nine 51st. 129 09:55.330 --> 10:03.310 It will give me the next salary in the first row, then it will give me like this and what it will give 130 10:03.310 --> 10:03.910 me over here. 131 10:03.910 --> 10:08.380 It will give me null in the last row, right? 132 10:09.160 --> 10:13.060 So over in the leg function it will give me a top side. 133 10:13.060 --> 10:14.200 It will give me null. 134 10:14.230 --> 10:19.810 But when we will use the lead function, it will give me null in the bottom side. 135 10:19.840 --> 10:21.760 So that is only the difference. 136 10:21.760 --> 10:23.680 So let's see the example. 137 10:23.980 --> 10:24.850 So let's see. 138 10:24.850 --> 10:25.870 This is what I have. 139 10:26.050 --> 10:28.750 I have explained you right in the Excel file. 140 10:30.250 --> 10:30.970 Same. 141 10:30.970 --> 10:33.850 It is giving me over here null now. 142 10:36.340 --> 10:37.570 Let's see one. 143 10:37.570 --> 10:39.640 Uh this example first. 144 10:39.640 --> 10:42.850 So if we do lead of salary comma two right. 145 10:42.850 --> 10:46.470 So it will give me last two record null value. 146 10:46.470 --> 10:48.480 And this salary. 147 10:48.480 --> 10:50.910 This salary will be come over here first. 148 10:50.940 --> 10:51.330 Why? 149 10:51.360 --> 10:54.450 Because we have done offset of two right. 150 10:54.480 --> 10:55.770 Offset of two. 151 10:56.340 --> 10:59.910 Then this salary will be there in the second row. 152 11:00.030 --> 11:03.360 Then this salary will be there in the third row like that. 153 11:03.390 --> 11:05.370 And over here it will give. 154 11:07.860 --> 11:08.760 3000. 155 11:08.790 --> 11:11.520 And over here it will give 5500. 156 11:11.550 --> 11:13.860 But over here why it is giving null. 157 11:13.890 --> 11:17.040 Because there is no next to next salary, is there. 158 11:17.040 --> 11:17.700 Right. 159 11:18.360 --> 11:22.260 There is no next to next salary, is there? 160 11:22.260 --> 11:23.880 After 5500? 161 11:23.910 --> 11:25.020 There is no salary. 162 11:25.020 --> 11:29.190 So that is why it is giving null over here and null over here as well. 163 11:30.570 --> 11:34.980 Now let's see this example what this example will do. 164 11:38.190 --> 11:40.170 Let's see that one. 165 11:42.750 --> 11:45.930 Actually, I want all the column. 166 11:59.310 --> 12:01.680 Let me copy the result set. 167 12:07.590 --> 12:10.440 So now understand what is written over here. 168 12:10.470 --> 12:14.190 These are the these are the framing clause. 169 12:14.190 --> 12:16.620 And we have not used either lead or lag. 170 12:16.620 --> 12:17.220 Right. 171 12:17.460 --> 12:20.100 We have not used either lead or lag. 172 12:20.520 --> 12:22.260 And this is a framing clause. 173 12:22.260 --> 12:26.970 So sum of salary over order by salary row order by salary. 174 12:27.000 --> 12:30.900 Rows between one following and one following. 175 12:30.900 --> 12:34.350 So what will happen over here in this particular case. 176 12:36.510 --> 12:39.360 So if we analyze the first row right. 177 12:39.360 --> 12:46.310 If we analyze the first row, then it will give one following and one following. 178 12:46.310 --> 12:49.490 So it will only give me this second row only, right? 179 12:49.520 --> 12:52.010 It will only give me this second row only. 180 12:52.040 --> 12:54.860 So over here it will give me 950. 181 12:54.890 --> 13:00.800 Now when we analyze 950 again one following and one following. 182 13:00.800 --> 13:02.510 So what it will give. 183 13:02.540 --> 13:05.780 It will give me 1100. 184 13:05.810 --> 13:08.300 Only one following and one following. 185 13:09.560 --> 13:11.900 So it will give me 1100 only. 186 13:11.900 --> 13:12.530 Right. 187 13:12.920 --> 13:19.460 Now again, again for this, if we analyze for this row, then one following. 188 13:19.490 --> 13:20.210 Following means. 189 13:20.210 --> 13:21.680 What is the next row? 190 13:22.010 --> 13:24.110 Next row is this one right. 191 13:25.880 --> 13:28.220 And one following means the same. 192 13:28.220 --> 13:30.170 Same row only not one following. 193 13:30.290 --> 13:35.870 It means not next row, one following and one following. 194 13:35.990 --> 13:40.090 Then same row it will consider and it will give me the result over here. 195 13:40.630 --> 13:41.740 So what is this? 196 13:41.770 --> 13:43.240 Actually, what is this? 197 13:43.930 --> 13:45.730 This is the example of. 198 13:48.070 --> 13:55.450 So if we run both of these, query will let me run with all the columns. 199 14:02.320 --> 14:08.350 So see over here carefully both of these column will give me the same same thing. 200 14:08.350 --> 14:08.770 Right. 201 14:08.800 --> 14:12.520 Both of these query will give me the same thing over here. 202 14:12.550 --> 14:16.930 959 5010 one one. 203 14:16.960 --> 14:19.600 If you see 1616 hundred right. 204 14:19.600 --> 14:25.270 Oh, row number seven and eight, it will give me 1600 1600. 205 14:25.930 --> 14:32.380 So, so this is another explanation, another way of doing the same thing. 206 14:32.410 --> 14:36.310 Another way of doing the same thing with sum of salary function. 207 14:37.180 --> 14:44.770 But why we have used between one following and one following because we want to consider only one row, 208 14:44.800 --> 14:48.370 only one following only not next or one. 209 14:48.370 --> 14:50.620 Not any current row or not preceding row. 210 14:50.620 --> 14:51.250 Right? 211 14:51.940 --> 14:56.530 So that is why in this particular case, it will give me the same result set. 212 15:01.990 --> 15:02.710 Right. 213 15:02.740 --> 15:05.020 So now let me move forward. 214 15:08.920 --> 15:11.470 Now let us see the first value. 215 15:15.310 --> 15:20.290 So they written an element from the first row of corresponding window frame. 216 15:21.370 --> 15:29.170 First value they written an element from the first row of corresponding window frame. 217 15:38.070 --> 15:41.010 So let me show you first a table name. 218 15:41.400 --> 15:45.540 I have created the table and inserted the data into the same. 219 15:45.960 --> 15:49.440 Now let me try to select star from. 220 15:54.090 --> 15:55.380 This table. 221 16:12.870 --> 16:13.710 Okay. 222 16:17.520 --> 16:17.910 Over. 223 16:17.940 --> 16:20.100 Order by code in one minute. 224 16:33.240 --> 16:33.540 Okay. 225 16:33.540 --> 16:35.030 Now consider this one only. 226 16:35.060 --> 16:36.320 Do not consider this. 227 16:36.320 --> 16:37.370 This table. 228 16:37.910 --> 16:40.040 I have to do order by also. 229 16:41.420 --> 16:42.170 Okay. 230 16:42.650 --> 16:48.470 So now what will what will first value and last value will do. 231 16:48.500 --> 16:51.440 Let's understand that one. 232 16:52.340 --> 16:57.290 They written an element from first row of corresponding window frame. 233 16:57.560 --> 17:04.370 First value of scalar expression over partition by order by frame clause. 234 17:04.520 --> 17:07.970 So over here you can mention the frame clause right. 235 17:08.000 --> 17:11.750 Whereas in lead and lag function you cannot mention the frame clause. 236 17:11.750 --> 17:13.640 But over here you can mention. 237 17:14.030 --> 17:15.380 So I have. 238 17:17.900 --> 17:24.860 I have copied this table in order by order by code. 239 17:25.280 --> 17:32.060 And now I want to do the first value of first value in the first value. 240 17:32.060 --> 17:37.120 Also you have to mention some scalar expression right in the first value. 241 17:37.120 --> 17:40.090 Also, you have to mention some scalar expression. 242 17:40.540 --> 17:53.170 So over here I have mentioned the code code and over order by code first value and in the last value 243 17:53.170 --> 17:56.710 also over order by code last value. 244 18:02.500 --> 18:03.430 Right. 245 18:03.520 --> 18:07.240 So now let's carefully see the process. 246 18:07.240 --> 18:08.860 What is happening over here. 247 18:09.220 --> 18:10.600 So I. 248 18:13.030 --> 18:17.140 I do not have mention any partition by clause over here. 249 18:17.320 --> 18:20.920 I do not have mention any partition by clause over here. 250 18:20.980 --> 18:22.840 So what will happen? 251 18:22.870 --> 18:27.340 It will consider this entire table as one partition. 252 18:27.820 --> 18:29.650 That is the first thing thing. 253 18:29.650 --> 18:30.280 Right? 254 18:30.520 --> 18:32.260 That is the first thing. 255 18:32.410 --> 18:40.810 Now, if I mention this entire table as one partition, then what is the first value of code from this 256 18:40.810 --> 18:42.880 entire partition? 257 18:42.880 --> 18:44.800 What is the first value of code? 258 18:45.310 --> 18:47.140 So it will be 15, right? 259 18:47.170 --> 18:48.730 It will be 15. 260 18:49.210 --> 18:52.000 So 15 will be. 261 18:54.700 --> 19:01.540 1515 will come to the entire row. 262 19:01.570 --> 19:02.890 Entire table. 263 19:03.040 --> 19:12.700 In the next column 15 will come to the all the rows of the particular table in ideally in. 264 19:12.700 --> 19:16.930 Ideally when I use the last value what will come? 265 19:17.380 --> 19:25.840 It should become this one right to the entire all the rows of this particular table. 266 19:25.870 --> 19:31.230 So this is the result set of First value. 267 19:31.590 --> 19:35.730 And this is the laser set of last value. 268 19:37.590 --> 19:40.260 Now let me execute the query. 269 19:49.920 --> 19:54.180 So now let's carefully observe the the result set. 270 19:54.210 --> 19:57.240 Let's carefully observe the result set. 271 20:00.960 --> 20:02.160 Let's see. 272 20:02.220 --> 20:04.830 So this this one is correct right. 273 20:04.860 --> 20:07.710 This 15 is absolutely correct. 274 20:10.950 --> 20:13.800 But now what is happening to the last value. 275 20:13.920 --> 20:18.540 Last value is coming every time this particular rows. 276 20:18.540 --> 20:23.520 Only last value is every time will come. 277 20:23.520 --> 20:25.800 This particular column values only. 278 20:25.920 --> 20:27.990 So what is the problem over here? 279 20:28.040 --> 20:30.530 Let's understand that one. 280 20:33.500 --> 20:36.620 So let me copy this. 281 20:37.550 --> 20:38.030 Okay. 282 20:38.060 --> 20:38.990 One minute. 283 20:43.040 --> 20:44.240 Now over. 284 20:44.270 --> 20:45.620 Order by code. 285 20:45.920 --> 20:51.290 So I already told this this one previously as well. 286 20:51.290 --> 20:52.010 Range. 287 20:52.040 --> 21:00.380 When you do not specify anything, any range function or rose function after this code, then by default. 288 21:00.410 --> 21:08.660 By default it will be range between range between unbound preceding, unbound preceding and current 289 21:08.660 --> 21:16.550 row in the first value, as well as in the last value range between unbound, preceding and current 290 21:16.550 --> 21:17.120 row. 291 21:17.150 --> 21:19.730 So now let us understand that one. 292 21:22.970 --> 21:26.750 This is my Column. 293 21:27.140 --> 21:28.700 Wow. 294 21:31.100 --> 21:32.420 This is my column. 295 21:32.480 --> 21:38.360 Now, what is the range between unbound, preceding and current row? 296 21:39.950 --> 21:42.140 This is the same range, right? 297 21:42.260 --> 21:44.990 For the first row, it will be the same range. 298 21:45.020 --> 21:48.620 And what is the first value in this particular range? 299 21:49.130 --> 21:50.240 15 only. 300 21:50.270 --> 21:50.570 Right. 301 21:50.600 --> 21:52.070 15 only. 302 21:52.100 --> 21:56.450 So that is why in the first value it will come 15. 303 21:57.200 --> 21:59.060 Now let's understand. 304 21:59.090 --> 22:06.770 Second what is the range between unbound preceding unbound preceding and current row. 305 22:06.800 --> 22:09.020 So this two record right. 306 22:10.430 --> 22:12.860 This time it will be this two record. 307 22:12.950 --> 22:16.460 And what will be the first value in between these two record. 308 22:16.820 --> 22:19.340 What will be the first value between these two record. 309 22:19.340 --> 22:21.140 It will be 15 only. 310 22:27.010 --> 22:27.850 It will be. 311 22:31.840 --> 22:33.430 It will be 15 only. 312 22:33.430 --> 22:34.030 Right? 313 22:44.200 --> 22:46.900 Now let's understand this one. 314 22:47.320 --> 22:50.290 So now this will become my current row. 315 22:50.290 --> 22:50.890 Right. 316 22:50.890 --> 22:51.730 And what? 317 22:52.270 --> 22:54.460 This will become my current row. 318 22:54.460 --> 22:55.660 And unbound. 319 22:55.690 --> 22:57.220 Preceding and current row. 320 22:57.250 --> 22:59.620 So what are the unbound preceding? 321 23:07.540 --> 23:10.240 What are my unbound preceding this time? 322 23:17.410 --> 23:19.540 Unbound preceding is all these three row. 323 23:19.570 --> 23:20.320 Right, right. 324 23:20.320 --> 23:23.850 All these three row and what and what is the current Ro? 325 23:23.850 --> 23:25.620 Current Ro will be 51. 326 23:25.890 --> 23:28.020 And what is the first value from this? 327 23:28.020 --> 23:30.360 This three rows. 328 23:30.360 --> 23:32.490 What is the first value from this? 329 23:34.830 --> 23:35.730 This three one. 330 23:35.760 --> 23:40.320 The first value will be 15 only. 331 23:40.320 --> 23:40.590 Right. 332 23:40.620 --> 23:42.870 First value will be 15 only. 333 23:43.710 --> 23:46.890 So that is why my first value is getting correct. 334 23:47.340 --> 23:50.550 That is why my first value is getting correct. 335 23:50.970 --> 23:53.550 Now let's see for the last value. 336 23:55.170 --> 23:57.750 Understand this one very carefully. 337 24:00.960 --> 24:03.450 Now let's start with the beginning. 338 24:06.600 --> 24:07.980 This is my current row. 339 24:08.010 --> 24:10.560 What are the unbound, preceding and current row? 340 24:10.590 --> 24:11.280 Same thing. 341 24:11.310 --> 24:11.940 Right? 342 24:12.150 --> 24:15.030 And what is the last row for this one? 343 24:15.030 --> 24:15.570 Same. 344 24:15.570 --> 24:18.390 Same thing will be my last row. 345 24:19.410 --> 24:21.660 Now let's understand this one. 346 24:21.960 --> 24:26.850 What is the unbound, preceding and current row? 347 24:26.880 --> 24:27.600 Same thing. 348 24:27.630 --> 24:28.230 Right? 349 24:28.830 --> 24:29.460 Unbound. 350 24:29.460 --> 24:30.240 Preceding or. 351 24:30.360 --> 24:32.700 Sorry, we need to look into this one. 352 24:35.190 --> 24:38.220 What is the unbound, preceding and current row? 353 24:39.540 --> 24:43.860 Unbound preceding is 15 and 29 is my current row. 354 24:43.890 --> 24:45.900 Now what is the last value? 355 24:46.290 --> 24:48.060 What is the last value? 356 24:48.120 --> 24:50.190 Last value will be 29. 357 24:50.190 --> 24:53.070 That is why 29 is coming over here. 358 24:54.690 --> 24:57.240 That is why 29 is coming over here. 359 24:57.420 --> 25:01.830 Now let's see what 51 while analyzing 51. 360 25:02.040 --> 25:05.130 What are the unbound, preceding and current row? 361 25:05.910 --> 25:08.400 Unbound preceding is this all? 362 25:08.400 --> 25:13.230 This above row will be unbound preceding and current row will be 59. 363 25:13.260 --> 25:18.210 Now what is the last value in this particular range? 364 25:20.780 --> 25:21.650 51. 365 25:21.650 --> 25:23.300 Only by last value, right? 366 25:23.450 --> 25:26.120 That is why 51 is coming over here. 367 25:26.180 --> 25:27.680 You got it right. 368 25:27.740 --> 25:29.690 Now let's see this one. 369 25:30.020 --> 25:30.650 Five. 370 25:30.680 --> 25:30.890 Three. 371 25:30.920 --> 25:31.070 Three. 372 25:31.100 --> 25:31.610 One. 373 25:31.640 --> 25:33.410 What are the unbound preceding? 374 25:33.410 --> 25:35.420 All the above rows will be my unbound. 375 25:35.450 --> 25:41.660 Preceding all the above rows will be my unbound preceding and current row will be five, three, three 376 25:41.690 --> 25:46.220 one current row will be five, three, three, one. 377 25:46.250 --> 25:49.640 And what are the last value from this all this range. 378 25:50.180 --> 25:51.710 5331 only. 379 25:51.710 --> 25:52.340 Right. 380 25:53.360 --> 25:54.200 That is why. 381 25:54.230 --> 25:56.330 That is why this is the issue. 382 25:57.200 --> 26:00.740 That is why in the last value is coming the issue. 383 26:01.250 --> 26:04.010 Then what is the solution for this one? 384 26:04.820 --> 26:15.380 I want 1212211 in all this all this record I want 12211 all. 385 26:17.840 --> 26:19.660 In all this record. 386 26:21.220 --> 26:23.320 So what is the solution? 387 26:23.350 --> 26:24.460 Let's see. 388 26:29.410 --> 26:32.020 Let's see the solution for this one. 389 26:32.020 --> 26:37.690 So for that particular purpose you have to mention like this. 390 26:40.930 --> 26:43.960 Rows between unbound preceding. 391 26:44.200 --> 26:47.140 Rows between unbound preceding and unbound. 392 26:47.170 --> 26:48.160 Following. 393 26:49.600 --> 26:53.020 Rows between unbound preceding and unbound following. 394 26:53.050 --> 26:55.180 Now let's execute this query. 395 26:55.180 --> 26:57.100 Then I will explain you. 396 27:09.970 --> 27:11.710 Now let's see the result. 397 27:11.800 --> 27:13.690 Now my result is perfect. 398 27:13.720 --> 27:17.230 Right now my result is Perfect. 399 27:17.380 --> 27:19.390 This is my current result. 400 27:20.260 --> 27:22.930 Now let's understand one two example. 401 27:23.350 --> 27:25.720 So what I have written over here. 402 27:27.490 --> 27:28.300 Over here. 403 27:28.330 --> 27:28.900 Unbound. 404 27:28.930 --> 27:30.400 Preceding and unbound. 405 27:30.400 --> 27:30.880 Following. 406 27:30.910 --> 27:31.510 Right. 407 27:31.930 --> 27:32.350 Unbound. 408 27:32.380 --> 27:33.490 Preceding and unbound. 409 27:33.490 --> 27:34.360 Following. 410 27:34.870 --> 27:35.980 So what are the unbound? 411 27:35.980 --> 27:36.460 Preceding? 412 27:36.490 --> 27:37.960 For this first row. 413 27:38.080 --> 27:39.100 Nothing. 414 27:39.100 --> 27:41.560 And what are the unbound following? 415 27:42.460 --> 27:47.470 All this thing will be my unbound following only all this thing. 416 27:47.470 --> 27:49.060 So that is why. 417 27:49.720 --> 27:52.180 And what is the last value in this range? 418 27:53.230 --> 27:55.990 12211 only. 419 27:55.990 --> 27:56.680 Right. 420 27:59.560 --> 28:06.730 Now let's say for 29, what are the unbound preceding all this above row will be unbound, bound, preceding. 421 28:06.910 --> 28:08.830 And what are the unbound following? 422 28:08.830 --> 28:11.530 All this row below will be unbound following. 423 28:11.710 --> 28:14.380 And what are the last row in this particular range? 424 28:14.410 --> 28:16.590 Again Same thing. 425 28:16.590 --> 28:18.750 12211. 426 28:18.780 --> 28:20.490 So it will come over here. 427 28:20.490 --> 28:21.990 12211. 428 28:23.340 --> 28:26.100 Same thing will be happening for all the rows. 429 28:26.340 --> 28:29.160 What are the unbound following for 101. 430 28:29.190 --> 28:33.840 All these will be unbound following and so on. 431 28:33.840 --> 28:36.000 All this thing will be unbound preceding. 432 28:36.210 --> 28:38.400 And all the below thing will be unbound. 433 28:38.400 --> 28:47.340 Following in 1 to 2 one, one will be come over here again, because last value of this particular range 434 28:47.340 --> 28:51.420 will be 1 to 2, one one only. 435 28:51.420 --> 28:55.830 So that is why again it will be coming 1 to 2 one one over here. 436 28:55.830 --> 28:59.070 And for all the below record as well. 437 28:59.100 --> 28:59.430 Right. 438 28:59.430 --> 29:01.200 So this is the solution. 439 29:02.850 --> 29:06.270 This is the very good interview question they will ask you. 440 29:06.300 --> 29:12.060 If you directly mention like this, only then your result will be wrong. 441 29:12.090 --> 29:14.510 Then your result will be wrong. 442 29:14.720 --> 29:17.420 You have to mention like this one only. 443 29:18.710 --> 29:21.500 You have to mention like this one only, right? 444 29:29.540 --> 29:36.260 So in the above example, first value is the same and equal to to the value in the first value for the 445 29:36.260 --> 29:41.900 entire result, set 15 will be coming right, while the last value changes for each record in equal 446 29:41.900 --> 29:43.880 to the last value that was pulled. 447 29:44.150 --> 29:47.990 Current value in the result set only so that we have seen already. 448 29:49.730 --> 29:55.100 Now, if we mention the partition by clause, then again the same thing will be happen. 449 29:55.130 --> 29:59.960 Let's see this quickly. 450 29:59.990 --> 30:05.960 So if we mention the partition by clause, partition by department or. 451 30:06.080 --> 30:06.650 Sorry. 452 30:09.830 --> 30:12.550 What is we have mentioned in the partition by. 453 30:21.940 --> 30:22.840 My department. 454 30:22.840 --> 30:25.930 I have written over here by one one. 455 30:27.790 --> 30:28.360 Okay. 456 30:28.390 --> 30:30.160 Actually, it is not a department. 457 30:30.160 --> 30:31.120 It is a. 458 30:33.310 --> 30:34.840 This second column only. 459 30:34.840 --> 30:35.980 This second column only. 460 30:36.010 --> 30:36.190 Okay. 461 30:36.220 --> 30:36.430 Okay. 462 30:36.460 --> 30:38.380 Let's let's see this example. 463 30:38.500 --> 30:43.900 Now it will consider this this one as a first first partition. 464 30:43.900 --> 30:46.360 This one will become the first partition. 465 30:46.510 --> 30:51.010 Then it will become this one will become the second partition. 466 30:54.340 --> 30:57.730 Then subsequently it will giving the. 467 30:57.880 --> 30:59.680 This is a department actually. 468 31:00.100 --> 31:06.100 So now in this first department it will analyze what will be the first value. 469 31:06.160 --> 31:09.280 So now again 51 will be the first value. 470 31:09.310 --> 31:11.230 But last value is coming wrong. 471 31:11.260 --> 31:11.830 Right. 472 31:12.100 --> 31:13.810 Last value is coming wrong. 473 31:13.840 --> 31:14.560 Why? 474 31:14.590 --> 31:17.560 Because we have not mentioned anything like this. 475 31:17.590 --> 31:26.590 So if we want to mention anything, then we have to mention this is what I have already told, right? 476 31:27.280 --> 31:32.710 By default it will be range between unbound, preceding and current row. 477 31:32.740 --> 31:36.850 So what we have to mention if we want the result like this. 478 31:37.330 --> 31:49.870 If we want a result like this 514, then 514 will be over here in all the values ten one, two, two, 479 31:49.900 --> 31:52.600 one one will be there in all the values. 480 31:55.690 --> 31:57.820 Actually, this will be the correct result, right. 481 31:57.850 --> 31:58.960 Partition by. 482 31:59.170 --> 32:07.570 So if we want this result set then we don't need to mention rows between unbound preceding and unbound 483 32:07.570 --> 32:08.230 following. 484 32:08.260 --> 32:09.010 Right. 485 32:09.630 --> 32:12.210 So this will work this time. 486 32:12.210 --> 32:13.920 This will work department wise only. 487 32:13.950 --> 32:17.460 See this is the correct result set right. 488 32:20.430 --> 32:21.720 This is the correct result. 489 32:21.720 --> 32:23.910 Set what I have seen over here. 490 32:27.450 --> 32:29.280 In the Excel file. 491 32:29.280 --> 32:29.880 Right. 492 32:33.180 --> 32:37.350 The working is the same which I have already explained you. 493 32:37.680 --> 32:41.940 If we mention partition by then also it will be fine. 494 32:42.180 --> 32:43.200 It will work. 495 32:43.350 --> 32:45.570 Depends on the different partitions. 496 32:51.570 --> 32:53.610 Now let's see one more example. 497 32:53.610 --> 32:54.300 Cumulative. 498 32:54.300 --> 32:55.200 Distinct. 499 32:56.610 --> 33:05.700 So it calculates the relative position of a specified value in a group of values by determining the 500 33:05.700 --> 33:10.100 percentage of values which are less than or equal to that particular value. 501 33:10.640 --> 33:12.230 So let's see. 502 33:13.610 --> 33:16.280 We have this particular table EMP table. 503 33:25.130 --> 33:27.170 Let me mention only star over here. 504 33:27.170 --> 33:28.280 And let's see. 505 33:35.540 --> 33:37.670 Let me copy this data. 506 33:41.840 --> 33:44.480 So let's see the query first. 507 33:44.510 --> 33:46.850 Cumulative list function we have used over here. 508 33:46.880 --> 33:48.680 Order by salary ascending. 509 33:49.160 --> 33:51.980 And we have given the alias. 510 33:51.980 --> 33:53.270 And then from EMP. 511 33:53.510 --> 33:55.760 So we have not done any partition by. 512 33:55.790 --> 33:56.300 Over here. 513 33:56.300 --> 33:56.960 Right. 514 33:59.660 --> 34:01.190 Now what we have to see. 515 34:01.220 --> 34:01.760 See. 516 34:01.790 --> 34:04.640 We have to see one divided by. 517 34:09.980 --> 34:12.470 How many total rows are there total 15 rows. 518 34:12.500 --> 34:13.400 Are there right? 519 34:14.420 --> 34:18.860 One divided by 15, one divided by. 520 34:24.080 --> 34:25.220 C over here. 521 34:25.250 --> 34:25.640 What? 522 34:25.670 --> 34:27.290 It will be happening. 523 34:28.430 --> 34:35.570 It will be happening like by determining the percentage of values which are less than or equal to that 524 34:35.570 --> 34:41.240 specific value, less than or equal to that specific value in the group. 525 34:41.240 --> 34:44.030 So over here we have not mentioned any partition by. 526 34:44.060 --> 34:48.650 So how many values are less than or equal to this particular. 527 34:52.070 --> 34:54.470 Particular this specific value. 528 34:54.920 --> 34:57.020 So only one one value is there. 529 34:57.020 --> 35:01.280 Right 800 is less than or equal to this. 530 35:01.280 --> 35:02.570 All the all the rows. 531 35:02.570 --> 35:03.230 Right. 532 35:03.260 --> 35:05.020 And how many total values are there? 533 35:05.020 --> 35:06.460 15 values are there. 534 35:06.490 --> 35:07.780 So what it will do? 535 35:07.780 --> 35:09.160 It will do like this. 536 35:09.310 --> 35:12.280 Equal to one divided by 15. 537 35:12.280 --> 35:14.650 So it will give me this result set. 538 35:14.800 --> 35:17.620 Now when we move to the second row. 539 35:17.650 --> 35:24.820 When we move to the second row, how many values are less than or equal to the specific value. 540 35:25.000 --> 35:26.590 So only two right. 541 35:26.620 --> 35:29.290 This one first and this one will be second. 542 35:29.320 --> 35:32.710 So what it will do two divided by 15. 543 35:33.190 --> 35:35.170 So 0.1333. 544 35:35.200 --> 35:35.770 Right. 545 35:35.920 --> 35:38.950 Now when it will give to the 1100. 546 35:38.980 --> 35:43.630 How many values are less than or equal to this particular value. 547 35:43.660 --> 35:46.390 So three divided by 15. 548 35:46.990 --> 35:49.420 So it will be 0.2 right. 549 35:49.840 --> 35:51.640 It will be 0.2. 550 35:51.670 --> 35:55.390 So this is what cumulative distinct will be there. 551 35:59.890 --> 36:06.300 Percentage of salary is less than or equal to the current salary within each department. 552 36:06.330 --> 36:10.380 So now this one will be work based upon the department wise. 553 36:10.830 --> 36:14.160 This one will be work based upon the department wise. 554 36:14.310 --> 36:18.780 So let's see quickly. 555 36:23.700 --> 36:28.950 So how many departments uh, first row is how many rows? 556 36:29.970 --> 36:30.960 Three rows are there. 557 36:30.960 --> 36:31.290 Right. 558 36:31.320 --> 36:32.760 Now this one. 559 36:32.760 --> 36:37.860 So one divided by 3.33333. 560 36:37.890 --> 36:38.430 Right. 561 36:38.460 --> 36:40.620 Now how many rows below. 562 36:40.770 --> 36:42.000 So one and two. 563 36:42.030 --> 36:43.680 So two divided by three. 564 36:43.710 --> 36:47.160 Equal to t divided by 3.6. 565 36:47.160 --> 36:50.790 And now over equal to three divided by three. 566 36:50.820 --> 36:52.530 So again one. 567 36:52.560 --> 36:54.030 Now it will start. 568 36:54.030 --> 36:57.690 When the department number is changing it will start again from scratch. 569 36:57.840 --> 37:01.050 So Four. 570 37:01.080 --> 37:01.530 Five. 571 37:01.560 --> 37:04.470 How many six departments are there? 572 37:04.470 --> 37:05.070 Right. 573 37:05.160 --> 37:08.130 So one divided by six. 574 37:08.730 --> 37:09.270 Correct. 575 37:09.270 --> 37:09.870 Right. 576 37:09.870 --> 37:13.080 So this is how this function will work. 577 37:13.470 --> 37:14.370 Cumulative. 578 37:14.370 --> 37:15.270 Distinct. 579 37:19.200 --> 37:23.100 Now let's quickly understand this one as well. 580 37:25.890 --> 37:27.150 Percent rank. 581 37:27.150 --> 37:31.050 So calculate the ranking of rho relative to the row set. 582 37:31.320 --> 37:38.280 The percentage is based on the number of rows in the group that have a lower value than current. 583 37:38.640 --> 37:39.840 Current row. 584 37:40.650 --> 37:42.270 So now how it will work. 585 37:42.270 --> 37:43.680 Let's see that one. 586 38:07.040 --> 38:07.430 Okay. 587 38:07.430 --> 38:09.380 Let me copy this result set. 588 38:16.310 --> 38:20.240 Now let's see what is happening over here. 589 38:21.530 --> 38:25.070 Calculate the ranking of a relative row to the row set. 590 38:25.100 --> 38:32.570 The percentage is based on the number of rows in the group that have a lower value than the current 591 38:32.570 --> 38:33.230 row. 592 38:33.500 --> 38:38.540 So now over here we have total 15 rows. 593 38:38.570 --> 38:41.030 Are there right now. 594 38:50.720 --> 38:54.320 Over here we have how many total. 595 38:59.020 --> 39:00.250 I am not sure. 596 39:00.280 --> 39:00.970 Okay. 597 39:01.060 --> 39:03.370 There are no no rows, are there? 598 39:03.370 --> 39:03.940 Right? 599 39:04.030 --> 39:10.900 There are no no rows are there which are below this particular particular salary right now. 600 39:10.900 --> 39:16.060 If we see come to the second second thing then there are two rows. 601 39:16.090 --> 39:17.440 Are there actually two rows. 602 39:17.470 --> 39:18.310 Are there. 603 39:20.920 --> 39:22.330 Two divided by 15. 604 39:22.360 --> 39:24.520 How it is working I am not sure. 605 39:34.420 --> 39:37.540 Equal to one divided by 15. 606 39:50.770 --> 39:57.210 Let's read this first again in the number of rows in the group minus one. 607 39:58.290 --> 40:00.630 Number of rows in the group minus one. 608 40:00.630 --> 40:00.990 Total. 609 40:00.990 --> 40:06.930 We have 15 rows are there so equal to one divided by. 610 40:09.960 --> 40:12.060 Sorry one divided by 14. 611 40:14.580 --> 40:14.940 Okay. 612 40:14.970 --> 40:15.540 Yeah. 613 40:16.830 --> 40:18.270 This one is the correct one. 614 40:18.270 --> 40:18.720 We have. 615 40:18.720 --> 40:19.920 How many rows below. 616 40:19.950 --> 40:21.450 Below to the current row. 617 40:21.480 --> 40:23.460 How many rows below to the current row. 618 40:23.460 --> 40:27.000 So only one row is below to the current row. 619 40:27.150 --> 40:30.930 And total number of rows are 15. 620 40:30.960 --> 40:32.490 So 15 minus one. 621 40:32.520 --> 40:35.400 Actually we have to do 15 minus one. 622 40:35.400 --> 40:36.300 We have to do. 623 40:36.420 --> 40:38.640 Now let's see for this one. 624 40:38.760 --> 40:43.260 So two rows are below this particular salary right. 625 40:43.410 --> 40:44.760 And how many. 626 40:44.790 --> 40:47.130 So two divided by 14. 627 40:47.730 --> 40:49.620 So it will be this one right. 628 40:49.620 --> 40:51.390 It is giving correct result. 629 40:51.420 --> 40:53.040 Now over here. 630 40:53.040 --> 40:55.950 How many rows below to this particular salary. 631 40:56.100 --> 41:03.090 One, two and three three rows are below to this particular salary, so equal to three divided by 14. 632 41:03.960 --> 41:06.780 So it is again giving me the correct result right. 633 41:08.340 --> 41:11.880 In the above one it was less than or equal to. 634 41:11.910 --> 41:18.000 Over here we need to we are checking only less than only right less than only. 635 41:18.000 --> 41:22.380 And we are also checking this thing. 636 41:22.380 --> 41:26.880 Also is the different number of row number of rows in a group and minus one. 637 41:26.880 --> 41:29.370 Also we have to do right. 638 41:33.000 --> 41:36.720 Now let's see this last one very quickly. 639 41:42.870 --> 41:45.240 Let me do 0.5 over here. 640 41:49.800 --> 41:54.050 So actually this percentile percentile will calculate them. 641 41:59.030 --> 42:01.970 Percentile will actually calculate the median. 642 42:01.970 --> 42:05.960 So it will calculate that how many rows are above. 643 42:05.990 --> 42:06.650 Above. 644 42:10.340 --> 42:12.410 Total 15 are there right. 645 42:12.470 --> 42:20.450 So ideally it will calculate how many rows are above me above me and how many rows are below me. 646 42:20.480 --> 42:22.340 How many rows are below me. 647 42:22.370 --> 42:31.910 So ideally it has seven rows are below and share seven rows are above and it will give that particular 648 42:31.940 --> 42:33.560 particular rows. 649 42:33.590 --> 42:41.210 It will give that particular rows to the newly generated column. 650 42:41.210 --> 42:47.450 So 1600 will be come over here every time while we are using the percentile. 651 42:48.740 --> 42:57.190 But actually it is depend totally depends on this particular value which we are giving over here. 652 42:59.050 --> 43:03.340 So percentage what is the median of salary column. 653 43:03.340 --> 43:04.300 What is the median. 654 43:04.300 --> 43:10.330 If 50 percentile then 50 percentile it will go and it will calculate the median. 655 43:10.360 --> 43:18.910 And show me show me the value in the salary below which there are 50% of the value in the salary column. 656 43:18.940 --> 43:23.080 Show me the value where 50% of value are below this value. 657 43:25.510 --> 43:30.280 So if we use the point seven right point seven. 658 43:30.820 --> 43:36.610 So ideally it it will not give me 2770. 659 43:36.610 --> 43:37.240 Right. 660 43:37.300 --> 43:45.040 So point seven you need to first calculate this percent rank which which we are calculating. 661 43:46.720 --> 43:48.040 And point seven. 662 43:48.040 --> 43:51.250 So it will be ideally between this or this. 663 43:52.150 --> 43:55.450 It is giving me the value between this or this. 664 43:58.360 --> 43:58.990 Because. 665 43:59.020 --> 44:01.510 0.7 is coming in this particular range. 666 44:01.510 --> 44:02.110 Right. 667 44:02.260 --> 44:05.710 So randomly it has taken 2770. 668 44:14.980 --> 44:17.320 One minute 15 values are there. 669 44:18.460 --> 44:19.090 Over here. 670 44:21.850 --> 44:22.420 Ten. 671 44:27.760 --> 44:34.120 So actually these are, uh, you need more practice, but these are very rarely, rarely used functions. 672 44:34.120 --> 44:38.440 This last three which I have shown you which are very rarely used. 673 44:39.130 --> 44:42.310 Now let me unmute you all for five minutes. 674 44:47.470 --> 44:49.810 Now you can ask me if you have any.