1 00:02.280 --> 00:02.760 Okay. 2 00:04.680 --> 00:05.560 Hello, everyone. 3 00:06.400 --> 00:08.600 So this is our phase two. 4 00:08.640 --> 00:10.600 We are starting from today. 5 00:11.560 --> 00:17.920 Phase one is already completed, which was SQL Conceptual Training. 6 00:18.160 --> 00:27.680 Now in this phase we will see uh, around 50 plus SQL queries, which is asked in uh, different Fang 7 00:27.680 --> 00:29.000 and MNC companies. 8 00:30.160 --> 00:32.480 So we will try to solve that. 9 00:34.320 --> 00:39.320 And whenever you have any question, you can pause me and you can ask at any time. 10 00:44.800 --> 00:52.440 So before this, uh, attending of the phase two, you if you are aware about SQL then no issue. 11 00:52.440 --> 00:56.760 But otherwise you have to go through the phase one videos. 12 00:57.520 --> 01:04.530 There are around 12 videos are there, so if you watch it then it will be easy for you to. 13 01:07.090 --> 01:08.410 For this phase. 14 01:16.050 --> 01:17.250 So one minute. 15 01:18.450 --> 01:18.850 Okay. 16 01:28.010 --> 01:32.490 So let's directly start with the our first query for today. 17 01:38.210 --> 01:46.810 So it is related to sign up uh sign up activation rate in this query has been asked in TikTok SQL interview 18 01:46.810 --> 01:47.530 question. 19 01:48.650 --> 01:54.330 So this is the brief description about the problem. 20 01:56.130 --> 02:03.150 New TikTok Users sign up with their emails, and they confirm their sign up by replying to the text 21 02:03.150 --> 02:06.550 confirmation to activate their accounts. 22 02:08.550 --> 02:11.470 Users may receive multiple text messages. 23 02:17.110 --> 02:24.310 Users may receive multiple text messages for account confirmation until they have confirmed their new 24 02:24.310 --> 02:24.950 accounts. 25 02:27.990 --> 02:29.470 So here they are. 26 02:30.670 --> 02:38.230 Firstly, a particular user will will sign up and after they sign up they will receive to confirm their 27 02:38.230 --> 02:38.830 account. 28 02:38.870 --> 02:45.630 So for that confirmed is the status will be given in the text message. 29 02:45.630 --> 02:50.550 So they are receiving text messages in after confirmation only. 30 02:50.750 --> 02:54.110 It is been confirmed by the particular user. 31 02:55.360 --> 03:03.040 So a senior analyst is interested to know the activation rate of specified users in the email table. 32 03:04.120 --> 03:06.600 Write a query to find the activation rate. 33 03:07.720 --> 03:10.840 Round the percentage to two decimal places. 34 03:13.240 --> 03:17.840 So here emails table contain the information of user sign up details. 35 03:18.360 --> 03:20.000 So there are two different tables. 36 03:20.000 --> 03:24.240 They have given us emails table and text table. 37 03:24.800 --> 03:33.920 So in the emails table if you see over here email ID user ID and signup date is given right. 38 03:33.960 --> 03:42.400 And in the text table, text ID, email ID and sign up action has been given. 39 03:43.560 --> 03:48.000 And if you see over here email ID column is common in both the table. 40 03:48.040 --> 03:48.360 Write. 41 03:48.400 --> 03:52.160 Email ID is common in both the tables. 42 03:56.090 --> 03:56.650 Now. 43 04:02.210 --> 04:08.570 Email table contain the information of user sign up details and text table contains the user's activation 44 04:08.570 --> 04:09.490 information. 45 04:11.290 --> 04:18.730 So the analysis interested in the activation rate of the specified user in the emails table, which 46 04:18.730 --> 04:23.570 may not include all the users that could potentially be found in the text table. 47 04:24.090 --> 04:28.090 So here we need to find the sign up activation rate right. 48 04:28.530 --> 04:33.450 So there will be total users are there total users are there. 49 04:33.450 --> 04:45.530 And those users who have done the sign up by doing the confirmation in reply to the text message. 50 04:45.530 --> 04:45.970 Right. 51 04:48.250 --> 04:51.580 So now let me go through the both the tables. 52 04:51.580 --> 04:58.420 Actually, I have created both the tables and inserted the data also into the same. 53 04:59.060 --> 05:00.980 Now let me go through this. 54 05:00.980 --> 05:02.140 Both the tables. 55 05:05.660 --> 05:06.060 Okay. 56 05:06.100 --> 05:10.340 First one is the email table and second one is the text table. 57 05:10.940 --> 05:16.300 So if you see over here in the email table how many users are there? 58 05:17.060 --> 05:19.180 How many distinct users are there. 59 05:23.380 --> 05:29.020 So if you see over here 123456 users are there. 60 05:29.020 --> 05:29.500 Right. 61 05:30.820 --> 05:37.380 And out of this, uh, six users, how many users have done the confirmation? 62 05:39.620 --> 05:43.700 Can anyone tell me, uh, how many users have done the confirmation? 63 05:46.220 --> 05:46.580 Two. 64 05:47.500 --> 05:47.780 Two. 65 05:48.100 --> 05:48.420 Okay. 66 05:48.460 --> 05:51.400 Two users have done the confirmation right now. 67 05:51.400 --> 05:55.280 How can we identify the sign up activation rate? 68 06:02.800 --> 06:04.440 Can anyone tell me that? 69 06:06.560 --> 06:09.920 How can we identify the sign up activation rate? 70 06:11.000 --> 06:13.640 Next item up. 71 06:16.880 --> 06:17.280 No. 72 06:21.240 --> 06:22.200 Means, uh. 73 06:23.520 --> 06:24.360 Uh, yes. 74 06:24.360 --> 06:25.480 We have to do joins. 75 06:25.480 --> 06:25.720 Right? 76 06:25.720 --> 06:29.840 We have to do joins between these two tables by email ID column. 77 06:29.840 --> 06:30.320 Right. 78 06:31.200 --> 06:41.160 But now how can we identify the sign up activation rate means how many users have confirmed the sign 79 06:41.200 --> 06:44.200 up and total number of users. 80 06:44.200 --> 06:47.160 We need to do division between these two. 81 06:47.290 --> 06:47.770 right? 82 06:48.810 --> 06:49.290 Right. 83 06:50.210 --> 06:53.410 So how many users have confirmed? 84 06:53.450 --> 06:54.690 Two users have confirmed. 85 06:54.690 --> 06:55.090 Right. 86 06:55.450 --> 06:57.770 And how many total number of users? 87 06:58.210 --> 07:00.530 Total six users are there. 88 07:00.530 --> 07:01.010 Right? 89 07:02.330 --> 07:02.690 Yeah. 90 07:03.010 --> 07:06.410 So two divided by six will be our answer. 91 07:09.730 --> 07:10.210 Right. 92 07:10.810 --> 07:14.290 So now 33%. 93 07:14.810 --> 07:16.410 Okay 33%. 94 07:16.770 --> 07:20.530 Now for that one minute. 95 07:24.890 --> 07:28.690 For that we need to first identify the total number of users. 96 07:28.690 --> 07:29.170 Right. 97 07:30.610 --> 07:31.730 Total number of users. 98 07:31.730 --> 07:34.930 So that we can find in the email table itself. 99 07:36.210 --> 07:36.690 Right. 100 07:37.490 --> 07:38.970 Total six users are there. 101 07:39.170 --> 07:47.700 So what I have done over here I have joined the two tables email table and text table with inner join 102 07:47.700 --> 07:51.060 and only one column is common write email ID. 103 07:53.260 --> 08:01.060 So I have done the join based on the email ID column and then count of I have done the count of user 104 08:01.060 --> 08:01.740 ID right? 105 08:05.300 --> 08:13.100 Just a minute count of user ID so it will be six only, right? 106 08:13.140 --> 08:17.180 Six distinct user we have write in the emails table. 107 08:18.460 --> 08:18.900 Right. 108 08:19.780 --> 08:28.460 Now what we need to do now in the second one we need to identify the confirmed user. 109 08:30.140 --> 08:33.580 Confirmed user how many total confirmed user are there. 110 08:34.380 --> 08:39.420 So again we need to join between the two table with email ID column. 111 08:39.980 --> 08:43.220 But we need to specify the where condition. 112 08:43.320 --> 08:46.880 right where sign up action is equal to confirmed. 113 08:49.840 --> 08:52.480 So how many record we will get over here? 114 08:57.880 --> 08:58.560 Only two. 115 08:58.760 --> 08:59.760 Two record, right. 116 09:01.040 --> 09:08.800 Now our requirement is we need to need to divide two divided by six. 117 09:08.960 --> 09:10.520 How can we do that? 118 09:11.360 --> 09:13.920 Can anyone help me to write the query? 119 09:16.320 --> 09:18.320 We need to do two divided. 120 09:18.320 --> 09:19.000 We need. 121 09:19.040 --> 09:21.920 This is our second resultset. 122 09:21.920 --> 09:23.600 This is our first result set. 123 09:24.120 --> 09:26.760 Now we need to consider the ratio right. 124 09:27.400 --> 09:35.240 That two divided by six means 33% or 0.33% is the ratio. 125 09:35.480 --> 09:37.040 But how can we do that? 126 09:37.040 --> 09:38.480 By writing the query. 127 09:42.530 --> 09:44.410 We can create city as well. 128 09:45.450 --> 09:47.250 Okay, we can create city. 129 09:48.490 --> 09:53.010 Okay, let me try with city. 130 09:55.930 --> 09:58.090 Now what I will do, I will do. 131 09:58.130 --> 09:59.730 This is our first query, right? 132 10:00.850 --> 10:02.170 Now, now what to do? 133 10:06.210 --> 10:07.770 You can run this in one city. 134 10:07.770 --> 10:08.330 Another one. 135 10:08.330 --> 10:09.930 We can store it in another city. 136 10:13.490 --> 10:14.090 Okay. 137 10:14.130 --> 10:18.050 We can keep this in one city, right? 138 10:18.090 --> 10:19.250 Now another city. 139 10:19.290 --> 10:21.250 How can we create another city? 140 10:24.250 --> 10:27.530 We can create it as a comma bracket with another city name. 141 10:27.530 --> 10:29.250 And we'll store that six number. 142 10:29.290 --> 10:29.890 Okay. 143 10:29.930 --> 10:30.970 This is comma. 144 10:32.090 --> 10:33.970 Then this is another bracket. 145 10:33.970 --> 10:35.770 And then we will store it. 146 10:35.770 --> 10:38.450 This one in this one right. 147 10:41.860 --> 10:42.140 Yeah. 148 10:42.740 --> 10:46.420 I think we need to give name city to city two. 149 10:46.580 --> 10:46.940 Yes. 150 10:48.900 --> 10:49.980 Okay, now. 151 10:51.180 --> 10:56.260 Now what we can do now you can do a join between those two CTE. 152 10:56.260 --> 10:57.820 And get that we can do the divide. 153 10:57.980 --> 10:59.420 Select star from. 154 11:02.140 --> 11:03.420 City two. 155 11:04.500 --> 11:05.580 This is total, right? 156 11:05.620 --> 11:06.300 City two. 157 11:06.340 --> 11:06.660 Okay. 158 11:06.700 --> 11:10.100 City to join inner. 159 11:10.140 --> 11:19.740 Join inner join CTE on city on common ID is email ID, right. 160 11:19.740 --> 11:25.540 Um, but, uh, we haven't taken over here in the email ID, right? 161 11:26.060 --> 11:26.700 Yeah, yeah. 162 11:29.060 --> 11:31.340 So confirmed users. 163 11:32.700 --> 11:33.140 Okay. 164 11:33.580 --> 11:33.940 Yeah. 165 11:35.900 --> 11:37.500 So now what we can do. 166 11:37.670 --> 11:38.070 We? 167 11:38.110 --> 11:42.590 We actually we do not need to use inner join over here. 168 11:42.910 --> 11:47.470 We can directly do comma CTE okay. 169 11:49.070 --> 11:51.510 Because this is also a cross join. 170 11:51.510 --> 11:54.710 But it will work as a inner join only. 171 11:54.710 --> 11:58.350 It will not give us multiple thing. 172 11:58.350 --> 11:58.830 Right. 173 11:59.070 --> 12:04.430 So now city total we need to do two. 174 12:05.510 --> 12:06.230 Confirm. 175 12:06.590 --> 12:12.270 Confirm the user confirmed users divided by total users right. 176 12:12.710 --> 12:13.230 Yes. 177 12:14.510 --> 12:16.950 You have to use that Ct2 dot. 178 12:17.310 --> 12:20.150 And also we have to use the alias of Ct2 Dot. 179 12:20.190 --> 12:22.710 Now actually that will also fine. 180 12:23.030 --> 12:31.470 We cannot if we not use then that then also fine because user is there in only CTE, right? 181 12:32.070 --> 12:36.370 It is not available in city two so it will not generate error. 182 12:36.410 --> 12:36.890 Right. 183 12:37.250 --> 12:37.690 Agreed. 184 12:38.970 --> 12:43.170 And same as for total users is only in city two. 185 12:44.090 --> 12:45.330 Not in city one. 186 12:45.330 --> 12:45.610 Right? 187 12:45.650 --> 12:50.290 If it is common in both one, then we have to use aliases. 188 12:52.090 --> 12:54.810 Yeah, but it is only available single time only. 189 12:54.810 --> 12:58.850 So if we do not use alias then also it is fine. 190 12:59.130 --> 12:59.970 One minute. 191 13:03.970 --> 13:05.410 Then also it is fine. 192 13:05.770 --> 13:12.330 Now let me try to end this concept is what is this concept is known. 193 13:13.850 --> 13:15.730 It is known as multiplicity. 194 13:15.770 --> 13:16.210 Right? 195 13:19.010 --> 13:19.290 Yeah. 196 13:19.330 --> 13:20.250 Multiplicity. 197 13:20.290 --> 13:21.410 Multiplicity. 198 13:22.210 --> 13:24.050 Because, uh, we can. 199 13:29.090 --> 13:36.700 We can use again, uh, one more city like this, uh, this city for n number of city. 200 13:36.740 --> 13:37.300 We can use. 201 13:37.300 --> 13:37.740 Right? 202 13:40.060 --> 13:41.500 This is also correct, right? 203 13:42.700 --> 13:43.180 Yeah. 204 13:43.220 --> 13:47.620 We can use n number of cities, but right now it is not our requirement. 205 13:47.900 --> 13:52.700 So now let me try to execute this one I think. 206 13:54.140 --> 13:55.900 Let me see the result. 207 13:57.540 --> 13:59.340 It is giving wrong result. 208 14:03.860 --> 14:07.060 Can anyone tell me why it is giving zero. 209 14:13.460 --> 14:19.180 Because in the in the select last clause we have not written any join condition here just directly. 210 14:19.540 --> 14:20.100 No. 211 14:21.220 --> 14:24.060 Once again, can you typecast it? 212 14:24.780 --> 14:25.140 Yeah. 213 14:25.540 --> 14:26.020 Uh, yeah. 214 14:26.060 --> 14:32.790 Because two divided by six it is actually a float or decimal value. 215 14:32.830 --> 14:33.150 Right. 216 14:33.150 --> 14:37.070 But we are considering over here as integer only. 217 14:37.070 --> 14:41.150 So that is why that is the issue over here. 218 14:41.910 --> 14:47.710 So what we need to do we need to convert this one into the decimal decimal. 219 14:48.430 --> 14:58.470 So let me try const cost of this as decimal decimal nine comma two. 220 14:58.510 --> 15:01.510 We can give anything five comma 2 or 9 comma two. 221 15:02.350 --> 15:03.390 Then again this one. 222 15:03.390 --> 15:04.710 Also we need to cast. 223 15:04.750 --> 15:05.470 Cast. 224 15:07.670 --> 15:12.150 Is desi mul nine two. 225 15:14.350 --> 15:15.910 Now let me try. 226 15:16.150 --> 15:19.150 Actually, one bracket is less over here. 227 15:21.510 --> 15:23.350 Now let me try to execute. 228 15:25.830 --> 15:26.270 Okay. 229 15:26.310 --> 15:29.790 ..3333. 230 15:29.790 --> 15:31.250 three right now. 231 15:32.650 --> 15:34.770 We need to multiply this by 100. 232 15:34.770 --> 15:35.210 Right. 233 15:37.290 --> 15:40.010 Because we want 33% as answer. 234 15:40.050 --> 15:42.450 Our answer is 33%. 235 15:42.450 --> 15:42.890 Right. 236 15:45.490 --> 15:47.810 So 33.33 is giving. 237 15:47.810 --> 15:54.250 Now we just want first four first four digit only. 238 15:54.250 --> 16:00.730 How can we get round round round with two. 239 16:01.090 --> 16:04.410 Round only two round with two. 240 16:05.210 --> 16:05.810 Comma two. 241 16:07.970 --> 16:08.810 Comma two. 242 16:11.330 --> 16:11.650 Right. 243 16:11.650 --> 16:12.210 Only. 244 16:16.730 --> 16:17.330 Okay. 245 16:17.450 --> 16:20.810 This is also problem 33.33. 246 16:21.010 --> 16:24.170 In rest it is converted to zero. 247 16:24.650 --> 16:29.100 Now let me try to cast this one also. 248 16:29.100 --> 16:29.820 Cast. 249 16:30.260 --> 16:30.980 Integer. 250 16:32.020 --> 16:35.020 Cast as decimal only. 251 16:35.020 --> 16:35.180 Right. 252 16:35.220 --> 16:40.740 Because you want that decimal 9 or 5 comma two. 253 16:42.460 --> 16:45.420 Now let me try to execute this one. 254 16:48.300 --> 16:48.620 Now. 255 16:48.620 --> 16:49.220 Perfect. 256 16:49.220 --> 16:49.660 Right. 257 16:53.660 --> 16:55.420 Now this is our requirement right? 258 16:55.700 --> 16:58.180 33.33 we want. 259 17:01.060 --> 17:04.820 We want to give the alias name as as well. 260 17:06.300 --> 17:07.060 Uh, yes. 261 17:07.060 --> 17:07.580 Over here. 262 17:07.580 --> 17:08.020 Right. 263 17:09.180 --> 17:09.620 Yeah. 264 17:10.420 --> 17:16.140 As uh, activation ratio, you can give any alias as per your choice. 265 17:17.860 --> 17:19.180 Activation ratio. 266 17:19.660 --> 17:24.420 Now 33.33 will be our activation ratio. 267 17:24.420 --> 17:24.860 Right. 268 17:28.910 --> 17:29.430 Now. 269 17:29.430 --> 17:36.270 Now, this is the main thing, uh, when you have to do cast, when you have to do use multiple city 270 17:37.870 --> 17:39.510 when you have to do round. 271 17:40.510 --> 17:46.590 So basically this is, that is the main purpose of this, uh, query. 272 17:47.390 --> 17:54.230 Because here we get to know, right, that in this kind of situation we have to use multiple city. 273 17:58.670 --> 18:01.110 That is the main purpose of this query. 274 18:01.830 --> 18:06.910 Now let me do you have any doubt or it is clear right. 275 18:07.790 --> 18:10.870 It is clear like how can we get this, uh, script. 276 18:10.870 --> 18:11.310 Right. 277 18:11.310 --> 18:14.470 I mean, the insert, just the insert script and the create script. 278 18:15.550 --> 18:16.070 Okay. 279 18:16.110 --> 18:26.040 Uh, I will put it on the Google Drive, and I will share at the end of the each of the hour session. 280 18:26.400 --> 18:26.800 Okay. 281 18:26.840 --> 18:27.320 Okay. 282 18:27.360 --> 18:33.520 That will be you will not only create table write, create table description and insert table and also 283 18:33.560 --> 18:34.200 the solution. 284 18:34.200 --> 18:35.760 Like we will just refer it. 285 18:35.800 --> 18:40.960 Once we we are not able to do solve it, then we will refer this solution okay okay. 286 18:41.000 --> 18:45.080 Sure I will send it after the session. 287 18:46.080 --> 18:49.440 And also I will put this video into my YouTube channel. 288 18:50.040 --> 18:53.480 So whenever you want to refer you can refer. 289 18:55.480 --> 18:56.000 Yeah. 290 18:56.200 --> 19:02.680 Actually we need the script as well because you know that if we just create the manually from our side. 291 19:02.680 --> 19:03.960 So it will take some time. 292 19:04.880 --> 19:06.680 Yes yes yes I will. 293 19:07.360 --> 19:09.080 I will provide scripts also. 294 19:11.880 --> 19:15.080 I will put it on the Google Drive and share the link. 295 19:16.880 --> 19:17.800 That's perfect. 296 19:18.000 --> 19:18.640 Sure, sure. 297 19:19.480 --> 19:20.200 Okay. 298 19:21.160 --> 19:24.140 Now let's jump to another question. 299 19:25.580 --> 19:32.940 This is a super cloud customer related question and it is asking Microsoft Company. 300 19:33.340 --> 19:38.860 So now let me go through the description of Microsoft Azure. 301 19:38.900 --> 19:48.100 Super Cloud customer is defined as a company super cloud customer that purchases at least one product 302 19:48.100 --> 19:50.660 from each product category. 303 19:53.060 --> 19:57.260 So now there are different multiple product categories are there. 304 19:57.940 --> 20:05.460 And we need to identify super cloud customer how we can consider a customer as a super cloud customer. 305 20:06.340 --> 20:14.420 That customer should have purchased at least one product from each of the distinct product category. 306 20:15.220 --> 20:22.190 Now write a query that effectively identify the customer ID of such super cloud customers. 307 20:22.910 --> 20:26.910 So now again I have created the table. 308 20:27.790 --> 20:29.870 This is customer contracts table. 309 20:30.150 --> 20:35.310 So in that customer ID product ID and amount column is given. 310 20:35.670 --> 20:39.270 And another table is products table. 311 20:41.670 --> 20:45.750 So in that product ID product category and product name is given. 312 20:46.390 --> 20:48.830 Now let me. 313 20:51.070 --> 20:52.590 Show you both the tables. 314 20:56.350 --> 20:59.030 So first one is the customer contract table. 315 20:59.390 --> 21:02.590 And second one is the products table. 316 21:02.990 --> 21:11.950 Now if you see over here one product ID and product ID column is. 317 21:14.070 --> 21:14.710 Common right. 318 21:14.750 --> 21:15.470 Common column. 319 21:15.510 --> 21:17.510 Common column in both the tables. 320 21:17.510 --> 21:19.640 Is now here. 321 21:19.680 --> 21:21.640 Different customer ID are there, right? 322 21:22.520 --> 21:24.880 And they have purchased the product ID. 323 21:24.880 --> 21:27.880 And this product ID is basically. 324 21:29.920 --> 21:32.360 Comes into some product category right. 325 21:35.320 --> 21:38.000 Now this is the data. 326 21:38.760 --> 21:41.600 Now what is our requirement. 327 21:41.640 --> 21:43.640 One minute I will take uh. 328 21:46.320 --> 21:48.520 Take this data into the Excel file. 329 21:53.240 --> 21:54.120 One minute. 330 22:05.600 --> 22:06.040 Okay. 331 22:06.080 --> 22:08.000 This is the first table data. 332 22:10.160 --> 22:10.760 And. 333 22:19.210 --> 22:21.010 This is the second table data. 334 22:25.450 --> 22:26.290 One minute. 335 22:34.490 --> 22:35.050 Now. 336 22:39.330 --> 22:43.210 Now, can anyone tell me who is our super cloud customer? 337 22:46.570 --> 22:49.850 By seeing both these tables. 338 22:55.210 --> 22:59.250 First let me know you understand the definition of super cloud. 339 22:59.610 --> 23:01.370 Customer or not. 340 23:07.850 --> 23:08.890 You can explain that. 341 23:08.930 --> 23:09.410 Yes, sir. 342 23:09.410 --> 23:09.770 Yes, sir. 343 23:09.970 --> 23:10.570 Understood. 344 23:11.050 --> 23:12.450 Okay, I will explain one. 345 23:12.490 --> 23:22.110 Once again we can consider those customers as a super cloud customer who has purchased at least one 346 23:23.030 --> 23:31.870 product, at least one product from each of this product category means he has purchased one product 347 23:31.870 --> 23:38.150 from analytics category, one product from category, and one product from compute category. 348 23:38.630 --> 23:46.150 Right now, by watching this data, can you tell me the customer ID. 349 23:49.870 --> 23:52.470 Of that particular customer? 350 23:56.350 --> 24:03.070 771737677. 351 24:04.950 --> 24:05.830 Perfect. 352 24:06.510 --> 24:07.350 Seven. 353 24:08.310 --> 24:10.390 If you see seven over here. 354 24:10.430 --> 24:10.950 Seven. 355 24:10.950 --> 24:11.510 Again. 356 24:13.440 --> 24:15.920 So he has purchased product ID one. 357 24:16.400 --> 24:16.760 Right. 358 24:16.760 --> 24:19.080 So one means analytics category right. 359 24:19.480 --> 24:24.240 So this is done now three three means container category. 360 24:24.240 --> 24:30.040 So this is also one more category in seven six means compute category. 361 24:30.040 --> 24:31.640 So this is also perfect. 362 24:32.000 --> 24:37.320 Now if you see for two two means two product ID means analytics category. 363 24:37.320 --> 24:40.880 Now two is coming again two is coming in five. 364 24:41.400 --> 24:47.160 So five means compute category but two is not purchase anything from container category. 365 24:47.160 --> 24:47.400 Right. 366 24:47.440 --> 24:52.360 So two will not consider our in our result set right. 367 24:52.400 --> 24:53.480 Same for three. 368 24:54.160 --> 24:55.840 Three is coming only one time. 369 24:55.840 --> 25:02.880 So it is will not be considered for four is coming for one and four is coming for five. 370 25:04.040 --> 25:07.120 So for again not purchase container category right. 371 25:08.080 --> 25:10.450 So only seven is our result set. 372 25:12.730 --> 25:13.170 Correct. 373 25:13.170 --> 25:14.530 Right now. 374 25:15.050 --> 25:17.850 Now, how can we write the query for that? 375 25:20.970 --> 25:25.050 So now what we have to do one. 376 25:25.970 --> 25:34.130 Firstly we need to identify how many total distinct category are available with us. 377 25:35.970 --> 25:39.610 How many total distinct product category are available? 378 25:39.650 --> 25:40.290 How many? 379 25:44.010 --> 25:45.770 One, two and three. 380 25:45.770 --> 25:46.250 Right. 381 25:46.970 --> 25:49.410 So, uh, so over here. 382 25:52.570 --> 26:00.610 Firstly, I first I have used the products table and in the products table count of distinct product 383 26:00.610 --> 26:01.490 category. 384 26:04.050 --> 26:06.410 Count of distinct product category are there. 385 26:06.410 --> 26:06.730 Right. 386 26:06.770 --> 26:09.950 Three three distinct product categories are there. 387 26:10.430 --> 26:13.230 Now what we have to do. 388 26:16.110 --> 26:18.670 We need to identify the customer. 389 26:22.350 --> 26:23.310 Select. 390 26:24.190 --> 26:31.230 Firstly it is like this from customer contracts. 391 26:32.230 --> 26:44.390 See inner join products P on c dot product id is equal to p dot product ID. 392 26:46.270 --> 26:46.790 Now. 393 26:49.830 --> 26:51.710 Here our requirement is customer ID. 394 26:51.710 --> 26:55.630 We have to find right and count of distinct. 395 27:01.950 --> 27:02.870 P dot. 396 27:04.230 --> 27:05.670 Why it is not coming. 397 27:08.520 --> 27:08.880 Product. 398 27:08.880 --> 27:09.400 Spelling. 399 27:14.640 --> 27:16.440 Uh, product. 400 27:19.040 --> 27:20.240 Now it is coming, right? 401 27:22.920 --> 27:25.920 So now let me try to execute this one. 402 27:30.200 --> 27:30.720 Okay. 403 27:30.720 --> 27:35.240 Because of we have used the aggregate function we need to do group by right. 404 27:39.040 --> 27:43.120 Whenever we are using any aggregate function we need to do group by right. 405 27:44.560 --> 27:48.560 So now watch this both the queries. 406 27:52.880 --> 27:54.520 And now what is our requirement. 407 27:54.520 --> 27:59.480 We our requirement is customer ID equal to seven only right. 408 28:00.720 --> 28:03.480 Because distinct category are three. 409 28:04.080 --> 28:10.890 And we need to identify those customers who are coming in three categories only. 410 28:10.890 --> 28:11.290 Right. 411 28:12.050 --> 28:13.250 All three categories. 412 28:16.370 --> 28:20.890 So our over here our requirement is customer ID equal to seven. 413 28:20.890 --> 28:23.970 We want customer ID equal to seven as a result set. 414 28:24.770 --> 28:27.570 Now how can we identify that. 415 28:32.170 --> 28:35.130 Group by customer product and product category. 416 28:39.610 --> 28:40.930 Group by customer. 417 28:40.930 --> 28:41.610 Product. 418 28:43.010 --> 28:45.010 On the three columns you have to group by. 419 28:49.090 --> 28:50.730 Means this column. 420 28:52.250 --> 28:52.690 Yeah. 421 28:54.970 --> 28:55.930 Uh, no. 422 28:59.410 --> 29:00.890 Actually our. 423 29:00.930 --> 29:02.980 Now, what is our requirement? 424 29:03.460 --> 29:04.900 We requirement. 425 29:06.740 --> 29:09.260 Customer ID equal to seven only. 426 29:10.580 --> 29:12.020 That is our requirement. 427 29:12.300 --> 29:16.860 In one column customer ID and result will be seven. 428 29:17.260 --> 29:19.180 This will be our output only. 429 29:19.700 --> 29:22.820 From this entire both the queries. 430 29:29.060 --> 29:33.220 Now can anyone think and let me know how we can achieve this? 431 29:36.180 --> 29:39.940 Again, we can create two separate key condition. 432 29:39.940 --> 29:41.020 We can find out. 433 29:41.260 --> 29:45.940 We can create two separate key and then do a uh, yes, perfect. 434 29:46.700 --> 29:49.180 Two separate city is the perfect answer. 435 29:49.820 --> 29:51.780 So let me write it. 436 29:52.380 --> 29:56.980 This is our first city, right comma. 437 29:57.060 --> 29:59.140 City two s. 438 29:59.740 --> 30:01.960 This will be our second city. 439 30:04.520 --> 30:05.480 Now what? 440 30:05.520 --> 30:08.360 Select star from city. 441 30:09.120 --> 30:10.320 Now over here. 442 30:10.320 --> 30:12.040 We need to do inner join. 443 30:13.600 --> 30:14.520 Inner join. 444 30:14.560 --> 30:15.400 City. 445 30:15.440 --> 30:16.280 Two on. 446 30:16.280 --> 30:17.880 Two on city. 447 30:17.920 --> 30:18.520 Dot. 448 30:18.560 --> 30:19.320 City. 449 30:19.960 --> 30:20.400 City. 450 30:21.000 --> 30:23.120 Equal to city two. 451 30:23.160 --> 30:23.560 Dot. 452 30:23.600 --> 30:24.080 Pete. 453 30:25.280 --> 30:25.640 Pete. 454 30:26.640 --> 30:26.960 Pete. 455 30:27.000 --> 30:27.880 Customer ID? 456 30:27.920 --> 30:30.000 We haven't taken over here also, right? 457 30:31.040 --> 30:31.520 Got it. 458 30:31.760 --> 30:34.360 We have taken only count in our count. 459 30:34.400 --> 30:36.800 Three will be matching with this count. 460 30:36.840 --> 30:37.280 Three. 461 30:37.840 --> 30:38.280 Correct. 462 30:39.200 --> 30:39.680 Now. 463 30:40.080 --> 30:41.080 Very simple. 464 30:41.400 --> 30:46.680 Now here we need to do CTE two dot. 465 30:47.080 --> 30:47.560 Customer. 466 30:47.880 --> 30:48.920 Customer ID. 467 30:52.800 --> 30:55.880 Now let me try to execute this one. 468 30:56.440 --> 30:57.920 And this is our result. 469 31:01.090 --> 31:02.450 Perfect, right? 470 31:03.730 --> 31:04.130 Yeah. 471 31:09.930 --> 31:18.490 So this is again, one more example of how and in which situation we can use multiple city. 472 31:21.890 --> 31:33.410 And what I have done, I have never tried to write the entire query in one one time. 473 31:34.010 --> 31:36.330 Always try to break down your problem. 474 31:36.570 --> 31:37.010 Right. 475 31:37.050 --> 31:39.610 That that is the main thing over here. 476 31:39.610 --> 31:43.250 We have also done we have done the breakdown, the problem only right. 477 31:43.250 --> 31:44.410 We have done right. 478 31:44.410 --> 31:46.930 First thing this query. 479 31:47.050 --> 31:55.050 Then we have understand the customer ID wise count and then we have done the multiple city. 480 31:55.090 --> 31:58.340 Don't directly Right like this. 481 31:59.060 --> 32:05.300 Try to break down your problem into small, small part and then combine everything. 482 32:07.460 --> 32:11.940 That is the best way to solve any SQL query. 483 32:16.220 --> 32:21.180 Like apart from CTE, we can do we can solve this using subquery as well right. 484 32:22.980 --> 32:24.300 Subquery. 485 32:27.620 --> 32:30.380 Because of over here multiple city are there. 486 32:30.380 --> 32:32.900 How can we write the subquery. 487 32:32.940 --> 32:33.620 Can you. 488 32:35.820 --> 32:38.940 What I meant is this is one method of doing solution. 489 32:38.980 --> 32:42.020 Yeah that I understand this is one method. 490 32:42.780 --> 32:50.540 But now if it is a single query I think if it is single query then we can write a subquery. 491 32:50.540 --> 32:54.020 But uh this is multiple query. 492 32:54.020 --> 32:55.440 Are there right now. 493 32:55.760 --> 32:59.160 How can we convert this into a sub query? 494 32:59.280 --> 33:01.400 That is my question. 495 33:04.560 --> 33:05.760 Selector. 496 33:08.960 --> 33:10.240 Suppose we write like this. 497 33:10.240 --> 33:14.280 Select star from and then this entire query. 498 33:14.280 --> 33:16.640 We can put it inside. 499 33:17.440 --> 33:17.680 Yeah. 500 33:17.720 --> 33:18.960 Inside that query. 501 33:21.480 --> 33:23.160 And we can give alias. 502 33:23.320 --> 33:24.240 Suppose t. 503 33:25.160 --> 33:27.080 Now this is our. 504 33:29.240 --> 33:30.680 One result set right. 505 33:30.920 --> 33:31.360 Yeah. 506 33:31.400 --> 33:32.960 This is our one result set. 507 33:33.480 --> 33:35.880 But where we can put this one. 508 33:39.480 --> 33:42.560 So you can write where and find it out. 509 33:44.440 --> 33:45.400 Okay. 510 33:45.400 --> 33:46.120 Let me try. 511 33:46.120 --> 33:46.680 Where. 512 33:50.880 --> 33:51.200 In. 513 33:53.450 --> 33:54.890 Pity in that query. 514 33:56.730 --> 33:58.130 Yeah, this may be. 515 33:58.130 --> 33:59.370 I think it will work. 516 34:00.890 --> 34:02.650 Now let me execute. 517 34:04.770 --> 34:05.210 Yeah. 518 34:06.650 --> 34:08.610 We can we can just keep the customer ID. 519 34:08.650 --> 34:11.010 Yeah, we can keep the customer ID only. 520 34:14.530 --> 34:14.810 So. 521 34:14.810 --> 34:15.130 Yes. 522 34:15.130 --> 34:15.770 Perfect. 523 34:17.250 --> 34:19.130 So this is again one more thing. 524 34:19.130 --> 34:23.770 Uh, one in SQL, that is the main beauty, right? 525 34:23.770 --> 34:26.610 We can solve one problem with multiple ways. 526 34:32.730 --> 34:35.530 Okay, now let's jump to the next query. 527 34:44.210 --> 34:46.970 So this is query card launch success. 528 34:46.970 --> 34:50.570 It is asking JP Morgan Chase SQL interview question. 529 34:52.100 --> 34:56.180 So your team is at JP Morgan. 530 34:57.220 --> 34:59.500 Chase is soon launching a new credit card. 531 35:00.860 --> 35:05.060 You are asked to estimate how many cards you will issue in the first month. 532 35:06.100 --> 35:12.700 Before you can answer this question, you want to first get some perspective on how well new credit 533 35:12.700 --> 35:16.740 card launches typically do in their first month. 534 35:17.860 --> 35:24.740 Write a query that outputs the name of the credit card and what is the issued amount in its launch month? 535 35:26.060 --> 35:28.820 What is the issued amount in its launch month? 536 35:29.500 --> 35:34.700 The launch month is the earliest record in the monthly card issued table for a given card. 537 35:36.060 --> 35:39.180 Order the results starting from the biggest issued amount. 538 35:41.300 --> 35:46.260 Okay, so now they have given us the monthly card issued. 539 35:47.740 --> 35:48.420 Issued. 540 35:48.700 --> 35:54.830 And over here the card name issued amount issue month and insurer is given. 541 35:57.790 --> 36:00.550 So now let me execute this query. 542 36:01.430 --> 36:06.710 So if you see over here there are two different credit cards are available. 543 36:06.710 --> 36:07.150 Right. 544 36:09.230 --> 36:13.470 And all this card has issue here. 545 36:13.470 --> 36:15.030 An issue month is given. 546 36:16.510 --> 36:20.270 Now we need to identify those. 547 36:21.110 --> 36:26.430 The credit card is issue month and issue year. 548 36:26.470 --> 36:29.270 What is the amount is there. 549 36:33.150 --> 36:37.590 For both the different or distinct card name right. 550 36:38.150 --> 36:40.710 Now can anyone tell me over here the answer. 551 36:44.750 --> 36:47.450 For Chase Sapphire Reserve. 552 36:48.850 --> 36:51.730 What is the issued amount in its launch month? 553 36:52.210 --> 36:53.090 In launch year? 554 36:58.610 --> 37:02.050 Means minimum of year end month. 555 37:02.050 --> 37:03.170 We need to consider. 556 37:03.170 --> 37:03.610 Right? 557 37:05.810 --> 37:06.890 Yes, I think yes. 558 37:06.930 --> 37:12.090 Group one we have to order by card name. 559 37:12.090 --> 37:12.810 Let me do. 560 37:13.850 --> 37:15.930 Now let me know the our result. 561 37:15.930 --> 37:17.330 What should be our result. 562 37:19.810 --> 37:23.210 2021 I mean it should be the latest month, right? 563 37:23.810 --> 37:26.450 No, no, not the latest month. 564 37:26.930 --> 37:29.970 It should be the launch month. 565 37:31.250 --> 37:33.810 First month will be the launch month, right? 566 37:34.410 --> 37:35.090 Ah, yes. 567 37:36.490 --> 37:38.610 First month or anything. 568 37:38.610 --> 37:40.970 Which is minimum one by. 569 37:41.010 --> 37:42.610 By considering year and month. 570 37:42.610 --> 37:43.050 Both. 571 37:43.090 --> 37:43.490 Right. 572 37:44.820 --> 37:45.100 Huh? 573 37:45.900 --> 37:46.220 Huh? 574 37:47.620 --> 37:50.620 So in 2021. 575 37:53.420 --> 37:54.740 There are two outputs. 576 37:55.260 --> 37:57.340 So one will be for 2021. 577 37:57.460 --> 37:58.940 The Chase Freedom flex. 578 38:01.180 --> 38:04.420 Those who are not speaking, please be on mute. 579 38:06.940 --> 38:08.340 Those who are not speaking. 580 38:08.740 --> 38:09.900 Now let me know. 581 38:11.780 --> 38:12.020 Yeah. 582 38:12.020 --> 38:13.340 For this first card. 583 38:13.380 --> 38:13.740 Chase. 584 38:13.740 --> 38:14.140 Freedom. 585 38:14.140 --> 38:14.660 Flex. 586 38:14.820 --> 38:16.460 Uh, it will be 55,000. 587 38:17.500 --> 38:18.660 55,000. 588 38:18.700 --> 38:19.180 Okay. 589 38:19.780 --> 38:21.700 And fifth row again. 590 38:21.980 --> 38:22.260 Again. 591 38:22.300 --> 38:27.740 Fifth row, 11th month, 2021 5150. 592 38:27.740 --> 38:28.060 Okay. 593 38:28.100 --> 38:31.060 That will be our answer right now. 594 38:31.100 --> 38:34.220 How can we achieve achieve this? 595 38:39.300 --> 38:44.830 We require two rows in our result set right Now how can we achieve this? 596 38:50.470 --> 38:55.270 So partition on card name, then order by month, partition by card name and year. 597 38:55.310 --> 39:00.630 Then order by month and take the first month. 598 39:00.670 --> 39:02.590 Okay, let me write it. 599 39:03.590 --> 39:04.390 Card name. 600 39:04.430 --> 39:04.870 Issued. 601 39:04.870 --> 39:05.430 Amount. 602 39:07.510 --> 39:08.190 Month. 603 39:10.030 --> 39:10.510 Issue. 604 39:10.590 --> 39:10.950 Year. 605 39:11.270 --> 39:11.990 And then. 606 39:12.710 --> 39:13.950 Row number. 607 39:14.390 --> 39:15.070 Row number. 608 39:15.510 --> 39:16.070 Over. 609 39:16.670 --> 39:17.150 Partition. 610 39:17.150 --> 39:18.150 By partition. 611 39:18.150 --> 39:20.270 By partition. 612 39:20.270 --> 39:20.950 By card. 613 39:20.990 --> 39:21.470 Name. 614 39:21.510 --> 39:24.110 Card order by. 615 39:27.470 --> 39:28.190 Order by. 616 39:28.230 --> 39:28.910 Issue. 617 39:28.950 --> 39:29.430 Year. 618 39:29.630 --> 39:30.230 Comma. 619 39:30.270 --> 39:31.030 Issue. 620 39:31.350 --> 39:31.990 Month. 621 39:32.030 --> 39:32.550 Month. 622 39:35.430 --> 39:37.350 Do we need to do descending or not? 623 39:38.950 --> 39:39.590 No. 624 39:39.590 --> 39:41.070 No no. 625 39:41.330 --> 39:42.330 For the latest month. 626 39:42.330 --> 39:42.690 Yes. 627 39:42.690 --> 39:43.170 Right. 628 39:43.210 --> 39:43.570 Okay. 629 39:43.570 --> 39:43.770 Yes. 630 39:43.770 --> 39:44.730 For the latest month. 631 39:44.730 --> 39:46.210 If it is required, then yes. 632 39:46.730 --> 39:48.810 Otherwise not now. 633 39:49.090 --> 39:52.450 We can give some alias name like R.K.. 634 39:53.690 --> 39:55.850 Now let me remove this. 635 39:56.610 --> 40:04.330 Now what we can do, take another query and r r k equals to one where condition. 636 40:05.130 --> 40:05.770 Okay. 637 40:06.250 --> 40:10.010 So can we use like this where r k equal to one. 638 40:12.330 --> 40:13.930 No I don't think it will work. 639 40:14.130 --> 40:15.530 We cannot use it right. 640 40:15.970 --> 40:16.410 Yes. 641 40:17.210 --> 40:20.810 Now subquery subquery. 642 40:21.930 --> 40:26.010 And what is another thing also we can use we can use CTE. 643 40:26.010 --> 40:28.890 And then again okay perfect. 644 40:28.890 --> 40:30.010 We can use city. 645 40:30.570 --> 40:31.610 Let me try. 646 40:34.690 --> 40:40.900 City star from city t where r k is equal to one. 647 40:40.900 --> 40:41.340 Right. 648 40:41.660 --> 40:42.060 Yeah. 649 40:44.860 --> 40:46.580 Here two rows it will should come. 650 40:46.580 --> 40:47.140 Right. 651 40:47.140 --> 40:50.300 One lakh 50,050 5000 55,000. 652 40:50.780 --> 40:54.620 Now another way also we can do. 653 40:57.500 --> 40:58.820 That is subquery. 654 40:58.820 --> 41:00.020 Also we can use right. 655 41:00.260 --> 41:00.460 Yeah. 656 41:00.540 --> 41:05.580 Inline view select star from select star from this. 657 41:07.060 --> 41:09.860 And then we can write that as t. 658 41:09.860 --> 41:11.420 And then we can read the value. 659 41:16.540 --> 41:17.620 Okay perfect. 660 41:17.940 --> 41:24.860 So this is uh actually this is a concept in SQL server. 661 41:24.860 --> 41:25.980 What it is known. 662 41:26.140 --> 41:32.660 Can anyone tell me this below concept window function. 663 41:33.780 --> 41:35.580 Uh, no ranking function. 664 41:36.060 --> 41:37.020 No ranking function. 665 41:37.020 --> 41:43.270 That is fine, but we have written the Select query in the from clause. 666 41:44.030 --> 41:44.870 Inline view. 667 41:45.430 --> 41:45.710 Yeah. 668 41:45.750 --> 41:46.510 Inline view. 669 41:46.550 --> 41:48.670 It is known in Oracle. 670 41:51.510 --> 41:52.430 Derived table. 671 41:52.470 --> 41:52.910 No. 672 41:52.950 --> 41:53.230 Yeah. 673 41:53.230 --> 41:54.990 Perfect derived table. 674 41:55.870 --> 42:00.750 This is known as derived table in SQL server in inline view in Oracle. 675 42:07.630 --> 42:08.870 So perfect. 676 42:09.070 --> 42:10.790 One more query we have solved. 677 42:11.390 --> 42:13.350 Again we have used over here city. 678 42:13.350 --> 42:17.310 And one more concept that is derived table. 679 42:19.470 --> 42:22.110 Now let me go through the another query. 680 42:27.470 --> 42:30.430 So these are the mainly topics are there right. 681 42:30.750 --> 42:38.680 Like ranking functions city multiple Multiplicity derived table joins. 682 42:38.840 --> 42:43.920 These are the main concepts we are using day to day basis in our life. 683 42:45.520 --> 42:48.120 Now let me go through another query. 684 42:50.480 --> 42:51.800 Pharmacy analytics. 685 42:54.480 --> 43:00.240 This is asking CVS health SQL interview question. 686 43:00.880 --> 43:07.960 Now CVS health is trying to better understand his pharmacy sales and how well different drugs are selling. 687 43:08.880 --> 43:18.040 Write a query to find the top two drugs sold in in terms of units sold for each manufacturer. 688 43:20.320 --> 43:23.880 List your results in alphabetical order by manufacturer. 689 43:25.960 --> 43:29.920 Now this is the pharmacy sales table. 690 43:32.360 --> 43:38.420 They have given product ID units sold total sales, Cogs, manufacturer and drug. 691 43:40.540 --> 43:54.020 Now, whenever you find this keyword for each manufacturer at that time, you have to use either partition 692 43:54.020 --> 43:56.820 by clause or group by query. 693 44:00.100 --> 44:00.820 Correct. 694 44:01.060 --> 44:03.380 Because for each manufacturer. 695 44:03.380 --> 44:03.780 Right. 696 44:05.460 --> 44:11.780 For each product, for each product, or for each product category, or for each product subcategory, 697 44:12.180 --> 44:19.700 we need to identify the data by each of the different categories or each of the different manufacturer. 698 44:20.340 --> 44:25.420 So grow without group by or without partition by that query cannot be solved. 699 44:25.420 --> 44:26.700 It is not possible. 700 44:29.060 --> 44:30.020 Now this is the. 701 44:30.300 --> 44:31.750 This is the data. 702 44:32.630 --> 44:34.310 Pharmacy sales data. 703 44:34.350 --> 44:37.990 I have already created the table. 704 44:38.030 --> 44:40.230 Now let me show you the result. 705 44:44.350 --> 44:45.990 Now over here. 706 44:45.990 --> 44:47.430 What is the our requirement? 707 44:47.470 --> 44:50.190 Have you got it? 708 44:53.150 --> 45:03.230 We need to identify the top two drugs sold in terms of units sold for each manufacturer. 709 45:03.270 --> 45:04.030 Manufacturer. 710 45:05.590 --> 45:08.470 So if you see over here, let me. 711 45:10.950 --> 45:15.190 Copy this data into into the Excel file. 712 45:20.110 --> 45:22.510 Now actually I have already ordered the data. 713 45:22.510 --> 45:22.990 Right. 714 45:27.390 --> 45:28.550 Order by descending. 715 45:28.550 --> 45:30.240 I have already done over here. 716 45:31.000 --> 45:32.360 And units sold. 717 45:32.360 --> 45:36.240 We have to do top two ducks based upon units sold. 718 45:37.400 --> 45:42.360 So for this manufacturer, this will be our result. 719 45:45.040 --> 45:47.320 Now whenever manufacturers. 720 45:49.920 --> 45:56.240 Change this will be our result because we need to identify based upon units sold. 721 45:58.400 --> 46:06.280 So whatever the highest units sold is their top two highest units sold will be our result set for each 722 46:06.320 --> 46:07.160 manufacturer. 723 46:07.200 --> 46:13.560 Right now again, whenever manufacturer will change that will be our result set. 724 46:13.600 --> 46:17.120 So again this will be our result set. 725 46:17.200 --> 46:17.680 Right. 726 46:20.880 --> 46:27.280 Now let me know how can we get this data in SQL query. 727 46:32.940 --> 46:34.420 Yeah, it is simple, right? 728 46:34.860 --> 46:41.060 We need to do partition by manufacturer, order by unit sold descending. 729 46:46.460 --> 46:47.140 And then. 730 46:50.700 --> 46:51.300 After. 731 46:51.580 --> 46:53.500 Where arc less than or equal to two. 732 46:53.540 --> 46:53.820 Right. 733 46:53.820 --> 46:59.500 Because we want top two right by each manufacturer. 734 47:04.820 --> 47:06.820 So this will be our result set. 735 47:08.380 --> 47:12.980 Now let me know can we use this same thing through derived table also. 736 47:12.980 --> 47:13.420 Right. 737 47:15.420 --> 47:15.820 Yeah. 738 47:16.940 --> 47:17.540 Same thing. 739 47:17.540 --> 47:19.580 We can use it through derived table also. 740 47:19.580 --> 47:27.870 That is also simple one select star from then entire this query store. 741 47:30.470 --> 47:32.190 And then we can use S3. 742 47:32.710 --> 47:39.110 And then we can use this condition where condition where are less than two. 743 47:43.150 --> 47:44.430 So same thing. 744 47:47.910 --> 47:51.310 So here when you need to understand this thing it is. 745 47:55.230 --> 47:58.390 Can we do the same thing through group by. 746 48:02.590 --> 48:03.750 Is it possible. 747 48:08.110 --> 48:09.470 So let me think. 748 48:09.510 --> 48:10.310 We can try it. 749 48:10.590 --> 48:11.030 Group by. 750 48:11.030 --> 48:11.750 It's possible. 751 48:11.950 --> 48:12.270 Yeah. 752 48:12.790 --> 48:15.830 So over here this thing is not required. 753 48:17.190 --> 48:18.590 Order by unit. 754 48:18.590 --> 48:20.390 So group by group by. 755 48:20.430 --> 48:24.000 Let me do group by manufacturer and unit. 756 48:24.000 --> 48:29.880 Sole manufacturer and then unit sold. 757 48:29.880 --> 48:30.640 Are there, right? 758 48:31.360 --> 48:31.760 Yes. 759 48:32.000 --> 48:32.880 Order by. 760 48:36.160 --> 48:36.840 Unit sold. 761 48:36.840 --> 48:37.600 Descending. 762 48:37.880 --> 48:40.960 Now it will give everything right. 763 48:41.960 --> 48:42.360 Uh. 764 48:42.560 --> 48:43.320 Let me. 765 48:43.360 --> 48:44.120 Okay. 766 48:45.040 --> 48:46.040 You can remove that. 767 48:46.880 --> 48:47.240 And. 768 48:51.560 --> 48:51.960 Now. 769 48:52.720 --> 48:54.080 Group by manufacturer. 770 48:54.120 --> 48:54.880 Order by unit. 771 48:54.880 --> 48:55.360 Sold. 772 48:56.320 --> 48:57.680 You need to remove that unit sold. 773 48:57.680 --> 48:59.360 Because that is not part of group by. 774 49:00.320 --> 49:00.640 Yeah. 775 49:00.640 --> 49:05.760 But but again you don't have any aggregation right. 776 49:08.280 --> 49:09.360 Aggregate function. 777 49:09.360 --> 49:12.520 So unit sold a one minute. 778 49:12.800 --> 49:16.280 Can I do max of max of unit sold. 779 49:19.840 --> 49:20.600 Column. 780 49:26.890 --> 49:30.490 But again, this will not give the proper result. 781 49:31.690 --> 49:34.090 Now what is the issue? 782 49:35.130 --> 49:36.050 Order by clause. 783 49:37.210 --> 49:38.650 Because it is not contained in either. 784 49:42.890 --> 49:44.530 Manufacturer unit sold. 785 49:44.530 --> 49:47.050 But it is giving max only one. 786 49:47.090 --> 49:48.450 Only not two. 787 49:50.010 --> 49:51.690 Our requirement is two, right? 788 49:52.130 --> 49:57.290 We need two top two per each manufacturer. 789 49:57.850 --> 49:59.850 But it is giving max only, right? 790 50:02.770 --> 50:03.850 This is wrong, right? 791 50:06.210 --> 50:10.530 If you see this result set it is giving 24 row. 792 50:11.410 --> 50:13.410 But this is giving 12 row only. 793 50:20.590 --> 50:20.990 Um. 794 50:21.630 --> 50:23.310 Now, can anyone tell me? 795 50:24.870 --> 50:27.550 I think we can do unit sold as well, right? 796 50:27.590 --> 50:28.750 Group by manufacturer. 797 50:28.750 --> 50:29.070 Comma. 798 50:29.110 --> 50:29.990 Unit sold. 799 50:30.430 --> 50:32.110 Group by manufacturer. 800 50:32.110 --> 50:32.510 Comma. 801 50:32.550 --> 50:33.510 Unit sold. 802 50:36.310 --> 50:38.230 And then we need to remove this. 803 50:40.030 --> 50:41.710 Let me remove this, Max. 804 50:44.430 --> 50:47.110 But again this is not giving proper result. 805 50:53.390 --> 50:58.390 I think this problem you can try it by yourself through group by. 806 50:58.390 --> 51:00.070 But I think it is in group by. 807 51:00.070 --> 51:00.830 It is not. 808 51:01.590 --> 51:04.590 We cannot achieve perfect result. 809 51:05.670 --> 51:10.790 If it is maximum one or top one then we can get it. 810 51:11.910 --> 51:15.950 Or if it is minimum one then also we can get it. 811 51:15.950 --> 51:17.750 But top two or top three. 812 51:21.040 --> 51:24.000 It is not possible, I think, in this scenario. 813 51:27.520 --> 51:30.000 But you can try to use it. 814 51:32.760 --> 51:34.080 By yourself. 815 51:40.200 --> 51:42.800 Now let me know up to here any doubt. 816 51:47.240 --> 51:49.640 We have solved total four queries today. 817 51:51.880 --> 51:53.760 Are you guys available tomorrow? 818 51:59.600 --> 51:59.960 Yeah. 819 52:00.160 --> 52:01.680 Tomorrow is holiday, right? 820 52:06.000 --> 52:10.080 Not for tomorrow, 11:00. 821 52:10.080 --> 52:13.520 If you are available, then we can conduct class. 822 52:14.160 --> 52:17.090 Otherwise, next weekend we will meet. 823 52:23.850 --> 52:28.130 Those who are not available are not aware. 824 52:28.130 --> 52:33.450 You can go through the phase one video in my YouTube channel. 825 52:33.970 --> 52:35.690 This is my YouTube channel link. 826 52:40.770 --> 52:41.650 Hello guys. 827 52:43.250 --> 52:43.730 One minute. 828 52:43.770 --> 52:44.930 Good morning everyone. 829 52:45.330 --> 52:46.370 This is the playlist. 830 52:46.370 --> 52:47.410 So, uh. 831 52:49.690 --> 52:57.330 And in this playlist you can go through this, select, uh, this link. 832 52:57.610 --> 53:01.410 Are you ready to supercharge your Java coding skills but don't know how? 833 53:01.930 --> 53:04.170 Introducing lambdas and streams. 834 53:04.330 --> 53:07.330 Advanced Java tools that will not only enable you. 835 53:08.570 --> 53:11.210 Now let me know if you have any any doubt. 836 53:11.250 --> 53:12.010 Anything. 837 53:15.420 --> 53:19.620 Like right after the class, you'll be uploading those four questions in the drive. 838 53:19.660 --> 53:20.060 Right. 839 53:20.860 --> 53:21.340 Yeah. 840 53:21.340 --> 53:26.660 I will upload this four script into the drive and we'll share you the link. 841 53:27.100 --> 53:29.980 You all are available in WhatsApp group, right? 842 53:30.420 --> 53:30.820 Yeah. 843 53:31.340 --> 53:31.820 Yes. 844 53:31.820 --> 53:35.940 So I will share it the link into the WhatsApp group. 845 53:37.860 --> 53:39.300 Can you do one more question? 846 53:39.300 --> 53:40.620 We'll round it up to five. 847 53:41.100 --> 53:41.460 Yeah. 848 53:43.420 --> 53:44.260 Okay. 849 53:44.300 --> 53:46.900 So let me do the fifth question. 850 53:49.020 --> 53:50.020 Assume you are. 851 53:50.460 --> 53:53.180 This is related to first transaction. 852 53:53.220 --> 53:58.700 Assume you are given a table containing it's a user transactions. 853 53:59.060 --> 54:07.060 Write a query that retrieves the customers whose first transaction was valued at $50 or more. 854 54:08.300 --> 54:13.360 Output the total number of users who meet this criteria. 855 54:14.480 --> 54:21.320 Now, to determine the first user transaction for each user, use the transaction date field. 856 54:24.320 --> 54:29.560 And take into account scenario where a user had a multiple transactions on the same day. 857 54:30.280 --> 54:33.320 Use a specific function to handle these cases. 858 54:34.520 --> 54:38.080 So here they have given us the user transaction table. 859 54:38.840 --> 54:39.440 In this. 860 54:39.440 --> 54:47.640 If you see over here transaction ID user id spend means how many amount he has spent in transaction 861 54:47.640 --> 54:48.080 date. 862 54:49.960 --> 54:54.600 Now how can we find identify the user's first transaction. 863 54:54.800 --> 54:57.520 It is based upon the transaction date only right. 864 54:59.640 --> 55:02.320 Now if you see over here this table. 865 55:07.440 --> 55:09.200 These are the transaction date. 866 55:09.240 --> 55:10.930 Let me order the data. 867 55:12.330 --> 55:13.570 Order by user. 868 55:16.250 --> 55:16.810 ID. 869 55:20.690 --> 55:25.010 Okay, so what is the first transaction? 870 55:25.330 --> 55:28.050 Let me copy this data into Excel file. 871 55:36.370 --> 55:38.530 Okay so here. 872 55:41.210 --> 55:48.530 We need to identify those users who has done the first transaction which is $50 or more. 873 55:51.010 --> 55:53.570 Now which falls into the category. 874 55:53.570 --> 55:57.650 Can you tell me that which which users. 875 55:59.370 --> 56:04.050 So this one will not fall into the category. 876 56:04.050 --> 56:08.180 Now this one is also not fall into the category No. 877 56:09.620 --> 56:10.260 68. 878 56:10.900 --> 56:11.140 One. 879 56:11.180 --> 56:12.060 56. 880 56:12.100 --> 56:14.780 I think this one will come. 881 56:15.420 --> 56:17.260 Now again. 882 56:18.420 --> 56:20.220 This one will come. 883 56:22.620 --> 56:24.780 And then 180. 884 56:27.340 --> 56:28.740 These three are coming, right? 885 56:29.500 --> 56:31.060 So what will be our answer? 886 56:33.340 --> 56:33.660 Three. 887 56:33.700 --> 56:34.140 Right. 888 56:35.180 --> 56:35.980 One user. 889 56:35.980 --> 56:36.100 Two. 890 56:36.140 --> 56:36.820 User and three. 891 56:36.820 --> 56:37.180 User. 892 56:37.180 --> 56:37.500 Total. 893 56:37.500 --> 56:37.700 Three. 894 56:37.700 --> 56:41.540 Users are coming into that category. 895 56:43.580 --> 56:46.780 Now, how can we solve this query? 896 56:53.180 --> 56:54.020 Let me. 897 56:56.100 --> 57:00.660 Try to write all the column transaction ID. 898 57:03.060 --> 57:09.440 User ID Spend n transaction date. 899 57:10.160 --> 57:11.640 Now what we need to do. 900 57:24.400 --> 57:25.720 It is group by here. 901 57:28.120 --> 57:28.760 Okay. 902 57:28.800 --> 57:29.760 Group by. 903 57:30.680 --> 57:33.360 Better to use city only. 904 57:33.800 --> 57:36.360 Yeah, city only you can store that. 905 57:36.960 --> 57:41.040 Let me write row number over. 906 57:43.920 --> 57:44.880 Partition by. 907 57:46.920 --> 57:47.680 What we need to do. 908 57:47.720 --> 57:48.520 Partition by. 909 57:53.880 --> 57:57.600 The simple user id. 910 57:58.760 --> 57:59.680 User id. 911 58:01.880 --> 58:02.720 Order by. 912 58:05.370 --> 58:07.690 Order by transaction date. 913 58:07.930 --> 58:08.690 Transaction. 914 58:13.570 --> 58:16.770 Transaction date is. 915 58:16.810 --> 58:17.450 Ark. 916 58:18.290 --> 58:20.610 Now let me execute this one. 917 58:23.210 --> 58:23.810 Okay. 918 58:24.610 --> 58:27.610 Now I will remove this one. 919 58:31.410 --> 58:32.970 With city's. 920 58:35.330 --> 58:36.570 Now what we can do. 921 58:41.050 --> 58:41.210 This. 922 58:45.010 --> 58:46.130 Now what we can do. 923 58:53.090 --> 58:54.450 Where the where clause. 924 58:55.050 --> 58:55.490 Where? 925 58:55.490 --> 58:56.250 Perfect. 926 58:56.570 --> 58:57.010 Where? 927 59:00.250 --> 59:00.570 Yeah. 928 59:00.570 --> 59:00.890 Where. 929 59:01.740 --> 59:03.140 Spend spend, spend. 930 59:04.060 --> 59:05.420 Greater than or equal to 50. 931 59:06.140 --> 59:06.980 Spend. 932 59:07.020 --> 59:13.460 Greater than or equal to 50.00. 933 59:13.500 --> 59:13.900 Okay. 934 59:13.940 --> 59:14.300 And. 935 59:19.140 --> 59:22.300 Our case in our k. 936 59:24.980 --> 59:26.660 Equals one equal to one. 937 59:26.660 --> 59:27.180 Equal to one. 938 59:27.220 --> 59:27.620 Why? 939 59:27.660 --> 59:31.860 Because we need to identify users first transaction. 940 59:33.020 --> 59:37.060 First transaction should be greater than $50. 941 59:37.060 --> 59:37.460 Right. 942 59:38.060 --> 59:39.980 And here we cannot do descending. 943 59:39.980 --> 59:40.620 Right. 944 59:40.620 --> 59:46.140 Because if we do descending then it will show user last transaction. 945 59:48.620 --> 59:50.740 But we want first transaction right. 946 59:51.060 --> 59:53.700 Now let me try to execute this one. 947 59:56.460 --> 59:58.740 Three three are coming right now. 948 59:59.460 --> 01:00:00,140 Simple. 949 01:00:00,230 --> 01:00:01,550 Now what we have to do. 950 01:00:02,430 --> 01:00:02,950 Count. 951 01:00:04,190 --> 01:00:04,910 Perfect. 952 01:00:04,950 --> 01:00:16,550 Count of user ID is total users with first transaction greater than $50. 953 01:00:16,550 --> 01:00:16,990 Right? 954 01:00:17,430 --> 01:00:17,790 Yeah. 955 01:00:21,070 --> 01:00:27,430 So three three users that we have that only we have seen in our Excel file. 956 01:00:27,430 --> 01:00:27,870 Right. 957 01:00:28,550 --> 01:00:28,910 Yeah. 958 01:00:31,350 --> 01:00:33,310 So perfect right. 959 01:00:33,350 --> 01:00:34,910 Again we have used the city. 960 01:00:34,950 --> 01:00:36,910 City is widely used. 961 01:00:38,430 --> 01:00:40,750 City ranking functions joins. 962 01:00:42,030 --> 01:00:43,990 These are the widely used concepts. 963 01:00:46,910 --> 01:00:50,270 Now let me know if any doubt. 964 01:00:53,270 --> 01:00:54,070 I have a question. 965 01:00:54,070 --> 01:00:57,990 So when in interviews so are they going to give table details everything. 966 01:00:57,990 --> 01:01:00,330 Or we need to identify the table names. 967 01:01:00,330 --> 01:01:01,250 Column names. 968 01:01:03,290 --> 01:01:09,090 Yeah, actually in the interviews they will give the table mostly what they will give. 969 01:01:09,530 --> 01:01:11,010 They will give three four tables. 970 01:01:11,010 --> 01:01:11,250 Right. 971 01:01:11,290 --> 01:01:16,410 Three four tables in outside out of three four tables they will give ten queries. 972 01:01:18,530 --> 01:01:20,130 Ten different different queries. 973 01:01:20,490 --> 01:01:27,650 And then you have to resolve all these ten queries by using those three four tables. 974 01:01:30,210 --> 01:01:38,690 But you need to identify what kind of join you have to do among those four tables that you need to identify 975 01:01:38,730 --> 01:01:39,770 by yourself. 976 01:01:41,930 --> 01:01:42,450 Okay. 977 01:01:42,450 --> 01:01:42,970 Thank you. 978 01:01:44,330 --> 01:01:51,530 Actually that will also we will do after this phase two that we can consider as a project. 979 01:01:56,460 --> 01:01:59,620 Now let me know if you have any question. 980 01:02:06,020 --> 01:02:07,620 So actually I have one. 981 01:02:07,860 --> 01:02:10,300 Uh, so, uh, let's see. 982 01:02:10,340 --> 01:02:10,780 Let's say. 983 01:02:14,300 --> 01:02:14,580 Yeah. 984 01:02:14,580 --> 01:02:15,420 One by one. 985 01:02:15,460 --> 01:02:16,220 Uh, please. 986 01:02:19,460 --> 01:02:19,980 Yeah. 987 01:02:19,980 --> 01:02:20,820 You can ask. 988 01:02:22,140 --> 01:02:22,540 Okay. 989 01:02:24,380 --> 01:02:31,260 So actually, uh, so I recently started with my, uh, career in data analytics. 990 01:02:31,260 --> 01:02:34,020 So I had a question related to Star schema. 991 01:02:34,420 --> 01:02:40,180 So, uh, when we look up into the fact table, is it, uh, using is it done using inner join or left 992 01:02:40,180 --> 01:02:40,700 join. 993 01:02:41,460 --> 01:02:41,820 So. 994 01:02:44,060 --> 01:02:47,300 Actually there are, uh, dimension tables. 995 01:02:47,300 --> 01:02:47,780 Are there. 996 01:02:47,780 --> 01:02:48,900 In fact tables are there. 997 01:02:48,900 --> 01:02:49,340 Right. 998 01:02:49,820 --> 01:02:50,140 Yes. 999 01:02:50,140 --> 01:02:57,830 So all the dimension tables are Is directly connected with the fact table, right? 1000 01:02:58,510 --> 01:02:58,910 Yeah. 1001 01:02:59,030 --> 01:03:03,110 So in in in the all the dimension tables, primary key columns are there. 1002 01:03:03,550 --> 01:03:10,030 And in the fact table all the that dimension tables foreign key will be there in the fact table. 1003 01:03:10,630 --> 01:03:12,990 So we can directly connect. 1004 01:03:13,710 --> 01:03:17,550 So mostly in that case we can do inner join. 1005 01:03:17,590 --> 01:03:18,790 That is also fine. 1006 01:03:19,350 --> 01:03:22,070 But it is based upon the data. 1007 01:03:22,110 --> 01:03:23,310 We can consider. 1008 01:03:25,350 --> 01:03:25,750 Okay. 1009 01:03:26,190 --> 01:03:33,590 Because in the dimension table you all the primary key column has all the unique rows will be there 1010 01:03:33,590 --> 01:03:35,390 right in the dimension table. 1011 01:03:35,710 --> 01:03:43,030 But when you refer those, those uh, row into the fact table one, multiple rows will be there, right? 1012 01:03:44,430 --> 01:03:44,910 Yes. 1013 01:03:45,590 --> 01:03:46,190 Duplicate rows. 1014 01:03:46,190 --> 01:03:48,590 But in that case you can do inner join also. 1015 01:03:48,590 --> 01:03:50,110 That will be also fine. 1016 01:03:53,210 --> 01:03:54,130 So that's the thing. 1017 01:03:54,130 --> 01:03:58,410 Like in I was associated with the two different projects. 1018 01:03:58,530 --> 01:04:02,770 So in one they were using Leftjoin and in another they were using inner join. 1019 01:04:03,210 --> 01:04:05,770 So that was okay. 1020 01:04:08,730 --> 01:04:18,050 Actually you have to identify that, uh, you need to identify that, uh, your result sets should not 1021 01:04:18,050 --> 01:04:19,690 be a cross product. 1022 01:04:20,610 --> 01:04:21,290 Cross join. 1023 01:04:21,290 --> 01:04:21,690 Right. 1024 01:04:22,690 --> 01:04:23,010 Yes. 1025 01:04:23,050 --> 01:04:32,090 Because if there is in the, uh, main table, five rows are there in in the fact table, uh, those 1026 01:04:32,090 --> 01:04:40,290 five rows has multiple rows into the, those dimension table also in the fact table also. 1027 01:04:40,770 --> 01:04:42,570 Then it will become a cross product. 1028 01:04:42,570 --> 01:04:42,730 Right. 1029 01:04:42,770 --> 01:04:43,610 Cross join. 1030 01:04:44,290 --> 01:04:44,770 Yes. 1031 01:04:45,730 --> 01:04:53,540 So in the in that case inner join also will not work Properly, so you need to identify the data. 1032 01:04:54,220 --> 01:04:57,900 Then only you will decide which kind of join you have to use. 1033 01:04:59,660 --> 01:05:00,100 Yes. 1034 01:05:01,860 --> 01:05:02,420 Thank you. 1035 01:05:03,460 --> 01:05:04,060 Okay. 1036 01:05:06,100 --> 01:05:07,940 Now another person was there. 1037 01:05:07,980 --> 01:05:09,500 You can also ask. 1038 01:05:12,780 --> 01:05:17,260 How will you cover a performance tuning in your session? 1039 01:05:19,820 --> 01:05:22,420 Actually not now, not now. 1040 01:05:23,620 --> 01:05:26,900 Maybe after 1 or 2 months I will cover. 1041 01:05:29,700 --> 01:05:30,180 Because. 1042 01:05:30,340 --> 01:05:30,860 Ah, because. 1043 01:05:30,860 --> 01:05:38,660 Because in interview, mostly asked about the performance tuning, which is the most important thing? 1044 01:05:39,300 --> 01:05:39,900 Yes, yes. 1045 01:05:39,900 --> 01:05:48,590 But actually, uh, for freshers level or mid level experience, uh, two, three level experience people, 1046 01:05:49,230 --> 01:05:51,830 they will mostly ask the queries right first. 1047 01:05:53,550 --> 01:05:57,110 So, uh, actually this is our agenda. 1048 01:05:57,590 --> 01:06:04,790 We will cover around 50 plus queries, uh, in ten session, ten plus sessions. 1049 01:06:05,070 --> 01:06:08,990 And after that we will cover these projects. 1050 01:06:11,070 --> 01:06:11,830 These projects. 1051 01:06:11,830 --> 01:06:14,350 And after that, if, uh. 1052 01:06:16,710 --> 01:06:25,350 We have time, we will cover these performance tuning and all other advanced concepts we will cover. 1053 01:06:26,390 --> 01:06:29,310 But maybe next two month we will cover this thing. 1054 01:06:29,350 --> 01:06:34,870 Only SQL queries and projects, uh, projects in SQL. 1055 01:06:37,990 --> 01:06:40,550 Today we have covered this one right up to this. 1056 01:06:45,070 --> 01:06:49,560 Yeah, this query will help us a lot as well. 1057 01:06:50,960 --> 01:06:51,360 Ah, yes. 1058 01:06:51,360 --> 01:06:51,720 Yes. 1059 01:06:52,160 --> 01:06:55,840 Later on we will cover performance tuning when we finish this one. 1060 01:06:56,640 --> 01:06:57,440 These things. 1061 01:07:01,960 --> 01:07:02,400 Okay. 1062 01:07:03,400 --> 01:07:10,680 We will cover around 50 to 70 SQL queries which is asked in different companies. 1063 01:07:16,000 --> 01:07:18,840 Now let me know if any question. 1064 01:07:20,200 --> 01:07:21,840 Are you guys available tomorrow. 1065 01:07:22,280 --> 01:07:22,720 Tomorrow. 1066 01:07:22,720 --> 01:07:25,480 Can we take class or next Saturday? 1067 01:07:32,960 --> 01:07:33,360 Okay. 1068 01:07:33,840 --> 01:07:35,240 I guess next Thursday would be good. 1069 01:07:38,040 --> 01:07:38,800 Okay, fine. 1070 01:07:38,800 --> 01:07:41,600 Then we will meet directly on next Saturday. 1071 01:07:41,640 --> 01:07:42,240 Sunday? 1072 01:07:44,000 --> 01:07:49,540 I will share this script, uh, through Google Drive and share the link in WhatsApp group. 1073 01:07:49,540 --> 01:07:57,260 And also I will upload this video into my YouTube channel in new playlist. 1074 01:07:57,260 --> 01:08:06,020 I will create new playlist totally and will upload into that and you will cover from that. 1075 01:08:06,020 --> 01:08:15,580 Please support my YouTube channel and share with your with your friends and colleagues and people all 1076 01:08:15,580 --> 01:08:16,140 you know. 1077 01:08:19,380 --> 01:08:20,020 Sure. 1078 01:08:20,940 --> 01:08:25,140 Okay, now if there is no question then we will wind up for today. 1079 01:08:30,700 --> 01:08:31,420 Thank you. 1080 01:08:31,420 --> 01:08:32,100 Thank you sir. 1081 01:08:32,340 --> 01:08:32,780 Okay. 1082 01:08:32,780 --> 01:08:34,500 Thank you so much everyone. 1083 01:08:34,740 --> 01:08:36,500 We will meet again next Saturday. 1084 01:08:38,860 --> 01:08:39,700 Thank you all. 1085 01:08:39,740 --> 01:08:40,820 Have a nice day.