1 00:00.080 --> 00:01.360 Starting the recording. 2 00:07.200 --> 00:14.640 So today we will see few queries which has been asked in different, uh, companies. 3 00:15.480 --> 00:18.480 So let me directly start with the first query. 4 00:21.120 --> 00:27.480 Find out the detail of the employees whose salary is more than their manager salary. 5 00:29.040 --> 00:38.240 So I have already created the table employee table and I have already inserted the data few sample records 6 00:38.240 --> 00:39.000 into it. 7 00:39.760 --> 00:43.080 So let me select the data from the employees table. 8 00:44.320 --> 00:44.720 So. 9 00:48.760 --> 00:55.120 So uh, there is a ID column name, column salary and manager ID column is there. 10 00:59.610 --> 01:07.250 So whenever we want to find there is only one table with us, right? 11 01:07.930 --> 01:10.330 And here we need to do the self join. 12 01:10.330 --> 01:20.810 Or we can say that inner join between the same table because we want to compare employee and their manager's 13 01:20.810 --> 01:21.330 salary. 14 01:21.330 --> 01:21.770 Right. 15 01:22.970 --> 01:31.810 And then we want to retrieve the those employee whose salary is greater than their manager's salary. 16 01:32.690 --> 01:34.290 So here what I have done. 17 01:41.810 --> 01:43.130 Here what I have done. 18 01:45.210 --> 01:49.450 Select we can say the star from employee table. 19 01:49.450 --> 01:53.250 Then again I have joined the inner join with employees. 20 01:54.130 --> 01:57.730 Now we have given the different alias E one. 21 01:58.740 --> 01:59.900 Now what we require. 22 02:03.060 --> 02:05.220 We need to compare the manager salary. 23 02:05.220 --> 02:05.620 Right. 24 02:06.380 --> 02:11.420 So e dot manager ID equal to e1.id. 25 02:11.460 --> 02:13.820 Now let me just run this block only. 26 02:14.540 --> 02:18.340 So let me copy this data into the Excel file. 27 02:19.940 --> 02:22.140 So and. 28 02:29.780 --> 02:32.940 So let me copy this data into the Excel file. 29 02:35.580 --> 02:38.420 I have muted you everyone for some time. 30 02:40.860 --> 02:45.220 You can ah ping over here whenever you have any doubt. 31 02:52.340 --> 02:55.740 So if you see over here now I have copied this data. 32 02:56.180 --> 02:59.230 So these are the up to here. 33 02:59.430 --> 03:01.510 This is employees detailed, right? 34 03:03.350 --> 03:04.710 This is employees detail. 35 03:04.710 --> 03:05.430 And this is. 36 03:07.710 --> 03:09.110 Their managers detail. 37 03:12.430 --> 03:13.950 This is their managers detail. 38 03:14.550 --> 03:14.990 Now. 39 03:17.230 --> 03:21.070 If you see over here I have taken first E1 dot start. 40 03:21.990 --> 03:25.550 So e1 means this sorry e dot star. 41 03:26.110 --> 03:27.110 So employee. 42 03:30.070 --> 03:33.430 Here there will be total four columns are there. 43 03:33.430 --> 03:33.750 Right. 44 03:33.750 --> 03:42.710 So first four column is coming from the employee table means employee E table. 45 03:47.030 --> 03:49.590 And then these four columns will be coming from. 46 03:51.870 --> 03:53.070 Employee E1. 47 03:57.560 --> 04:03.160 Now, if you see over here, this is the employees detailed, right. 48 04:03.160 --> 04:04.320 124. 49 04:04.760 --> 04:07.800 Now what is the manager ID of one. 50 04:08.240 --> 04:11.480 Manager ID of one is three. 51 04:11.520 --> 04:11.960 Right. 52 04:12.560 --> 04:19.880 Then in the second table this will become the ID right. 53 04:20.200 --> 04:25.640 Because we are comparing E dot manager id with even.id. 54 04:26.320 --> 04:29.360 So now in the second time it will become the ID. 55 04:29.360 --> 04:35.280 So now what is the all the details of ID three. 56 04:35.840 --> 04:38.000 So Suresh salary is 6000. 57 04:38.480 --> 04:42.000 Now what we are comparing we are comparing e dot salary. 58 04:46.040 --> 04:49.920 Should be greater than e one dot salary right? 59 04:51.880 --> 04:53.560 So now what is the E dot salary. 60 04:53.560 --> 04:55.210 Over here it is 5000. 61 04:57.330 --> 04:59.650 And even dot salary is 6000. 62 05:00.890 --> 05:04.170 So it is not greater than even salary. 63 05:04.170 --> 05:04.450 Right. 64 05:04.450 --> 05:14.010 So this will not retrieve this data will not retrieve right now again go to the second row. 65 05:14.930 --> 05:17.930 Now what is the manager ID of employee ID two. 66 05:19.570 --> 05:21.290 So manager ID is four. 67 05:21.330 --> 05:29.010 Now we want manager ID four as employee ID in detail of all the. 68 05:31.250 --> 05:34.850 Like all the details of manager ID four. 69 05:34.850 --> 05:37.490 So for that it is giving over here. 70 05:38.010 --> 05:41.010 Now it is 8009 thousand. 71 05:41.050 --> 05:44.930 So let me 8000 9000. 72 05:45.450 --> 05:48.290 So 8000 is not greater than 9000 right. 73 05:48.770 --> 05:50.810 So again this will not retrieve. 74 05:54.010 --> 05:57.970 Now let's come to employee ID manager. 75 05:58.010 --> 05:59.810 Now, what is the money manager? 76 06:00.170 --> 06:04.090 Manager is three means it is a source, right? 77 06:06.890 --> 06:10.010 So these three salary ID is 6000. 78 06:10.050 --> 06:12.130 Now this 9000. 79 06:16.090 --> 06:19.610 Now now this 9000 is greater than 6000. 80 06:19.650 --> 06:19.850 Right. 81 06:19.850 --> 06:20.730 So this will. 82 06:23.050 --> 06:23.810 Retrieve. 83 06:24.170 --> 06:26.850 So let me just run this query. 84 06:32.890 --> 06:34.450 So if you see over here. 85 06:39.410 --> 06:40.490 This is money s right. 86 06:40.490 --> 06:43.290 Money manager is three three means storage. 87 06:43.690 --> 06:45.650 So storage salary is 6000. 88 06:45.810 --> 06:47.610 Money salary is 9000. 89 06:48.090 --> 06:52.980 So this employee salary is greater than their manager salary. 90 06:57.020 --> 06:57.580 Right. 91 07:00.820 --> 07:03.420 This is the entire data with us. 92 07:08.580 --> 07:11.940 So now every up to this is clear, right? 93 07:17.300 --> 07:17.860 Yes, yes. 94 07:17.860 --> 07:18.460 It's clear. 95 07:22.180 --> 07:23.020 Okay, okay. 96 07:28.900 --> 07:31.060 So this is the first question. 97 07:33.100 --> 07:35.380 Now let's move to the second. 98 07:36.020 --> 07:44.060 And very important, frequently this question is asked how to identify the duplicate data and remove 99 07:44.060 --> 07:46.500 it using two different methods. 100 07:46.900 --> 07:49.380 Actually I have resolved with two different methods. 101 07:51.070 --> 07:52.710 So let me. 102 07:53.590 --> 07:57.470 I have already created this table cells dot context table. 103 07:58.030 --> 08:02.110 So in that context ID first name last name and email column is there. 104 08:02.710 --> 08:06.630 Whereas contact id is primary key and identity column. 105 08:08.270 --> 08:11.910 So no need to insert the data into that particular column. 106 08:12.670 --> 08:18.830 So now let me select the data from this contact table. 107 08:28.950 --> 08:31.550 Let me select the data from this table. 108 08:39.670 --> 08:41.510 So now if you see over here. 109 08:44.150 --> 08:47.550 The contact ID is different for all the contacts right. 110 08:47.910 --> 08:56.760 But if you see now in the first name, last name and email ID column, then this scheme scheme is coming 111 08:56.800 --> 08:57.640 three times. 112 08:58.360 --> 09:01.360 So this one is duplicated data right now. 113 09:01.960 --> 09:06.880 This is ASM is coming two times and this one is also coming two times. 114 09:08.640 --> 09:10.640 So these are the duplicated data. 115 09:10.920 --> 09:16.320 We just want to identify the duplicate data in remove the duplicate data. 116 09:16.320 --> 09:22.000 We just need to keep one record for each of these three combinations. 117 09:25.240 --> 09:27.960 So what I have done. 118 09:30.840 --> 09:39.600 I have just copied this data into some temporary table so that if I remove, I can perform again the 119 09:39.600 --> 09:42.480 same thing with different method. 120 09:42.560 --> 09:44.200 So this is the same data. 121 09:44.960 --> 09:45.480 Now. 122 09:48.770 --> 09:50.250 If you see over here. 123 09:57.610 --> 09:59.810 Let me right over here. 124 09:59.850 --> 10:04.210 Select star from same table name. 125 10:07.490 --> 10:08.410 Then group by. 126 10:16.690 --> 10:17.570 First name. 127 10:21.010 --> 10:22.970 Last name in email. 128 10:37.610 --> 10:38.610 Count of one. 129 10:40.930 --> 10:41.930 Greater than one. 130 10:43.170 --> 10:44.930 So these are all these? 131 10:44.930 --> 10:45.570 All three. 132 10:45.650 --> 10:46.970 Three are duplicated. 133 10:46.970 --> 10:47.100 it. 134 10:47.460 --> 10:51.340 And if you see now the count the number of times this. 135 10:54.780 --> 10:55.940 Data is coming. 136 10:56.940 --> 11:00.780 Then if you see this scheme is coming three times this one is coming two times. 137 11:01.220 --> 11:02.900 This one is coming two times. 138 11:08.220 --> 11:15.460 Now, if now, if you carefully observe this table, the contact ID is different, right? 139 11:16.020 --> 11:17.580 So now what we need to do. 140 11:23.820 --> 11:28.500 We need to find the minimum of contact ID among this. 141 11:28.500 --> 11:29.660 All of the groups. 142 11:31.820 --> 11:32.140 Right. 143 11:32.180 --> 11:35.940 Minimum of contact ID among this all of these groups. 144 11:36.260 --> 11:41.900 So let me just first run that particular block only. 145 11:45.380 --> 11:45.700 Uh. 146 11:45.830 --> 11:46.710 One minute. 147 11:52.870 --> 11:53.670 One minute. 148 11:57.990 --> 12:06.910 So now I have firstly taken the minimum of contact ID, so the one is there, right. 149 12:06.910 --> 12:08.270 So one has no issue. 150 12:08.310 --> 12:09.630 It is a unique record. 151 12:10.070 --> 12:12.310 Now two is also unique record. 152 12:12.670 --> 12:19.870 Now in this one minimum of there is three right three. 153 12:21.350 --> 12:23.790 Then over here uh six will come. 154 12:27.150 --> 12:29.470 Then eight will come and ten will come. 155 12:32.030 --> 12:37.150 So this all this minimum are our required data. 156 12:37.190 --> 12:38.350 All these minimum. 157 12:38.870 --> 12:45.080 If you see now how many records from this table, we need to remove one, two, three and four. 158 12:45.960 --> 12:51.520 So out of this ten record, this six record will be our requirement. 159 12:51.520 --> 12:55.720 And apart from this six contact ID we need to remove. 160 12:58.120 --> 12:58.600 Right. 161 12:58.640 --> 13:02.240 Apart from this six contact ID we need to remove. 162 13:02.840 --> 13:10.840 So what I have done over here select all this records all the ten record. 163 13:11.520 --> 13:14.280 Now where contact ID not in. 164 13:15.440 --> 13:16.240 Not in. 165 13:19.440 --> 13:21.240 Not in this same query. 166 13:21.280 --> 13:25.520 Minimum of contact ID based upon this three combination. 167 13:29.000 --> 13:33.160 So apart from this combinations we need to remove. 168 13:33.160 --> 13:35.920 So here we will get four five. 169 13:38.280 --> 13:39.440 Seven and nine. 170 13:40.280 --> 13:43.280 So if you see again this tables. 171 13:43.280 --> 13:43.370 Pulse. 172 13:46.010 --> 13:46.570 Four. 173 13:46.610 --> 13:46.890 Five. 174 13:46.930 --> 13:47.290 Right. 175 13:47.330 --> 13:47.970 This one. 176 13:49.170 --> 13:49.690 This one. 177 13:49.690 --> 13:49.970 Four. 178 13:49.970 --> 13:50.330 Five. 179 13:50.810 --> 13:51.610 Then this one. 180 13:51.610 --> 13:52.130 Seven. 181 13:52.770 --> 13:53.050 Because. 182 13:53.050 --> 13:53.490 Minimum. 183 13:53.530 --> 13:54.610 Is this one right? 184 13:54.810 --> 13:57.010 And this one nine. 185 14:01.290 --> 14:03.090 So 457 and nine. 186 14:03.090 --> 14:04.010 We need to keep. 187 14:08.290 --> 14:08.610 Sorry. 188 14:08.610 --> 14:10.810 We need to, uh, remove that record. 189 14:10.810 --> 14:11.210 Right. 190 14:11.730 --> 14:14.090 So let me again select the data now. 191 14:14.650 --> 14:17.290 Now only one thing we have to do delete. 192 14:17.530 --> 14:23.130 Instead of select we need to write delete condition delete statement. 193 14:24.250 --> 14:27.850 Now if I delete this data four records is gone. 194 14:27.850 --> 14:28.290 Right. 195 14:32.050 --> 14:33.330 Four records is gone. 196 14:33.730 --> 14:40.250 So now if I select now from this table it is giving us unique data only. 197 14:40.290 --> 14:40.690 Kim. 198 14:40.690 --> 14:43.500 Also Kim, is this minimum ID right. 199 14:43.500 --> 14:44.900 So it will keep this one. 200 14:45.460 --> 14:48.660 This one is this minimum ID and this one is this minimum ID. 201 14:49.100 --> 14:51.700 And rest of the them are single single record. 202 14:51.740 --> 14:53.900 So no minimum record right. 203 14:53.900 --> 14:55.740 So this is our unique data now. 204 14:57.260 --> 15:00.580 So this method is clear guys group by. 205 15:04.380 --> 15:06.940 I have a question over here okay. 206 15:08.420 --> 15:09.220 Can I go ahead. 207 15:10.940 --> 15:15.060 Uh just like you mentioned, can you please go through the question once, like, uh, how to remove 208 15:15.060 --> 15:17.580 the duplicate question instead of this? 209 15:17.580 --> 15:21.740 Can we use distinct function over here, like to find out how many are unique ones? 210 15:21.740 --> 15:22.580 Something like that. 211 15:25.260 --> 15:27.620 Like select distinct to a particular column. 212 15:28.020 --> 15:29.700 Distinct complete table. 213 15:30.860 --> 15:31.260 Uh, yes. 214 15:31.260 --> 15:31.460 Yes. 215 15:31.460 --> 15:36.340 But then I think we need to insert first into some temporary table, I think. 216 15:36.340 --> 15:36.740 Right. 217 15:37.620 --> 15:39.460 Uh, select distinct. 218 15:40.620 --> 15:42.180 Let me try again. 219 15:42.180 --> 15:43.020 One minute. 220 15:43.540 --> 15:43.940 Okay. 221 15:46.100 --> 15:47.780 Contract, I think. 222 15:48.380 --> 15:48.740 Right. 223 15:52.980 --> 15:53.300 Okay. 224 15:59.580 --> 16:00.540 One minute. 225 16:13.060 --> 16:13.460 Okay. 226 16:13.460 --> 16:14.220 These are the data. 227 16:14.220 --> 16:18.340 Right now you are telling that these three distinct record, right? 228 16:22.300 --> 16:22.780 Yes, yes. 229 16:22.820 --> 16:26.140 For for example, if we go by first name and last last name. 230 16:26.140 --> 16:27.860 So in the row number three. 231 16:27.900 --> 16:29.500 Kim, can you please zoom in a bit? 232 16:31.660 --> 16:32.300 Actually. 233 16:34.580 --> 16:34.980 Yeah. 234 16:35.020 --> 16:36.020 Zoom, zoom. 235 16:38.340 --> 16:38.740 Okay. 236 16:38.780 --> 16:38.940 No. 237 16:38.940 --> 16:39.540 Sure, sure. 238 16:39.580 --> 16:42.630 So for example like row number three, four, five are same, right. 239 16:42.670 --> 16:46.270 Kim then uh six and seven, then eight and nine. 240 16:46.270 --> 16:46.750 Right. 241 16:46.990 --> 16:51.350 So if we can put distinct function with first name and last name. 242 16:52.830 --> 16:53.150 Right. 243 16:53.470 --> 16:53.870 Okay. 244 16:53.870 --> 16:54.870 Let me try. 245 16:55.230 --> 16:58.110 Uh, distinct first name. 246 17:01.590 --> 17:03.590 Last name and email. 247 17:03.590 --> 17:04.030 Right. 248 17:04.710 --> 17:07.990 Now, this data are coming, right? 249 17:10.990 --> 17:11.390 Oh, yes. 250 17:11.390 --> 17:12.310 Yes, this one only. 251 17:12.350 --> 17:15.430 But but now I want ID column as well. 252 17:15.910 --> 17:18.150 Then how I can take ID column. 253 17:18.150 --> 17:18.750 And now. 254 17:20.550 --> 17:20.910 Right. 255 17:21.150 --> 17:21.550 Uh, yes. 256 17:21.550 --> 17:22.830 And also one more thing. 257 17:23.510 --> 17:27.350 That two record we want to remove completely from the table. 258 17:28.790 --> 17:30.750 Uh, this scheme has three record, right. 259 17:30.790 --> 17:31.350 Scheme is. 260 17:32.070 --> 17:32.390 Yes. 261 17:32.390 --> 17:32.990 Yes, yes. 262 17:33.110 --> 17:35.910 So now this is, uh, actually it is. 263 17:35.950 --> 17:36.350 Okay. 264 17:36.510 --> 17:37.990 The question is like this one, right? 265 17:38.030 --> 17:42.640 So we have to find out the duplicate records, and we have to permanently delete the duplicate records 266 17:42.640 --> 17:44.200 from the database also. 267 17:44.240 --> 17:44.640 Right? 268 17:44.840 --> 17:45.520 Yes yes yes. 269 17:45.520 --> 17:45.840 Right right. 270 17:45.880 --> 17:46.680 Okay okay okay. 271 17:46.680 --> 17:47.040 Yeah. 272 17:47.520 --> 17:49.360 This is our original table. 273 17:54.680 --> 17:55.280 Uh, yes. 274 17:55.280 --> 18:00.160 Uh, we want to find out first and then we want to remove the same as well. 275 18:07.280 --> 18:07.960 Yes, yes. 276 18:16.160 --> 18:24.360 So now if we choose select the distinct then we can select only we cannot remove uh, this data. 277 18:24.360 --> 18:24.760 Right. 278 18:27.040 --> 18:27.960 Yes yes yes. 279 18:28.000 --> 18:28.320 Yeah. 280 18:29.560 --> 18:29.960 Okay. 281 18:29.960 --> 18:30.480 Okay. 282 18:32.280 --> 18:42.330 Now, uh, let this is the first method, uh, and we are able to use this first method of group by 283 18:42.330 --> 18:47.210 and having because of we have ID column is there with us, right? 284 18:49.330 --> 18:50.530 Let me show you. 285 18:52.530 --> 19:00.490 If we do not have the ID column, then we cannot use this method group by in having because then how 286 19:00.690 --> 19:06.890 we can do minimum and maximum by which column we can do minimum and maximum. 287 19:06.890 --> 19:07.930 That is not possible. 288 19:07.930 --> 19:08.330 Right. 289 19:09.050 --> 19:15.330 So because of we have this contact ID column, that is why we are using group by having method. 290 19:15.370 --> 19:16.930 Now another method is there. 291 19:18.650 --> 19:21.330 And which is the best solution. 292 19:21.330 --> 19:29.370 If we have all these four four combination of column are duplicated then also we can use that method 293 19:29.370 --> 19:29.970 as well. 294 19:31.770 --> 19:32.210 So. 295 19:34.810 --> 19:38.740 Let me Go through that method. 296 19:41.700 --> 19:43.460 Let me first select this data. 297 19:43.460 --> 19:45.900 Insert this data into some temporary table. 298 19:57.500 --> 19:58.020 Okay. 299 19:58.020 --> 20:01.860 Now let me select the this table record. 300 20:04.420 --> 20:08.460 So this is the second method guys. 301 20:10.020 --> 20:12.020 So if you see over here. 302 20:15.860 --> 20:19.500 Here we have used the string function. 303 20:20.980 --> 20:28.340 So these are the different ranking functions are there are rank dense rank row number. 304 20:29.260 --> 20:33.380 And in that function we are we can do the partition by. 305 20:33.940 --> 20:35.230 So here Bye. 306 20:35.750 --> 20:43.070 What are the combinations in through which we can consider any row as a duplicate row? 307 20:43.590 --> 20:49.470 So here we are taking into consideration three particular columns. 308 20:50.790 --> 20:56.350 If this three column has same data, then only row will be considered as a duplicate row. 309 20:56.390 --> 20:58.790 We are not taking consideration. 310 20:58.790 --> 21:00.030 Contact ID right. 311 21:00.510 --> 21:02.830 Only first name last name and email. 312 21:03.710 --> 21:10.310 So that is why in the partition by clause I have written first name, last name and email. 313 21:12.710 --> 21:13.870 And now order by. 314 21:14.470 --> 21:17.070 We are using in the order by contact ID. 315 21:17.870 --> 21:19.430 So now what it will do. 316 21:20.150 --> 21:27.710 So let me just select the this uh let me first select the data from this one. 317 21:35.680 --> 21:36.200 Okay. 318 21:37.680 --> 21:40.080 Let me copy this data into the Excel. 319 21:49.120 --> 21:50.880 So here what it will do. 320 21:53.920 --> 21:57.600 This is a this combination is only one record right. 321 22:06.600 --> 22:08.320 This combination has only one record. 322 22:08.320 --> 22:11.000 So it will give rank one. 323 22:11.480 --> 22:14.760 Now again this combination has one record. 324 22:14.760 --> 22:16.800 So it will give rank one. 325 22:17.400 --> 22:21.840 Now if you see over here Kim has total three records are there. 326 22:21.840 --> 22:22.240 Right. 327 22:23.080 --> 22:31.880 And we are doing partition by this three column in order by order by contact ID. 328 22:32.170 --> 22:37.170 So it will give rank one, two and three. 329 22:40.210 --> 22:45.330 Then again, he is coming two times, so it will give rank one two. 330 22:47.330 --> 22:49.530 Then this Humberto is coming also two times. 331 22:49.530 --> 22:52.250 So it will give one, two and then one. 332 22:52.970 --> 23:01.250 So now what we require, we require only rank one details only right? 333 23:01.850 --> 23:03.770 Rank one details we do not require. 334 23:03.770 --> 23:09.290 Or we can say that we want to delete this rank two and three data. 335 23:16.690 --> 23:17.690 Two and three data. 336 23:17.730 --> 23:18.970 Then this one also. 337 23:21.330 --> 23:22.570 And then this one also. 338 23:22.570 --> 23:24.530 So four records will be deleted. 339 23:25.290 --> 23:27.290 All this one are our requirement. 340 23:27.810 --> 23:29.330 So here what we have done. 341 23:34.660 --> 23:41.620 Now I want to use r k r k equal to one or k equal to one. 342 23:41.620 --> 23:43.980 We want to keep our r k greater than one. 343 23:44.180 --> 23:45.260 We want to delete. 344 23:45.460 --> 23:52.580 But now we can't use this key over here where r k greater than one. 345 23:52.620 --> 23:54.260 We cannot use it over here. 346 23:55.780 --> 23:56.060 Right. 347 23:56.060 --> 23:58.500 It will give error invalid column name. 348 23:58.980 --> 24:07.260 So for that purpose we are encapsulating entire this block into CTE right into CTE. 349 24:15.540 --> 24:17.740 And then we are. 350 24:20.180 --> 24:22.220 Specifying r k greater than one. 351 24:22.700 --> 24:26.300 So all this duplicated data are coming over here. 352 24:26.740 --> 24:29.660 And then if you delete this data from this one. 353 24:32.110 --> 24:32.870 This city. 354 24:34.990 --> 24:35.710 Then it will. 355 24:36.590 --> 24:37.830 Four records are deleted. 356 24:37.830 --> 24:39.870 Now, if you again select this data. 357 24:42.310 --> 24:44.150 Only unique records are coming. 358 24:47.150 --> 24:48.830 So this is the best method. 359 24:48.830 --> 24:54.790 If even if contact id is duplicated, then also this method will work. 360 24:56.150 --> 25:01.350 But if contact id is duplicated then above method will not work right? 361 25:01.390 --> 25:02.030 Above method. 362 25:02.030 --> 25:03.150 Method will not work. 363 25:05.190 --> 25:12.670 Because minimum of contact id two contact id cannot be minimum right for same group. 364 25:14.710 --> 25:18.750 So this both method are clear everyone. 365 25:25.710 --> 25:27.390 You can write in the will. 366 25:27.390 --> 25:29.510 This will be recorded? 367 25:32.510 --> 25:32.910 Yes. 368 25:35.990 --> 25:37.870 This both method are clear, guys. 369 25:42.630 --> 25:42.990 You can. 370 25:43.030 --> 25:43.230 Yes. 371 25:43.230 --> 25:43.470 Yes. 372 25:44.110 --> 25:44.990 Okay, okay. 373 25:46.190 --> 25:49.030 You can speak or write in the chat box. 374 25:55.230 --> 25:57.830 Now let's jump to the. 375 26:00.030 --> 26:00.990 Third question. 376 26:03.030 --> 26:07.310 Find out the department wise 10th highest salary. 377 26:09.150 --> 26:14.990 10th means fourth highest salary or fifth highest salary or seventh highest salary. 378 26:14.990 --> 26:15.710 Anything. 379 26:15.710 --> 26:16.110 Right? 380 26:18.190 --> 26:18.510 Any. 381 26:18.550 --> 26:19.430 Highest salary. 382 26:20.830 --> 26:26.990 So I have already created the table and inserted the data into the same. 383 26:27.680 --> 26:30.560 Now let me select the data from this one. 384 26:33.360 --> 26:35.280 So these are the data right. 385 26:38.360 --> 26:41.480 ID name department id salary and city. 386 26:42.680 --> 26:46.040 So here we have several department IDs. 387 26:46.040 --> 26:49.080 Are there 1234 and five. 388 26:50.640 --> 26:58.120 And one or multiple employees are attached with any particular department. 389 26:59.920 --> 27:02.360 Now what we want we want to find. 390 27:03.440 --> 27:07.760 Let me first select the data order by. 391 27:11.360 --> 27:12.400 Department ID. 392 27:18.680 --> 27:21.360 Let me copy this data into the Excel file. 393 27:33.010 --> 27:33.610 Okay. 394 27:34.770 --> 27:35.410 So now. 395 27:37.970 --> 27:44.130 Based on now, let me try to find the third highest salary. 396 27:44.170 --> 27:45.250 Department wise. 397 27:46.290 --> 27:48.530 Department wise third highest salary. 398 27:48.890 --> 27:51.050 So what should be our answer? 399 27:53.330 --> 27:56.410 So department ID department ID one. 400 27:56.410 --> 27:56.810 Right. 401 27:57.290 --> 27:59.890 So third highest salary will be 80,000. 402 27:59.970 --> 28:00.250 Right. 403 28:01.890 --> 28:03.490 This one will be our answer. 404 28:04.850 --> 28:07.210 Now again Department ID two. 405 28:09.250 --> 28:11.250 Let me do order by over here. 406 28:11.370 --> 28:15.130 Order by department ID order by salary. 407 28:15.610 --> 28:17.450 Order by salary descending. 408 28:18.370 --> 28:21.050 And then copy this data into the Excel file. 409 28:32.900 --> 28:34.180 Now this is our. 410 28:36.500 --> 28:39.740 In Department ID one 80,000 is the third highest salary. 411 28:40.220 --> 28:46.620 Now in department ID two, 65,000 will be our third highest salary. 412 28:47.700 --> 28:49.060 Department ID three. 413 28:49.100 --> 28:58.940 We can say 66,000 is third highest salary then department ID for. 414 29:01.020 --> 29:04.300 This one is our third highest salary in department ID five. 415 29:08.260 --> 29:13.140 This one is our third highest salary in department ID one 80,000. 416 29:13.180 --> 29:14.620 Also coming two times. 417 29:15.420 --> 29:20.020 So I think both will become as our third highest salary. 418 29:20.660 --> 29:23.750 So now Six. 419 29:24.790 --> 29:25.350 Six. 420 29:25.390 --> 29:26.830 It should be six. 421 29:27.510 --> 29:28.710 Record will become. 422 29:31.190 --> 29:37.310 So write 80,000 is coming two times, then 65,000. 423 29:37.350 --> 29:38.590 In department, add two. 424 29:40.870 --> 29:42.270 66,000. 425 29:42.310 --> 29:46.190 In department add three 5500. 426 29:46.310 --> 29:47.670 In Department ID four. 427 29:48.790 --> 29:50.350 In ten five zero. 428 29:50.390 --> 29:52.190 In Department ID five. 429 29:53.830 --> 29:58.590 So this is we have done using this method. 430 30:00.270 --> 30:01.310 Rank method. 431 30:03.710 --> 30:12.270 But actually why here we have done partition by department ID because we want to find the department 432 30:12.430 --> 30:15.310 wise third highest salary. 433 30:15.310 --> 30:17.950 And we have done the order by salary descending. 434 30:19.510 --> 30:22.710 So first highest salary will be ordering. 435 30:24.200 --> 30:31.120 S1, it will give rank one to first salary, then rank two to second highest salary. 436 30:31.800 --> 30:34.320 That's why descending we have to use over here. 437 30:36.040 --> 30:40.600 And that's why now there is one fault in rank function. 438 30:45.200 --> 30:53.040 And it is suggested to use dense rank whenever we want to find the second highest salary or fourth highest 439 30:53.040 --> 31:00.080 salary, any kind of department, ID wise or without any kind of partition by. 440 31:01.040 --> 31:01.760 Because. 441 31:02.400 --> 31:03.840 Now let me do. 442 31:06.520 --> 31:09.720 Now what if I want to find the fourth highest salary? 443 31:11.520 --> 31:13.720 So fourth highest salary over here. 444 31:13.720 --> 31:14.600 One, two. 445 31:16.000 --> 31:17.120 This is three, right. 446 31:17.960 --> 31:21.200 But now there is one problem with rank function. 447 31:21.200 --> 31:24.850 Let me just replace this four with four. 448 31:25.330 --> 31:26.170 Three with four. 449 31:28.490 --> 31:36.090 So ideally fourth highest salary is 70,000 right in department ID one. 450 31:37.610 --> 31:41.850 But it will not give us the answer 70,000. 451 31:43.170 --> 31:45.810 Let me show you the result. 452 31:48.650 --> 31:53.370 See it is keeping department ID one data. 453 31:56.210 --> 31:57.330 Can anyone see me? 454 31:57.370 --> 31:57.530 Why? 455 31:57.570 --> 32:01.810 Department ID one record is not coming. 456 32:06.930 --> 32:08.050 Can anyone say? 457 32:11.810 --> 32:18.130 Because denserank ah automatically ah means it does not rank it. 458 32:18.770 --> 32:24.740 Uh, like if you have similar records then then also it will rank it as one. 459 32:24.780 --> 32:29.500 And suppose both have got same salary, so both will be ranked as one and one. 460 32:30.500 --> 32:32.580 Uh, yes. 461 32:33.660 --> 32:38.260 Uh, whereas uh, if, if we would have used uh. 462 32:42.060 --> 32:42.420 Uh. 463 32:45.540 --> 32:45.820 Yeah. 464 32:45.820 --> 32:47.380 That's the reason I think uh. 465 32:49.620 --> 32:56.300 Actually uh, rank function is, uh, working slightly differently. 466 32:57.140 --> 33:03.180 Um, over rank function, actually, like, uh, if both have got same salary, suppose both have got, 467 33:03.180 --> 33:09.140 uh, 80,000 and the third person has got 70,000, then it will be ranked as one one and then three for 468 33:09.140 --> 33:18.380 rank, whereas for dense whereas for dense rank this will be ranked as one one and two are actually 469 33:18.580 --> 33:24.470 rank of or function, I skip the next ranking and then the function does not skip the next. 470 33:25.030 --> 33:26.110 Ah, yes yes yes. 471 33:26.270 --> 33:26.590 Ah. 472 33:28.150 --> 33:32.630 If you see over here, uh, this is a result of rank function, right. 473 33:33.270 --> 33:36.430 So this uh, one lakh, it will give one rank. 474 33:36.830 --> 33:39.150 With 90,000, it will go two rank. 475 33:39.590 --> 33:41.670 And now 80,000 is coming twice. 476 33:42.110 --> 33:43.830 So it will give three rank. 477 33:43.830 --> 33:45.750 But now what is the problem? 478 33:45.750 --> 33:46.950 In the next value? 479 33:47.590 --> 33:52.870 Rank function will skip one rank over here and it will give five rank five. 480 33:53.870 --> 33:55.710 Yeah it will give five rank. 481 33:55.710 --> 33:59.910 And that is why rank function we should not use in. 482 34:00.230 --> 34:09.910 In this kind of scenario, it is always preferred to use dense rank, because dense rank will not skip 483 34:09.910 --> 34:14.870 the rank or consecutive number, right? 484 34:14.910 --> 34:17.510 Dense rank will give always consecutive number. 485 34:18.560 --> 34:21.400 So now what it will give in terms of rank one. 486 34:22.680 --> 34:23.160 Sorry. 487 34:23.320 --> 34:27.000 One, two three, three and four. 488 34:28.520 --> 34:29.000 Right. 489 34:29.480 --> 34:32.040 So my requirement is fourth highest salary. 490 34:32.600 --> 34:36.880 But it is not giving me in terms of rank. 491 34:38.200 --> 34:46.720 But now it will give me when instead of RK one RK one I will use over here RK. 492 34:48.120 --> 34:52.560 So now it will give me fourth highest salary 70,000. 493 34:53.920 --> 34:59.280 Because it is not skip the rank or you can say consecutive number right? 494 35:05.760 --> 35:10.880 So this is the ultimate use of rank and dense rank function. 495 35:13.600 --> 35:16.040 Now this is also similar one. 496 35:16.520 --> 35:20.960 Find out the overall seventh highest salary from the entire table. 497 35:21.960 --> 35:29.280 So in that case, we do not need to do partition by right because they are not asking for department 498 35:29.280 --> 35:31.360 wise or anything other. 499 35:34.880 --> 35:37.640 Combination wise from the entire table. 500 35:37.640 --> 35:39.760 So here very straightforward result. 501 35:40.360 --> 35:43.320 We need to do order by salary and then descending. 502 35:44.120 --> 35:46.720 And then if we do the seventh one. 503 35:47.760 --> 35:50.120 So now let me first select this data only. 504 35:57.440 --> 35:59.440 So this is my seventh highest. 505 36:00.200 --> 36:01.880 It is not in order I think. 506 36:04.080 --> 36:04.640 Okay. 507 36:04.680 --> 36:05.760 No problem. 508 36:12.040 --> 36:13.920 I think we have done over here already right. 509 36:19.730 --> 36:20.210 Here. 510 36:20.210 --> 36:20.970 No need to. 511 36:25.010 --> 36:26.890 So here, what is the seventh highest salary? 512 36:26.890 --> 36:30.890 1234567. 513 36:30.890 --> 36:32.130 I think 70,000. 514 36:32.170 --> 36:32.450 Right. 515 36:33.210 --> 36:35.730 So let me select the data from this. 516 36:41.730 --> 36:42.170 Okay. 517 36:42.210 --> 36:43.610 70,000 is coming. 518 36:44.130 --> 36:49.050 Jenny is earning the second seventh highest salary from the entire table. 519 36:51.810 --> 36:54.290 So up to here, everything is clear, right? 520 37:00.010 --> 37:00.650 Yes, yes. 521 37:01.930 --> 37:04.890 Okay, now let's see another question. 522 37:08.890 --> 37:09.330 So now. 523 37:11.450 --> 37:14.500 This will this video be uploaded to the YouTube links. 524 37:14.540 --> 37:18.140 Later on we can also refer to it if possible. 525 37:19.180 --> 37:19.980 It will be uploaded. 526 37:20.020 --> 37:20.620 Yes. 527 37:25.100 --> 37:25.900 Thank you so much. 528 37:26.060 --> 37:26.380 Okay. 529 37:28.660 --> 37:31.420 So let's move to the next question. 530 37:31.820 --> 37:38.980 Write a query that calculates the difference between the highest salary found in the marketing and engineering 531 37:38.980 --> 37:39.940 departments. 532 37:41.260 --> 37:43.940 So here we have two different tables. 533 37:43.980 --> 37:45.940 Employee table and department table. 534 37:47.260 --> 37:52.220 I have already inserted the data into employee and department table. 535 37:53.460 --> 37:53.900 So. 536 37:56.780 --> 37:58.100 If you see over here. 537 38:01.860 --> 38:04.020 We have around 100 rows in employee table. 538 38:04.020 --> 38:05.620 And we have different departments. 539 38:05.620 --> 38:10.540 Are there engineering department, human resource department, marketing department. 540 38:11.020 --> 38:12.510 But now what we want, One. 541 38:13.310 --> 38:21.590 We want maximum salary of engineering department and maximum salary of marketing department, and then 542 38:21.590 --> 38:25.990 we want to find the difference between those maximum salary. 543 38:28.750 --> 38:31.070 So now here is very. 544 38:31.110 --> 38:35.670 One frequently used concept we can use over here. 545 38:38.710 --> 38:39.430 Let me. 546 38:44.670 --> 38:49.030 Here first of all we need to do join between both the tables right. 547 38:49.070 --> 38:49.550 DB. 548 38:51.710 --> 38:57.550 Employee and inner join DB department D on E dot. 549 38:58.390 --> 39:02.630 Department ID is equal to d.id. 550 39:03.310 --> 39:06.110 Now we require. 551 39:08.830 --> 39:09.910 Engineering department. 552 39:09.910 --> 39:10.150 Right. 553 39:10.190 --> 39:11.320 They have told us does. 554 39:12.800 --> 39:14.800 So let me copy this one over here. 555 39:16.000 --> 39:16.840 Engineering. 556 39:17.280 --> 39:22.760 Now, what we need to do, we need to do find maximum of salary. 557 39:24.080 --> 39:29.040 So this is the maximum of salary of engineering department. 558 39:33.200 --> 39:42.080 Now what we want we want to find another department is marketing right. 559 39:42.720 --> 39:45.600 So we want to find marketing. 560 39:46.040 --> 39:47.360 Maximum salary. 561 39:48.240 --> 39:51.080 So what is the maximum salary of marketing. 562 39:51.680 --> 39:52.680 It is this one. 563 39:52.720 --> 39:55.000 Now what is our answer. 564 39:55.600 --> 40:02.440 We need to find the difference between this one minus this one. 565 40:05.880 --> 40:07.480 That is 2400. 566 40:08.160 --> 40:12.530 Now how we can Find it over here. 567 40:12.970 --> 40:20.210 So here there is one most frequently used concept which is multiplicity. 568 40:20.690 --> 40:21.570 We have to use. 569 40:21.570 --> 40:26.090 So I will encapsulate this entire block into First City. 570 40:26.850 --> 40:29.170 So this is our first city right. 571 40:29.890 --> 40:33.210 Now we need to use Second city. 572 40:33.250 --> 40:38.570 So over here we can give comma and then city to S. 573 40:40.610 --> 40:43.610 So this will be our second city. 574 40:45.490 --> 40:46.810 Now what we need to do. 575 40:48.970 --> 40:51.930 We need to do city comma city too. 576 40:52.490 --> 41:01.610 So ideally this will be our we can give aliases engineering max salary. 577 41:02.370 --> 41:08.330 And over here we can give marketing make salary. 578 41:09.020 --> 41:15.420 Now this is ideally a cross join between this first city and second city. 579 41:16.380 --> 41:18.860 So now let me select the data. 580 41:20.940 --> 41:23.940 So this has given me two different values right. 581 41:24.540 --> 41:29.340 Now my what I have to do I have to do this one. 582 41:30.140 --> 41:33.860 I can use over here minus. 583 41:35.900 --> 41:36.620 This one. 584 41:39.860 --> 41:42.820 So this will be my answer. 585 41:45.180 --> 41:48.220 2400. 586 41:54.300 --> 41:57.260 So perfect guys right. 587 42:00.460 --> 42:04.340 So in this kind of scenarios we need to use multiple cities. 588 42:06.180 --> 42:08.430 There is a concept of multiple cities. 589 42:09.150 --> 42:16.870 So this solution is I will, uh, maintain all the script. 590 42:16.910 --> 42:21.750 Create table script and insert some script into my, uh, YouTube. 591 42:21.750 --> 42:24.830 You can take it from there and you can practice. 592 42:27.430 --> 42:32.470 Or you can write any other solution if you are aware about it. 593 42:32.470 --> 42:32.910 Right. 594 42:35.790 --> 42:39.710 You can take the script and everything from my YouTube channel. 595 42:41.270 --> 42:43.470 So this is clear, right? 596 42:43.470 --> 42:45.390 This scenario, this question. 597 42:50.830 --> 42:51.590 Clear, guys. 598 42:51.590 --> 42:52.030 Right. 599 42:59.590 --> 43:02.590 You can write in the chat box also. 600 43:02.590 --> 43:03.350 That is fine. 601 43:13.760 --> 43:14.600 Yes, it is clear. 602 43:15.400 --> 43:15.760 Okay. 603 43:15.760 --> 43:16.400 Okay, okay. 604 43:21.520 --> 43:24.480 Now, let me, uh, jump to the. 605 43:27.200 --> 43:30.000 Another questions. 606 43:31.320 --> 43:34.760 So this is one very good platform, guys. 607 43:34.760 --> 43:35.120 Data. 608 43:47.440 --> 43:48.560 In which we can. 609 43:50.720 --> 43:51.960 Practice online. 610 44:01.800 --> 44:02.880 Just a minute. 611 44:07.730 --> 44:11.730 So now let's directly jump to the first question. 612 44:12.730 --> 44:17.810 Actually, I have written over here which questions we need to cover. 613 44:18.570 --> 44:20.490 So in that sequence I will go. 614 44:23.210 --> 44:23.650 User. 615 44:23.930 --> 44:26.690 This is directly a medium level question guys. 616 44:30.570 --> 44:31.450 Give me a minute. 617 44:40.890 --> 44:41.810 Just a minute. 618 44:53.210 --> 44:59.290 So here this is our first problem users third transaction. 619 45:01.530 --> 45:07.300 So assume you are given the table below on Uber transactions made by users. 620 45:09.180 --> 45:13.060 Write a query to obtain the third transaction of every user. 621 45:14.340 --> 45:17.460 Output the user ID, spend, and transaction date. 622 45:18.460 --> 45:20.580 So here they have given us the table. 623 45:26.260 --> 45:27.420 Transactions table. 624 45:30.100 --> 45:34.940 And each user has done several transactions over here right. 625 45:35.940 --> 45:45.260 So now based upon the transaction date column we need to identify the third transaction of any particular 626 45:45.700 --> 45:47.220 all the particular users. 627 45:47.620 --> 45:52.820 So if you see over here 111111 has the third transaction is. 628 45:55.420 --> 45:56.140 This one. 629 45:58.500 --> 45:58.900 Right. 630 45:59.540 --> 46:02.420 Because this is the first transaction this is the second one. 631 46:02.900 --> 46:04.260 And this is the third one. 632 46:05.740 --> 46:09.860 So now which column I require I require. 633 46:13.420 --> 46:14.340 User ID. 634 46:16.620 --> 46:17.140 Spend. 635 46:25.020 --> 46:26.100 Transaction date. 636 46:26.820 --> 46:28.420 And then I need to do. 637 46:31.980 --> 46:32.780 Denserank. 638 46:33.740 --> 46:34.340 Yes we are. 639 46:34.340 --> 46:35.540 We are seeing you. 640 46:35.580 --> 46:39.580 We want user ID wise third transaction. 641 46:40.580 --> 46:43.700 So user ID should be our partition by key. 642 46:44.820 --> 46:46.340 So denserank over. 643 46:49.020 --> 47:00.140 Partition by user ID order by through which ordering we require transaction. 644 47:00.140 --> 47:04.830 So we need to do transaction date. 645 47:07.390 --> 47:08.190 As rank. 646 47:08.870 --> 47:12.670 So now let me select the data over here. 647 47:16.910 --> 47:18.270 It is giving some error. 648 47:18.270 --> 47:19.230 Line six. 649 47:19.630 --> 47:20.190 Okay. 650 47:23.070 --> 47:24.750 I think star is not required. 651 47:29.710 --> 47:33.390 So now if you see over here 111 has the. 652 47:33.910 --> 47:36.990 This one is the third transaction of 111. 653 47:36.990 --> 47:37.390 Right. 654 47:38.990 --> 47:44.390 Now 1 to 1 1 to 1 has this transaction is the third transaction. 655 47:49.430 --> 47:51.230 Now what we want. 656 47:54.790 --> 47:57.390 We only want the third transaction right. 657 47:59.470 --> 48:04.240 So we need to encapsulate this entire block into a city. 658 48:09.320 --> 48:16.640 And then we need to write select star from city where r k equal to three. 659 48:17.400 --> 48:19.040 Now which column we require. 660 48:19.800 --> 48:23.600 You can see the output over here example output. 661 48:24.640 --> 48:28.600 In that output user ID spend and transaction date column are given. 662 48:31.680 --> 48:33.600 So let me write user id. 663 48:36.360 --> 48:37.040 Spend. 664 48:39.560 --> 48:42.800 And transaction date and remove this star. 665 48:43.360 --> 48:45.000 Now let me select the data. 666 48:48.600 --> 48:53.000 So these are the third transaction of all these three users. 667 48:54.080 --> 49:00.930 Now just let me submit this query And let's see. 668 49:00.930 --> 49:02.770 Our answer is correct or not. 669 49:06.730 --> 49:08.610 So great it is accepted. 670 49:10.810 --> 49:16.090 So this solution is clear right guys this question. 671 49:22.330 --> 49:22.730 There. 672 49:24.810 --> 49:33.330 Basically are these are the concepts are important that when we in which scenario we have to use dense 673 49:33.330 --> 49:38.530 rank function, then in which scenario we can use city common table expressions. 674 49:39.050 --> 49:46.330 Because in data analysis or data engineering these are the frequently used concepts. 675 49:48.170 --> 49:49.450 I have one question. 676 49:49.890 --> 49:55.690 Um, like um, then in that case, mostly we do not use rank or what. 677 49:59.500 --> 50:00.460 It is also. 678 50:03.460 --> 50:07.740 If we are using rank, uh, when? 679 50:09.860 --> 50:11.060 If we are aware about. 680 50:11.060 --> 50:13.140 There is no duplicate data. 681 50:15.380 --> 50:16.980 Uh, like combination. 682 50:17.420 --> 50:23.060 If there is no duplicate data of same combination, then rank will give also same result and dense rank 683 50:23.100 --> 50:24.780 will also give same result. 684 50:25.140 --> 50:25.460 Right? 685 50:25.500 --> 50:26.180 Uh, no. 686 50:26.220 --> 50:27.340 Yeah, it will give same. 687 50:27.340 --> 50:28.580 Well, that's what I'm asking that. 688 50:28.620 --> 50:33.820 Then what is the purpose of rank when dense ranking is itself fulfilling its purpose? 689 50:33.860 --> 50:34.580 The purpose? 690 50:42.340 --> 50:49.380 Because they have given this, uh, all this row number, because sometimes, uh, we can use row number 691 50:49.420 --> 50:54.460 as well in place of rank in place of dense rank. 692 50:55.660 --> 50:55.980 Yeah. 693 50:56.580 --> 51:03.910 That is also possible that row number tens rank and rank all these three give us same result. 694 51:04.510 --> 51:06.150 Yeah, that's what I'm asking means. 695 51:06.310 --> 51:09.750 Is there any difference from performance point of view? 696 51:11.070 --> 51:12.270 Performance point of view? 697 51:12.270 --> 51:14.590 There is no difference. 698 51:14.590 --> 51:17.270 Only based on the scenario. 699 51:17.950 --> 51:22.230 You need to decide that which we should use. 700 51:23.910 --> 51:33.150 Okay okay means okay I understood okay performance will not not any reflect but based upon the data 701 51:33.150 --> 51:36.110 or duplicate how much duplicate data is available. 702 51:38.150 --> 51:38.550 Okay. 703 51:39.230 --> 51:40.110 Reside only. 704 51:41.550 --> 51:44.550 All right, all right, all right okay okay. 705 51:46.750 --> 51:47.230 Now. 706 51:52.510 --> 51:59.000 This is also one great example of multiple sending versus opening snaps. 707 52:09.080 --> 52:10.160 Multiple cities. 708 52:10.200 --> 52:10.880 Cities. 709 52:10.880 --> 52:11.960 Ranking functions. 710 52:12.480 --> 52:14.440 Frequently we are using. 711 52:14.960 --> 52:18.320 So let me jump to the question. 712 52:20.800 --> 52:28.720 Assume you are given tables with information of Snapchat users, including their ages and time spent 713 52:28.880 --> 52:30.520 sending and opening snaps. 714 52:36.080 --> 52:37.520 Write a query to obtain. 715 52:37.560 --> 52:39.720 Obtain a breakdown of the time spent. 716 52:46.200 --> 52:48.840 I muted everyone for five minutes. 717 52:50.440 --> 52:57.490 Write a query to obtain a breakdown of time spent sending versus opening snaps as a percentage of total 718 52:57.490 --> 53:02.410 time spent on these activities, grouped by age group. 719 53:04.890 --> 53:08.450 And rank the percentage to a two decimal places in the output. 720 53:10.450 --> 53:18.130 So here actually the calculation they have given us, we need to find the percentage. 721 53:18.330 --> 53:19.290 Percentage of. 722 53:23.290 --> 53:32.250 Comparison between time spent sending and from the total total time spent. 723 53:33.290 --> 53:38.770 So if you see over here they have given us the table activities. 724 53:39.610 --> 53:45.690 And then in this activity table sending are their opening are there and chat are there. 725 53:49.210 --> 53:53.580 But here we need to find the Percentage. 726 53:53.580 --> 53:55.140 Or you can say that ratio. 727 53:57.260 --> 54:02.540 Of opening with total time spent opening and sending both. 728 54:03.540 --> 54:09.700 Then sending and then again total time spent opening and sending both. 729 54:10.740 --> 54:12.460 So here what we need to do. 730 54:18.340 --> 54:21.860 We need to calculate the and also that is also. 731 54:28.660 --> 54:31.580 That is also we need to identify user ID wise. 732 54:31.580 --> 54:31.980 Right. 733 54:32.620 --> 54:38.220 Because one user ID is opening as well as sending. 734 54:38.740 --> 54:40.580 So here what we need to do. 735 54:44.420 --> 54:47.140 First of all let me select star from. 736 54:49.500 --> 54:50.700 Activities table. 737 54:53.110 --> 54:56.990 Here we need to do user ID wise. 738 55:00.310 --> 55:01.310 Time spent. 739 55:08.750 --> 55:10.790 From from activities. 740 55:10.790 --> 55:11.230 Where? 741 55:15.710 --> 55:16.550 Lower of. 742 55:23.190 --> 55:24.270 This is send. 743 55:26.150 --> 55:29.510 Now we need to do some of this time spent. 744 55:33.670 --> 55:34.870 And then group by. 745 55:37.030 --> 55:37.870 User ID. 746 55:41.670 --> 55:43.270 So if you see over here. 747 55:47.310 --> 55:49.230 We can give this aliases. 748 55:53.910 --> 55:54.710 Sending. 749 55:57.230 --> 56:02.190 So this will be our first city, right? 750 56:02.670 --> 56:05.790 Because this one, two, three has multiple records. 751 56:06.110 --> 56:09.310 May have multiple records of sending. 752 56:09.710 --> 56:21.390 So we are doing the sum of all the time spent of sending activity type per group by user ID. 753 56:23.030 --> 56:24.470 Now what we need to do. 754 56:26.710 --> 56:28.870 We need to do the same thing over here. 755 56:28.870 --> 56:35.430 And now we need to do do instead of send we need to use open. 756 56:40.910 --> 56:44.630 So let me try to can I I cannot right. 757 56:48.590 --> 56:51.000 So this is opening time spent. 758 56:53.720 --> 56:54.600 This is spending. 759 56:54.640 --> 56:55.560 This is opening. 760 56:55.920 --> 57:02.680 Now we need to compare this time spent spending and time spent opening. 761 57:02.680 --> 57:08.280 With time spent spending and time spent opening. 762 57:08.800 --> 57:10.600 So now what we need to do. 763 57:15.920 --> 57:18.040 We need to do over here. 764 57:18.040 --> 57:21.000 We need to write in condition. 765 57:23.280 --> 57:25.200 Then we need to write over here. 766 57:25.720 --> 57:26.440 Send. 767 57:29.560 --> 57:36.120 So now this is another great example of multiple cities guys. 768 57:36.560 --> 57:38.080 So this will become our. 769 57:45.080 --> 57:46.160 First city. 770 57:46.680 --> 57:48.610 Now this will become our. 771 57:52.690 --> 57:53.770 Second city. 772 57:57.090 --> 58:01.170 And this will become our third city. 773 58:01.890 --> 58:03.570 Now what we need to do. 774 58:14.210 --> 58:14.650 Now. 775 58:17.170 --> 58:20.450 We need to combine all this city. 776 58:27.170 --> 58:30.330 City one, city two, and city three. 777 58:32.970 --> 58:34.410 So this is our first city. 778 58:34.450 --> 58:36.130 Then this is second city. 779 58:36.570 --> 58:38.610 So which is the common column, guys? 780 58:39.930 --> 58:46.330 Common column between all these three cities user ID, user id and user ID. 781 58:47.420 --> 58:54.820 City one dot user id equal to city two dot user ID. 782 58:54.860 --> 58:59.100 Now again inner join city three dot. 783 59:02.340 --> 59:09.060 City dot user id equal to city three dot user ID. 784 59:16.540 --> 59:17.180 Five. 785 59:19.660 --> 59:20.060 Inner. 786 59:20.060 --> 59:21.300 Join city three. 787 59:36.340 --> 59:42.460 Now what we need to do we have another table is given age breakdown. 788 59:43.100 --> 59:44.780 So if you see over here. 789 59:49.630 --> 59:50.990 They've mentioned over here. 790 59:51.030 --> 59:54.070 Activities grouped by age group. 791 59:58.550 --> 01:00:04,110 So that we need to, uh, join this age breakdown table as well. 792 01:00:04,790 --> 01:00:06,110 So let me. 793 01:00:11,390 --> 01:00:17,430 Write is age breakdown a on a dot user ID equal to. 794 01:00:21,550 --> 01:00:22,990 City dot user id. 795 01:00:26,150 --> 01:00:35,150 What is this error is coming city with city as this is our first city. 796 01:00:35,950 --> 01:00:37,190 City two as. 797 01:00:41,310 --> 01:00:42,910 City three as. 798 01:00:46,560 --> 01:00:47,160 Okay. 799 01:00:47,560 --> 01:00:51,880 So this is our age breakdown table we have used. 800 01:00:54,800 --> 01:00:57,920 Now what columns is required in our output. 801 01:00:58,600 --> 01:01:01,360 In our output age bucket is required. 802 01:01:01,840 --> 01:01:04,000 So let me write it. 803 01:01:04,040 --> 01:01:04,440 Age. 804 01:01:07,080 --> 01:01:08,000 Age bucket. 805 01:01:08,880 --> 01:01:10,160 Then what we require. 806 01:01:12,440 --> 01:01:14,720 We require sending percentage. 807 01:01:18,280 --> 01:01:25,640 So here we need to find sending divided by this one. 808 01:01:25,640 --> 01:01:26,400 This one is. 809 01:01:29,880 --> 01:01:32,680 Total activities. 810 01:01:32,680 --> 01:01:33,880 So this is sending. 811 01:01:39,640 --> 01:01:42,240 Divided by total. 812 01:01:48,050 --> 01:01:52,290 Sending divided by total that we need to do round. 813 01:02:07,170 --> 01:02:10,250 And then what we need to do, we need to copy this one. 814 01:02:10,970 --> 01:02:12,410 And over here opening. 815 01:02:17,570 --> 01:02:19,330 Opening divided by total. 816 01:02:24,850 --> 01:02:26,650 So let me execute the data. 817 01:02:30,410 --> 01:02:31,130 Okay. 818 01:02:36,850 --> 01:02:38,290 Line number 19. 819 01:02:45,380 --> 01:02:46,140 Round off. 820 01:02:59,180 --> 01:03:03,780 And now this is actually this is the sending percentage and this is opening percentage. 821 01:03:04,380 --> 01:03:08,300 So let me give you the aliases as send. 822 01:03:10,740 --> 01:03:12,820 In is open. 823 01:03:15,620 --> 01:03:18,260 So now let me run the query again. 824 01:03:19,620 --> 01:03:26,980 So if you see over here now is there any other point they have mentioned round the. 825 01:03:30,500 --> 01:03:32,460 Two decimal places in the output. 826 01:03:34,780 --> 01:03:37,660 I think all we need to do order by age bucket. 827 01:03:38,340 --> 01:03:40,260 So let me do that. 828 01:03:41,950 --> 01:03:42,710 Order by. 829 01:03:46,350 --> 01:03:50,510 Age bucket and then run and then submit the query. 830 01:03:55,270 --> 01:03:55,830 So great. 831 01:03:55,830 --> 01:03:56,950 It is accepted. 832 01:03:56,950 --> 01:03:58,590 Our result is accepted. 833 01:04:01,910 --> 01:04:03,590 Now let me run again. 834 01:04:06,990 --> 01:04:09,630 So now let me guys I am unmuting you for. 835 01:04:11,710 --> 01:04:12,310 You all. 836 01:04:12,510 --> 01:04:15,110 Now you can ask the question over here. 837 01:04:17,510 --> 01:04:19,150 If you have the. 838 01:04:19,270 --> 01:04:20,270 This time we have. 839 01:04:20,310 --> 01:04:23,110 We are using three different cities. 840 01:04:24,030 --> 01:04:26,590 Multiple city but in multiple city. 841 01:04:27,310 --> 01:04:35,830 This is for sending sending time spent to calculate user ID wise total time spent sending. 842 01:04:35,870 --> 01:04:39,550 Then this is calculating user ID wise. 843 01:04:39,550 --> 01:04:39,720 Device. 844 01:04:39,760 --> 01:04:46,200 Total time spent in opening activity type and this is sending plus opening activity both. 845 01:04:46,960 --> 01:04:52,440 Then what we are doing sending divided by total and opening divided by total. 846 01:04:54,320 --> 01:04:57,280 But now we want age group. 847 01:05:00,000 --> 01:05:01,640 We want age bucket as well. 848 01:05:01,680 --> 01:05:02,200 Right? 849 01:05:02,200 --> 01:05:07,600 So different age bucket is given in age breakdown table and in the age breakdown. 850 01:05:07,600 --> 01:05:12,360 Also user id column is given if you see over here. 851 01:05:14,840 --> 01:05:20,120 So that that user ID only we are joining with any of the city. 852 01:05:22,640 --> 01:05:29,080 And then at the end we are doing age bucket order by default order ascending order. 853 01:05:32,200 --> 01:05:35,040 So it will divide this one divided by this one. 854 01:05:35,040 --> 01:05:36,280 Multiply by 100. 855 01:05:38,800 --> 01:05:46,680 And then we are doing rounding two because Teo mentioned over here round the percentage to two decimal 856 01:05:46,680 --> 01:05:47,360 places. 857 01:05:49,080 --> 01:05:53,080 If we don't do rounding then it will not give us proper result. 858 01:05:57,680 --> 01:05:58,040 Okay. 859 01:05:58,080 --> 01:05:59,400 It is giving some. 860 01:06:02,600 --> 01:06:04,360 Direct integer value only. 861 01:06:04,640 --> 01:06:07,120 But we want two decimal places as well, right? 862 01:06:11,200 --> 01:06:16,160 So now let me know guys if you are clear with this solution. 863 01:06:24,200 --> 01:06:25,560 This is the perfect. 864 01:06:26,360 --> 01:06:34,000 Actually whenever you read this kind of question at that time, after reading the question, you should 865 01:06:34,000 --> 01:06:41,410 have clear in your mind that in this kind of scenario, we have to use multiple city only. 866 01:06:44,450 --> 01:06:47,250 If you know any other method then that is also fine. 867 01:06:48,130 --> 01:06:52,610 But for me, cities are kind of my favorite. 868 01:06:57,450 --> 01:07:00,650 You are clear with the solution guys. 869 01:07:07,890 --> 01:07:08,650 Sagar. 870 01:07:08,650 --> 01:07:09,250 Surya. 871 01:07:12,570 --> 01:07:13,850 You can write in the chat. 872 01:07:14,810 --> 01:07:15,450 Yeah, sure. 873 01:07:19,890 --> 01:07:23,330 So this is the perfect use of multiple cities. 874 01:07:26,290 --> 01:07:29,970 The lower function is important there instead of lower function. 875 01:07:34,250 --> 01:07:36,660 Why we have used the lower lower function. 876 01:07:37,340 --> 01:07:39,540 Let me write directly over here. 877 01:07:39,740 --> 01:07:42,020 Activity type. 878 01:07:42,820 --> 01:07:52,540 But now we don't know that whether we have to write descend in uppercase or lowercase or like this one. 879 01:07:52,580 --> 01:07:57,420 First letter is capital letter and others are small letter. 880 01:07:57,420 --> 01:07:57,860 Right. 881 01:07:58,500 --> 01:08:04,180 So we are not aware about this send in which form we need to use. 882 01:08:04,260 --> 01:08:09,020 So because of that reason we are using the lower function over here. 883 01:08:09,700 --> 01:08:11,740 So now what it will done. 884 01:08:13,020 --> 01:08:16,460 We we have to write everything in small letter. 885 01:08:17,500 --> 01:08:18,300 So it will. 886 01:08:18,580 --> 01:08:25,140 If it is even in capital letter, it will convert into small letter and it will give us the result. 887 01:08:26,980 --> 01:08:30,380 And now it will if, if we use over here upper. 888 01:08:33,020 --> 01:08:34,910 Then we need to write over here. 889 01:08:36,350 --> 01:08:37,790 Send in uppercase. 890 01:08:40,590 --> 01:08:42,590 So that is the only the difference. 891 01:08:46,150 --> 01:08:49,110 That is the purpose we have used over here or. 892 01:08:54,430 --> 01:09:03,430 Maybe sometimes if the case is not matching it will not give us this particular ah send activities data. 893 01:09:10,910 --> 01:09:12,990 Now, this, uh, question is clear. 894 01:09:12,990 --> 01:09:14,470 Everyone solution. 895 01:09:14,510 --> 01:09:15,310 This solution. 896 01:09:33,230 --> 01:09:34,080 Now let me. 897 01:09:42,520 --> 01:09:44,760 Go to the next question. 898 01:09:53,160 --> 01:09:58,880 We will keep up to here or we want to continue. 899 01:10:07,680 --> 01:10:09,440 We will see few more questions. 900 01:10:09,440 --> 01:10:11,360 So tomorrow, few more queries. 901 01:10:11,920 --> 01:10:12,400 Tomorrow. 902 01:10:12,400 --> 01:10:14,760 You want to continue for one two queries or. 903 01:10:18,480 --> 01:10:19,200 Let me. 904 01:10:24,360 --> 01:10:25,360 Can we do it tomorrow? 905 01:10:26,480 --> 01:10:27,320 Yes yes sure. 906 01:10:27,360 --> 01:10:28,040 Yes yes. 907 01:10:28,080 --> 01:10:31,080 We can continue from here tomorrow.