1 00:01.080 --> 00:02.480 So good evening, everyone. 2 00:03.480 --> 00:06.800 Let's, uh, start with the first query of the day. 3 00:09.280 --> 00:11.760 So this is the description has been given. 4 00:12.200 --> 00:20.720 CVS health is analyzing its pharmacy sales data and how well different products are selling in the market. 5 00:21.800 --> 00:26.720 Each drug is exclusively manufactured by a single manufacturer. 6 00:28.600 --> 00:36.600 So write a query to identify the manufacturers associated with the drugs that resulted in losses for 7 00:36.600 --> 00:41.440 severe health, and calculate the total amount of losses incurred. 8 00:43.520 --> 00:51.080 After the manufacturers name the number of drugs associated with the losses in total losses in absolute 9 00:51.080 --> 00:51.640 value. 10 00:53.680 --> 00:59.800 Display the results sorted in descending order, with the highest losses displayed at the top. 11 01:00.640 --> 01:03.840 So here they have given us the table pharmacy sales table. 12 01:04.400 --> 01:07.400 In that product ID unit sold total sales. 13 01:09.080 --> 01:12.280 Uh, this is Cogs means cost of goods. 14 01:12.280 --> 01:12.840 Sales. 15 01:13.360 --> 01:16.440 Cost of goods. 16 01:16.440 --> 01:17.000 Sells. 17 01:17.400 --> 01:18.720 Manufacturer drug. 18 01:18.760 --> 01:19.680 Total loss. 19 01:20.160 --> 01:21.320 Total profit. 20 01:22.880 --> 01:28.360 Actually, these three columns are null column only, so it is not required. 21 01:30.520 --> 01:32.600 There is no value in this three column. 22 01:34.480 --> 01:40.520 And if as you can see, I have already inserted the data into the pharmacy sales table. 23 01:43.560 --> 01:48.520 So I will mail you the scripts and everything at the end of the session. 24 01:56.960 --> 01:57.600 So. 25 02:00.520 --> 02:01.600 This is the table. 26 02:03.840 --> 02:09.640 Here we need to find the drugs that resulted in the losses for CVS health. 27 02:10.800 --> 02:11.280 Right. 28 02:11.800 --> 02:12.880 So how? 29 02:13.560 --> 02:17.280 So here we need to identify how can we consider as a loss. 30 02:18.160 --> 02:21.920 So total sales minus cost of goods sales. 31 02:22.560 --> 02:29.680 And when this value are less than zero then we can consider it as a loss. 32 02:30.360 --> 02:30.880 So. 33 02:33.680 --> 02:35.720 Let me show you the same thing. 34 02:41.720 --> 02:46.320 If you see over here I have already written these are our table. 35 02:46.360 --> 02:46.720 Right. 36 02:47.280 --> 02:48.720 Pharmacy sales table. 37 02:49.120 --> 02:53.880 So manufacturer total sales minus cost of goods. 38 02:53.880 --> 02:58.440 Sells Cox from pharmacy shelves. 39 02:58.760 --> 03:06.720 And when this value total sales minus good sells less than zero, it means it is negative, right. 40 03:07.560 --> 03:09.760 And that is considered as a losses. 41 03:10.040 --> 03:17.800 So let me execute this statement and copy this data into the Excel file. 42 03:26.000 --> 03:26.360 So. 43 03:32.000 --> 03:33.680 So if you see over here. 44 03:37.560 --> 03:39.360 These are the two manufacturer. 45 03:40.360 --> 03:42.680 These are the value of total loss. 46 03:43.080 --> 03:44.920 Total sales minus good sales. 47 03:45.400 --> 03:48.120 And these are the two drugs are. 48 03:50.200 --> 03:56.640 Two different drugs are Available for this ABV manufacturer. 49 03:57.120 --> 03:59.520 Then if you see for our Johnson and Johnson. 50 04:00.200 --> 04:01.240 Johnson and Johnson. 51 04:01.240 --> 04:01.680 Right. 52 04:02.920 --> 04:04.520 So this Johnson and Johnson. 53 04:04.520 --> 04:05.000 Total. 54 04:06.240 --> 04:08.120 Six drugs are available. 55 04:09.040 --> 04:14.280 Total six drugs are available in the losses incurred for Johnson and Johnson. 56 04:14.680 --> 04:16.000 And what is the total. 57 04:16.840 --> 04:21.240 So now understand the question again. 58 04:23.000 --> 04:24.920 Output the manufacturer name. 59 04:25.320 --> 04:31.560 So we require manufacturer name the number of drugs the number of drugs associated with the losses. 60 04:32.440 --> 04:32.920 Right. 61 04:33.040 --> 04:38.280 So for Johnson and Johnson, how many number of drugs associated with the losses. 62 04:38.840 --> 04:44.920 We can say that six drugs six drugs are associated with the losses. 63 04:45.520 --> 04:51.560 Then for any how many drugs are associated with the losses. 64 04:54.720 --> 04:57.680 I think for for drugs are available with the losses. 65 04:57.680 --> 04:58.080 Right. 66 05:01.240 --> 05:03.520 For drugs are available with the losses. 67 05:05.120 --> 05:15.000 So now for this number of drugs associated with the losses we need to use count of drugs count aggregate 68 05:15.000 --> 05:15.480 function. 69 05:15.480 --> 05:15.880 Right. 70 05:16.240 --> 05:19.560 And then total losses in absolute value. 71 05:20.680 --> 05:27.560 So over here we need to do the sum of all these values. 72 05:27.560 --> 05:28.240 And then. 73 05:30.400 --> 05:37.320 If you see over here some of these values are 894913.13. 74 05:37.320 --> 05:37.760 Right. 75 05:38.200 --> 05:40.400 And it is giving us in minus. 76 05:41.200 --> 05:49.520 So absolute we need to do we need to eliminate the minus sign by using the absolute function abs function 77 05:50.200 --> 05:51.200 to eliminate. 78 05:56.240 --> 05:57.040 Minus sign. 79 05:57.040 --> 05:57.480 Right. 80 05:58.320 --> 06:02.320 So now again let's jump to the solution. 81 06:06.720 --> 06:07.760 We require. 82 06:10.400 --> 06:11.600 Manufacturer. 83 06:12.760 --> 06:18.040 Then count of drugs associated with each of the manufacturer. 84 06:18.640 --> 06:23.640 And then total losses occurred for each manufacturer. 85 06:24.040 --> 06:26.840 So now if you see over here. 86 06:30.280 --> 06:35.000 What I have done I have used the count aggregate function. 87 06:38.040 --> 06:45.320 To count the total drugs associated with each menu. 88 06:46.760 --> 06:47.400 Lecturer. 89 06:47.400 --> 06:47.800 Right. 90 06:49.400 --> 06:54.680 And then we are doing the sum sum of total sales minus cogs. 91 06:55.400 --> 06:58.040 So so total sales minus cogs. 92 06:59.200 --> 07:06.400 And when this value total sales minus Cogs less than zero then it is associated with the losses. 93 07:06.440 --> 07:06.880 Right. 94 07:07.760 --> 07:18.480 So we need to we need to identify only that manufacturer that has occurred the losses. 95 07:19.880 --> 07:20.440 Right. 96 07:20.680 --> 07:26.200 So that we need to filter over here with total sales minus Cogs less than zero. 97 07:26.840 --> 07:31.520 And because of we have used the count in some aggregate function. 98 07:36.840 --> 07:39.600 Because we have used the count and sum aggregate function. 99 07:41.640 --> 07:43.880 We need to do group by manufacturer. 100 07:43.880 --> 07:44.320 Right. 101 07:45.280 --> 07:46.800 And now what we have done. 102 07:46.840 --> 07:50.880 Count of drug and then sum of total sales minus cogs. 103 07:51.320 --> 07:52.280 And then if I. 104 07:52.280 --> 07:57.280 I am not using over here absolute value then what it is giving. 105 07:57.320 --> 07:58.520 Let me show you. 106 08:02.720 --> 08:04.880 It is giving the data into the minus. 107 08:09.160 --> 08:14.040 But now we require positive side only right? 108 08:15.080 --> 08:18.760 So that what we can do we need to use the absolute value. 109 08:25.600 --> 08:36.000 So if we use the absolute value then the losses are occurred in the positive not in the minus sign. 110 08:36.200 --> 08:41.320 So if you see over here this result set and compare with our Excel data. 111 08:46.040 --> 08:48.640 So if you see this Johnson and Johnson right. 112 08:48.640 --> 08:53.560 So Johnson and Johnson is total amount of drugs are six. 113 08:53.800 --> 08:56.160 Six drugs are associated with the losses. 114 08:56.560 --> 09:01.760 And then what is the sum of this total sales minus cost. 115 09:02.040 --> 09:08.280 So that is 894894913.13. 116 09:08.280 --> 09:11.520 So that same amount you can see over here right. 117 09:21.200 --> 09:22.960 Please give me one minute guys. 118 09:25.280 --> 09:25.880 Okay. 119 09:27.040 --> 09:31.400 So if you see uh then we can compare with the elderly right. 120 09:31.680 --> 09:38.920 So elderly is total for four drugs are associated with the losses in the sum of these four amount will 121 09:38.920 --> 09:39.280 be 122 09:41.680 --> 09:43.560 5252.9. 123 09:44.120 --> 09:45.520 So that is for Jelly Belly. 124 09:46.160 --> 09:51.360 So this is our final final answer. 125 09:51.360 --> 09:56.360 Select manufacturer count of drug then sum of total sales minus quotes. 126 09:56.960 --> 09:59.640 We have converted this into the absolute value. 127 10:00.000 --> 10:05.400 And then we have added the filter condition to filter only losses. 128 10:09.040 --> 10:09.760 Values. 129 10:09.800 --> 10:10.120 Right. 130 10:10.120 --> 10:13.680 Because we want only negative values not all the values. 131 10:14.880 --> 10:17.960 So this solution is clear. 132 10:17.960 --> 10:24.440 Everyone you can write in the chat box or you can speak if you have any doubt. 133 10:36.560 --> 10:41.520 Okay so actually what is the requirement of over here. 134 10:41.840 --> 10:42.680 We need to. 135 10:46.120 --> 10:50.120 We need to identify the data based upon the each of the manufacturer. 136 10:50.200 --> 10:50.680 Right. 137 10:51.200 --> 10:52.840 Each of the manufacturer. 138 10:53.120 --> 10:55.000 So if you see in the table. 139 11:00.440 --> 11:11.000 If you see in in this table, let me show you this one only here there are multiple multiple rows are 140 11:11.000 --> 11:15.440 given according based on all the manufacturer. 141 11:15.480 --> 11:15.880 Right. 142 11:16.520 --> 11:25.720 So we need to group by manufacturer only because we want only single record for each of the manufacturer. 143 11:26.680 --> 11:30.520 We want the count of the drug and sum of total sales. 144 11:36.920 --> 11:37.760 Total. 145 11:37.800 --> 11:45.280 Total sales, which is occurred in the negative means total sales minus folks value we require, which 146 11:45.280 --> 11:52.880 is also giving the less than zero so that we need only one value, so that we have done the group by 147 11:52.920 --> 11:53.920 manufacturer. 148 11:56.920 --> 11:57.680 Over here. 149 12:01.040 --> 12:05.520 Because we are using count and sum aggregate function, we need to use group by. 150 12:08.160 --> 12:08.960 Group by clause. 151 12:08.960 --> 12:09.360 Right. 152 12:17.120 --> 12:17.680 Yeah. 153 12:21.280 --> 12:22.080 One minute. 154 12:26.640 --> 12:29.120 So this solution is clear right everyone. 155 12:36.960 --> 12:37.160 Yeah. 156 12:37.160 --> 12:39.200 If anyone wants to speak, they can speak. 157 12:49.200 --> 12:50.240 Otherwise we will move. 158 12:50.240 --> 12:54.160 Move to the second question. 159 12:58.200 --> 12:58.840 Oh, clear. 160 12:59.880 --> 13:00.600 Okay, okay. 161 13:02.800 --> 13:05.520 So let's move to the second question. 162 13:05.680 --> 13:10.360 Query again, it is based on the CVS health data. 163 13:10.360 --> 13:17.400 Only CVS health is trying to better understand its pharmacy sales and how well different products are 164 13:17.400 --> 13:17.960 selling. 165 13:18.880 --> 13:22.240 Each drug can only be produced by one manufacturer. 166 13:22.920 --> 13:27.440 Write a query to find the top three most profitable drugs sold. 167 13:29.880 --> 13:36.760 Write a query to find top three most profitable drugs sold and how much profit they made. 168 13:37.720 --> 13:40.560 Assume that there are no ties in the profits. 169 13:41.560 --> 13:45.120 Display the result from the highest to the lowest total profit. 170 13:46.080 --> 13:52.520 So actually, when you read this one, guys assume that there are no ties in the profits. 171 13:53.440 --> 14:02.400 So in that case, if we use the rank function and dense rank function, both will be giving the same 172 14:02.440 --> 14:03.080 output. 173 14:04.720 --> 14:06.520 Dense rank and rank. 174 14:06.720 --> 14:11.560 Both will be giving the same output. 175 14:11.800 --> 14:12.200 Why? 176 14:12.800 --> 14:16.400 Because there is no ties in the profit. 177 14:17.440 --> 14:24.040 Because of that reason, only if there is a tie in the profit, then again rank and dense rank will 178 14:24.040 --> 14:25.440 function differently. 179 14:26.320 --> 14:29.320 So now we can use any of this function. 180 14:29.880 --> 14:33.320 Now display the result from the highest to the lowest total profit. 181 14:37.640 --> 14:44.680 Now stands for cost of goods sold, which is the direct cost associated with the products producing 182 14:44.680 --> 14:45.360 the drug. 183 14:46.120 --> 14:51.520 So total profit equal to total sales minus that we have seen in the last example. 184 14:51.560 --> 14:51.960 Right. 185 14:52.400 --> 14:59.200 So again the same table in this same data that what we have inserted over here. 186 15:00.160 --> 15:01.840 Now what is our requirement. 187 15:03.440 --> 15:06.760 We need to identify the top three most profitable drugs. 188 15:07.600 --> 15:08.280 So now. 189 15:11.840 --> 15:13.720 Let me see the data again. 190 15:18.960 --> 15:23.040 Now here we need to we can use any of the ranking function. 191 15:24.040 --> 15:24.480 So. 192 15:27.520 --> 15:31.040 Let me try to use the Denserank function. 193 15:33.240 --> 15:34.120 So select. 194 15:36.280 --> 15:39.960 Now profitable drugs we require right. 195 15:39.960 --> 15:43.440 So we need to mention the drug. 196 15:43.960 --> 15:46.880 Then we need to use the Denserank function. 197 15:49.240 --> 15:57.200 Over order by total sales minus cogs. 198 15:58.400 --> 15:59.440 And we need to. 199 16:01.880 --> 16:10.760 We need to see that the result in the ascending descending order means we need highest profit will come 200 16:10.800 --> 16:15.360 at the first, then second highest profit will come on the second like that. 201 16:15.800 --> 16:18.520 So that we need to do the over over here. 202 16:18.520 --> 16:23.640 We need to use descending and then we can give arc as alias. 203 16:27.000 --> 16:30.760 Then we can use either subquery or we can use the city. 204 16:31.880 --> 16:37.000 So let me try to use the city. 205 16:46.760 --> 16:49.000 Now what is our requirement. 206 16:49.000 --> 16:49.680 We want. 207 16:55.760 --> 16:56.560 Top three right. 208 16:56.600 --> 16:57.640 We want top three. 209 17:03.200 --> 17:07.680 Top three most profitable drugs sold and how much profit they made. 210 17:13.480 --> 17:13.880 So. 211 17:19.040 --> 17:21.320 We need to mention this column as well. 212 17:22.160 --> 17:24.800 And as we can give the alias to. 213 17:27.480 --> 17:28.240 Profit. 214 17:28.720 --> 17:31.080 Now let me run this query. 215 17:38.080 --> 17:40.400 Now our K column is not required right. 216 17:40.440 --> 17:43.440 We require drug and profit column only. 217 17:53.120 --> 17:54.960 So this is the result right. 218 17:55.600 --> 17:57.720 Now let me copy this result over here. 219 18:00.120 --> 18:05.160 So these are the top top three most profitable uh goods sold. 220 18:05.440 --> 18:07.560 So let me now write. 221 18:14.360 --> 18:19.920 Let me now execute this query which is written with the row number function. 222 18:22.200 --> 18:26.760 So if you see over here that is also giving the same result, right? 223 18:28.520 --> 18:37.560 And now if you use the rank function instead of dense rank, then also it is giving the same result. 224 18:37.560 --> 18:37.960 Why? 225 18:38.320 --> 18:43.600 Because there is no ties as it is mentioned in the. 226 18:46.040 --> 18:47.040 Question only right? 227 18:47.040 --> 18:48.880 There is no ties over here. 228 18:48.880 --> 18:52.880 So while using the rank function it is also giving the same result. 229 18:54.280 --> 18:59.680 So when there is a no tie and drinks are functioning the same way. 230 19:05.880 --> 19:09.680 So this this question is clear guys. 231 19:10.720 --> 19:13.440 Let me know if you have any doubt in this query. 232 19:15.760 --> 19:18.720 You can write in the chat box or you can speak. 233 19:25.200 --> 19:28.040 Here we are finding top three most profitable drugs. 234 19:34.400 --> 19:34.680 So. 235 19:41.920 --> 19:44.480 Let me show you over here again. 236 19:46.960 --> 19:47.440 Where? 237 19:47.480 --> 19:48.120 Total. 238 19:54.320 --> 19:57.840 Sales minus costs. 239 20:02.560 --> 20:04.120 Actually, we need to do order by. 240 20:04.120 --> 20:04.680 Let me. 241 20:08.200 --> 20:10.000 Can we do order by like this? 242 20:12.640 --> 20:13.240 Oh, sorry. 243 20:13.840 --> 20:14.400 By. 244 20:17.080 --> 20:19.960 And then Descending. 245 20:31.200 --> 20:37.080 And if I mention all this over in the select part. 246 20:39.920 --> 20:41.920 Then it is giving the same result, right? 247 20:42.400 --> 20:45.720 Which we, we we we are having over here. 248 20:47.040 --> 20:55.160 If you see this first three record set are the top three most profitable drugs across entire table. 249 20:57.560 --> 21:00.840 Now, now everything is clear in this question. 250 21:07.120 --> 21:08.480 This is the easiest part. 251 21:08.520 --> 21:11.600 We can understand this. 252 21:15.720 --> 21:17.240 Let me know if you have any doubt. 253 21:17.280 --> 21:18.200 Otherwise we will. 254 21:20.720 --> 21:21.280 Okay. 255 21:22.760 --> 21:25.600 Now we will move to the next question. 256 21:38.520 --> 21:39.280 One minute. 257 21:49.360 --> 21:52.000 That only I have explained right over here. 258 21:53.440 --> 21:54.480 Code part only. 259 21:56.480 --> 21:58.720 These are the different ranking functions. 260 21:58.720 --> 21:59.280 Row number. 261 21:59.280 --> 22:01.200 Rank dense rank. 262 22:02.000 --> 22:08.480 So whenever we want to find the top three or any kind of the highest, second, highest, third, highest, 263 22:09.120 --> 22:16.720 fifth, highest or top four or top five at that time, we are we can use different ranking function. 264 22:17.320 --> 22:24.880 So row number, rank, dense rank and tile are different, ranking functions are there and all are functioning 265 22:25.600 --> 22:26.440 differently. 266 22:26.720 --> 22:32.600 So I have already made the video on that and I have uploaded that video on my YouTube channel. 267 22:33.760 --> 22:36.600 So for that understanding you can see that video. 268 22:39.640 --> 22:48.200 So that that concept only we are using over here to identify the top three highest selling. 269 22:50.600 --> 22:56.800 Products by manufacturer by sorry top three highest drugs. 270 22:57.400 --> 22:58.560 Profitable drugs. 271 23:01.200 --> 23:03.760 Now let me jump to the next next question. 272 23:04.800 --> 23:07.440 So again this is for CVS health. 273 23:07.440 --> 23:14.960 Only CVS health want to gain a clear understanding of his pharmacy sales and the performance of various 274 23:14.960 --> 23:15.320 products. 275 23:15.360 --> 23:20.400 Write a query to calculate the total sales for each manufacturer. 276 23:20.760 --> 23:24.160 Total drug sales for each manufacturer. 277 23:24.200 --> 23:32.880 So whenever you find this kind of word, like each manufacturer or each product category or each product 278 23:32.880 --> 23:36.440 subcategory, then you have to understand. 279 23:41.960 --> 23:49.880 One thing either either you have to use the group by clause one minute. 280 23:55.200 --> 24:06.080 Whenever you heard this word in the any of the query, then either you have to use the group by clause. 281 24:08.920 --> 24:13.200 Or you have to use any. 282 24:15.480 --> 24:16.720 Of the ranking. 283 24:19.040 --> 24:20.080 Functions. 284 24:22.400 --> 24:26.240 With partition by clause. 285 24:28.480 --> 24:31.160 These two options will be available right now. 286 24:31.160 --> 24:32.360 What is our requirement? 287 24:32.800 --> 24:36.880 Write a query to calculate the total drug sales for each manufacturer. 288 24:38.560 --> 24:45.840 Now round the answer to the nearest million and report your result in descending order of total sales. 289 24:46.640 --> 24:49.040 So highest total sales will come first. 290 24:49.480 --> 24:52.840 Then second highest sell will come second like that. 291 24:53.400 --> 24:59.400 In case of any duplicates, sort them alphabetically by the manufacturer name. 292 25:01.080 --> 25:07.160 And since this data will be displayed on a dashboard, you should buy a business stakeholder. 293 25:07.760 --> 25:10.400 Please format your result as follows. 294 25:11.120 --> 25:15.480 Like 36,000,100 million. 295 25:15.520 --> 25:16.240 Like that. 296 25:18.720 --> 25:22.360 So this is again the same table. 297 25:22.920 --> 25:32.120 Now, this is the one thing I have taken into consideration while writing my solution to convert a number 298 25:32.600 --> 25:35.400 in rupees to million, follow the below process. 299 25:36.560 --> 25:39.400 We need to divide by. 300 25:43.080 --> 25:43.800 Ten lakh. 301 25:43.840 --> 25:44.960 Divide by ten lakh. 302 25:45.920 --> 25:46.840 Ten lakh rupees. 303 25:46.840 --> 25:48.160 Equal to 1 million. 304 25:48.960 --> 25:49.360 Right? 305 25:49.880 --> 25:51.560 So if you see over here. 306 25:55.040 --> 25:58.160 We need to identify total sales by manufacturer. 307 25:58.160 --> 25:58.560 Right? 308 26:00.280 --> 26:05.480 Because we want to find for each manufacturer total sales. 309 26:05.600 --> 26:08.720 So definitely you have to use group by function. 310 26:08.720 --> 26:10.520 Now, if you see over here. 311 26:13.480 --> 26:19.960 These results are coming in like like it is coming like 111 lakhs. 312 26:19.960 --> 26:25.680 Or you can say that 113 or like one crore or something. 313 26:25.720 --> 26:26.240 Right. 314 26:26.440 --> 26:28.320 But we want the data into the. 315 26:30.800 --> 26:31.560 Millions. 316 26:31.560 --> 26:32.000 Right. 317 26:32.680 --> 26:36.560 So that we don't want all this thing. 318 26:37.000 --> 26:47.240 We want like one one, 3.77 or 78 like that, then 32 million, then 33 million like that. 319 26:47.240 --> 26:48.720 We want result. 320 26:51.000 --> 26:53.040 So now if you see over here. 321 26:57.000 --> 26:57.640 Let me. 322 27:00.000 --> 27:02.200 Write this this query in. 323 27:04.800 --> 27:06.320 Small small parts. 324 27:17.800 --> 27:19.200 Pharmacy sales. 325 27:24.280 --> 27:25.640 Now what is our requirement? 326 27:25.640 --> 27:29.880 We need to do manufacturer in total. 327 27:32.720 --> 27:33.640 Sales we require. 328 27:33.640 --> 27:34.040 Right. 329 27:35.840 --> 27:38.360 So we need to do some of total sales. 330 27:40.600 --> 27:42.320 That we have already done above. 331 27:45.160 --> 27:47.200 Now let me just execute this query. 332 27:48.280 --> 27:48.600 Sorry. 333 27:48.600 --> 27:53.000 We need to do group by manufacturer. 334 27:56.120 --> 27:57.880 Now this result is coming right. 335 27:58.520 --> 28:02.240 Now what we need to do we need to divide this value by. 336 28:04.440 --> 28:05.200 Ten lakhs. 337 28:05.480 --> 28:06.320 So that. 338 28:13.360 --> 28:20.480 So that we got answer in like 113113.777. 339 28:22.480 --> 28:31.560 So now we do not need all this uh, after point, we do not need all this digits. 340 28:31.600 --> 28:32.040 Right. 341 28:32.680 --> 28:36.520 So that I have done the rounding over here. 342 28:37.240 --> 28:37.720 So. 343 28:42.160 --> 28:43.760 Let me do the round first. 344 28:48.800 --> 28:50.120 Now what it is giving. 345 28:50.120 --> 28:54.200 Let me check that one okay. 346 28:56.520 --> 28:58.520 It is rounding in. 347 28:58.520 --> 29:06.080 It is giving the it is converting all to the 0000 like that means it is. 348 29:09.040 --> 29:10.160 You can say it is. 349 29:10.200 --> 29:12.600 A rounding means 113.77. 350 29:12.600 --> 29:17.720 So it is converting to 114, right? 351 29:18.560 --> 29:21.040 Now what is our requirement. 352 29:22.320 --> 29:24.080 We need we need. 353 29:27.440 --> 29:29.520 We need to do the casting. 354 29:30.200 --> 29:33.640 Casting we need only after point. 355 29:33.680 --> 29:36.560 Decimal after point. 356 29:36.600 --> 29:38.400 We do not require that values. 357 29:38.840 --> 29:41.320 So let me do that over here. 358 29:48.120 --> 29:50.040 Firstly we have done round. 359 29:50.400 --> 29:52.520 Then we have used the cast function. 360 29:53.240 --> 29:59.720 Then in the cast function we are casting to decimal to five comma zero. 361 30:00.440 --> 30:04.680 So it means after point we do not require any digits. 362 30:04.720 --> 30:06.560 So let me select this data. 363 30:08.560 --> 30:14.320 So if you see over here it is like 114 32 3470 right. 364 30:15.400 --> 30:16.960 Now what is our requirement. 365 30:17.760 --> 30:21.080 We want we want to show this data into the dashboard. 366 30:21.760 --> 30:24.800 And we want dollar and million also. 367 30:25.880 --> 30:28.600 So now only one thing is remaining. 368 30:28.600 --> 30:30.000 We need to do concrete. 369 30:34.000 --> 30:36.120 Concrete function will concatenate. 370 30:36.120 --> 30:37.040 Concatenate. 371 30:39.000 --> 30:42.920 I have a question here why we have used as decimal five comma zero. 372 30:46.080 --> 30:46.680 Okay. 373 30:47.040 --> 30:50.760 Because okay one minute I show you. 374 30:53.320 --> 30:58.200 If you see the after manufacturer there is one column is there. 375 30:58.240 --> 30:58.760 right? 376 30:58.800 --> 31:03.160 So in that column we have not used the cost cost function. 377 31:03.160 --> 31:11.360 So because of we are not doing the cost it is giving all the point all the digits after point. 378 31:11.360 --> 31:11.840 Right. 379 31:12.880 --> 31:21.440 But that digits are not required to show into the dashboard so that we are doing the cast into the decimal. 380 31:21.800 --> 31:31.960 And for that element eliminating this after point, which is we are written over here zero, because 381 31:32.000 --> 31:34.480 we do not require anything after point. 382 31:35.440 --> 31:40.480 If we require two digit at that time, we have to do two like that. 383 31:40.480 --> 31:43.920 If we require one digit, then we will do one. 384 31:44.600 --> 31:49.720 But we do not require any any digit after point like 0000. 385 31:49.760 --> 31:50.320 That's zero. 386 31:50.360 --> 31:55.040 We do not require so that we are doing casting to zero. 387 31:56.290 --> 31:58.130 So what is the role of five here? 388 31:58.130 --> 31:59.650 I just wanted to check that one. 389 31:59.690 --> 32:00.090 Okay. 390 32:00.130 --> 32:05.130 Role of five means five means before digit. 391 32:06.890 --> 32:08.050 What is there? 392 32:08.450 --> 32:09.810 Let me copy this. 393 32:10.170 --> 32:13.250 So role of five over here is before. 394 32:13.410 --> 32:15.490 It is indicating before point. 395 32:16.930 --> 32:19.890 And this zero is indicating after point. 396 32:20.410 --> 32:31.970 So now suppose if this value is like like this right then five will not work because these are more 397 32:31.970 --> 32:34.970 than five digit number right. 398 32:35.010 --> 32:36.570 More than five digit number. 399 32:37.090 --> 32:37.450 Got it. 400 32:37.450 --> 32:37.890 Got it. 401 32:37.890 --> 32:46.930 So will be taking means if there is 4332 then it will take that is it means pre pre decimal points right. 402 32:48.810 --> 32:49.210 Uh sorry. 403 32:49.210 --> 32:50.090 Can you repeat. 404 32:50.770 --> 32:50.930 Yeah. 405 32:50.970 --> 32:57.290 These are for you know if you are indexing it like five comma, zero comma, zero it will be means it 406 32:57.290 --> 32:59.930 will be allowing only four numbers. 407 33:00.090 --> 33:00.690 Yes, yes. 408 33:00.730 --> 33:01.370 Decimal. 409 33:01.410 --> 33:02.730 Otherwise it will truncate the. 410 33:02.770 --> 33:04.050 That's the yes. 411 33:04.050 --> 33:04.250 Yes. 412 33:04.250 --> 33:04.490 Right. 413 33:04.490 --> 33:05.450 Yes yes yes. 414 33:05.490 --> 33:05.810 Got it. 415 33:05.810 --> 33:06.370 Thank you. 416 33:06.730 --> 33:07.370 Okay okay. 417 33:10.610 --> 33:15.090 So now final uh, final thing is remaining is to concatenate over here. 418 33:15.730 --> 33:21.450 So okay so over here we need to use the concat function. 419 33:21.690 --> 33:26.130 And then in the concrete we can give any number of parameters. 420 33:26.130 --> 33:26.570 Right. 421 33:27.010 --> 33:30.890 So what is my first parameter requirement is dollar. 422 33:32.450 --> 33:34.250 So this is my first parameter. 423 33:34.690 --> 33:37.770 Then this entire thing will be my second parameter. 424 33:39.210 --> 33:45.370 And then I want million also after uh digit right one one 3 million. 425 33:45.970 --> 33:47.690 So that I have done. 426 33:51.090 --> 33:51.690 Million. 427 33:51.690 --> 33:55.970 So these three parameter is concrete is using this is first one. 428 33:55.970 --> 33:58.610 This is second one and this is third one. 429 33:59.250 --> 34:02.690 And then we can give as alias. 430 34:05.530 --> 34:07.370 Now let me execute this one. 431 34:11.690 --> 34:17.130 So if you see over here one one $4 million then $32 million. 432 34:17.810 --> 34:19.970 Now only one thing is pending. 433 34:21.770 --> 34:25.130 That is we need to do order by right. 434 34:25.170 --> 34:29.410 Because what it is saying over here. 435 34:35.250 --> 34:37.410 In descending order of total sales. 436 34:38.090 --> 34:46.370 So we need highest value at the first place, then second highest value at the second place like that. 437 34:48.050 --> 34:49.970 So we can do order by. 438 34:54.010 --> 34:55.010 Total sales. 439 34:56.410 --> 34:59.450 And we can use descending. 440 35:01.210 --> 35:04.850 So now highest sales will become first. 441 35:04.850 --> 35:05.410 Then second. 442 35:05.410 --> 35:06.810 Highest will become second. 443 35:06.810 --> 35:10.090 Third will become third place. 444 35:15.570 --> 35:18.090 So now this query will be cleared. 445 35:18.130 --> 35:18.650 Guys. 446 35:25.810 --> 35:27.970 Let me know if you have any doubt. 447 35:30.290 --> 35:34.050 Otherwise we will jump to the next problem. 448 35:41.290 --> 35:42.850 And understand the query. 449 35:42.850 --> 35:48.010 Understand the requirement and then start writing the query. 450 35:49.370 --> 35:50.930 step by step only. 451 35:53.770 --> 35:57.410 Never try to write entire query in single step. 452 36:06.650 --> 36:07.090 Clear. 453 36:07.090 --> 36:07.410 Right. 454 36:07.410 --> 36:09.490 So we will jump to the next. 455 36:13.690 --> 36:14.450 Question. 456 36:21.250 --> 36:23.850 So again this is for CVS health only. 457 36:26.290 --> 36:30.410 Now how will you write a query to find the top two drugs sold. 458 36:31.450 --> 36:37.810 Write a query to find the top two drugs sold in terms of units sold for each manufacturer. 459 36:39.290 --> 36:40.010 So here. 460 36:42.450 --> 36:44.570 We will use the partition by clause. 461 36:47.130 --> 36:47.850 And what? 462 36:48.050 --> 36:51.250 By which way we will do the partition by we will. 463 36:51.610 --> 36:54.770 As I have already said in the last query, write. 464 36:55.370 --> 37:03.050 Whenever you find this kind of keyword for each manufacturer, or for each product category, or for 465 37:03.050 --> 37:07.930 each product subcategory at that time, we are either using group by. 466 37:11.130 --> 37:14.930 Or partition by each manufacturer. 467 37:14.970 --> 37:17.050 So partition by manufacturer. 468 37:19.650 --> 37:20.330 Like that. 469 37:20.850 --> 37:28.850 So query to find the top two drugs sold in terms of units sold for each manufacturer, list your result 470 37:28.850 --> 37:32.050 in alphabetical order by manufacturer. 471 37:32.770 --> 37:33.490 So now. 472 37:36.650 --> 37:38.090 If you see over here. 473 37:40.290 --> 37:42.810 Let me copy this data into the Excel file. 474 37:50.570 --> 37:52.170 So if you see over here. 475 37:59.450 --> 38:00.250 You need sold. 476 38:00.250 --> 38:00.690 Okay. 477 38:11.090 --> 38:12.250 So for this. 478 38:15.810 --> 38:17.170 These are our manufacturer. 479 38:17.170 --> 38:17.450 Right. 480 38:17.450 --> 38:22.490 If you see over here above is our manufacturer. 481 38:29.570 --> 38:33.850 And now this is the unit sold. 482 38:34.570 --> 38:37.410 This is the units sold. 483 38:37.690 --> 38:43.930 So as of now these two column is required for our Solution purpose. 484 38:46.170 --> 38:49.690 So if you see for a baby, these two are the. 485 38:52.810 --> 38:56.090 Based on the units sold, these two are our. 486 38:58.210 --> 38:58.650 Answer. 487 38:58.650 --> 38:58.890 Right. 488 38:58.930 --> 39:00.130 Top two drugs sold. 489 39:00.850 --> 39:07.450 Now let me see for the second manufacturer by. 490 39:08.770 --> 39:10.970 So this is our second manufacturer. 491 39:13.810 --> 39:14.450 Astra. 492 39:16.570 --> 39:17.330 Zeneca. 493 39:17.570 --> 39:20.890 And for that if you see in the unit sold column. 494 39:24.490 --> 39:26.170 These two are the top two. 495 39:29.330 --> 39:29.850 Drugs. 496 39:32.570 --> 39:33.930 Descending by unit. 497 39:33.930 --> 39:34.370 Sold. 498 39:34.970 --> 39:35.490 Right. 499 39:36.210 --> 39:39.050 So now if you see the, uh solution. 500 39:41.450 --> 39:42.890 That is very simple. 501 39:46.650 --> 39:48.970 Select manufacturer drug units sold. 502 39:49.650 --> 39:55.730 As we can see, use our rank row number or denserank any of the function. 503 39:56.210 --> 40:02.530 I use the row number function partition by I have already told for each manufacturer. 504 40:03.130 --> 40:06.930 So in that case and also we need top two right. 505 40:07.530 --> 40:13.410 So in that case we need to do partition by manufacturer order by unit sold. 506 40:14.250 --> 40:18.570 And top two means top two means highest. 507 40:18.730 --> 40:21.810 So highest will become first then second highest will become second. 508 40:21.810 --> 40:22.530 Like that. 509 40:23.170 --> 40:26.130 So that order by descending order. 510 40:28.410 --> 40:30.330 Order by descending order. 511 40:31.210 --> 40:36.410 And then we are encapsulating this block into the CTE. 512 40:37.290 --> 40:42.330 And then We write the city and then wear rank less than or equal to two. 513 40:42.970 --> 40:44.610 So if you run this query. 514 40:47.410 --> 40:50.650 Here are that is coming 2 to 1 331. 515 40:50.650 --> 40:50.890 Right. 516 40:50.890 --> 40:58.370 So that same thing is coming over here 2 to 1 331 ten 189 9 to 5. 517 40:58.410 --> 40:59.970 So 189 9 to 5. 518 41:00.650 --> 41:08.250 Now for Astra Zeneca that is a value 289 1 to 1. 519 41:08.770 --> 41:10.090 So that is coming over here. 520 41:10.090 --> 41:10.250 Right. 521 41:10.290 --> 41:15.250 289289 1 to 1 and 217652. 522 41:15.930 --> 41:17.530 So this is our result. 523 41:17.930 --> 41:18.410 Now. 524 41:20.970 --> 41:24.170 How can we use the sub query. 525 41:24.730 --> 41:29.370 Let me try to do that in place of city. 526 41:29.410 --> 41:33.210 If I want to use the sub query then how can we use that. 527 41:37.530 --> 41:38.050 So. 528 41:42.690 --> 41:47.210 Select star from this will become our entire table. 529 41:52.090 --> 41:52.450 Where? 530 41:52.450 --> 41:52.970 Arc. 531 41:55.170 --> 41:56.530 Less than equal to two. 532 41:57.570 --> 41:59.610 Now which column we require? 533 41:59.650 --> 42:00.290 Over here. 534 42:01.010 --> 42:06.130 We require manufacturer drugs and units sold. 535 42:06.770 --> 42:08.810 These three column only we require. 536 42:15.410 --> 42:17.490 So let me execute this query. 537 42:20.570 --> 42:23.610 So if you see this both query. 538 42:26.250 --> 42:29.010 Will give the same output right. 539 42:32.330 --> 42:35.050 First one we have done with the use of subquery. 540 42:35.330 --> 42:41.010 And then second one we have done we have used this common table expression. 541 42:45.530 --> 42:48.170 So this problem is clear everyone. 542 43:09.050 --> 43:10.530 Clear right guys. 543 43:20.130 --> 43:23.090 Now let me jump to the next question. 544 43:26.930 --> 43:27.250 Okay. 545 43:27.290 --> 43:28.890 This is interesting one. 546 43:31.330 --> 43:35.490 Here we are not using the pharmacy sales data. 547 43:36.370 --> 43:38.490 So this is completely new one. 548 43:43.330 --> 43:51.010 So the LinkedIn creator team is looking for power creators who use their personal profiles as a company 549 43:51.010 --> 43:53.330 or influencer page. 550 43:54.930 --> 44:01.490 This means that if someone's LinkedIn page has more followers, then someone's LinkedIn page has more 551 44:01.490 --> 44:04.810 followers than all the company they work for. 552 44:05.490 --> 44:09.090 We can safely assume that person is a power creator. 553 44:10.170 --> 44:17.330 So here there are different individuals are given and different companies are given in which they are 554 44:17.330 --> 44:18.170 working for. 555 44:19.250 --> 44:25.090 So now different individuals has their own followers and different companies. 556 44:25.090 --> 44:27.850 They are working has their own followers. 557 44:28.290 --> 44:28.730 Right. 558 44:29.570 --> 44:40.450 So now if the individual follower is greater than they work for the company in the they work for the 559 44:40.450 --> 44:47.290 company's follower, then you can consider that individual as a power creator. 560 44:48.330 --> 44:54.690 So now write a query to return the ID of this LinkedIn power creators in ascending order. 561 44:56.050 --> 44:59.410 A person one person can work for multiple companies. 562 45:00.330 --> 45:01.210 In case of. 563 45:01.690 --> 45:07.050 In the case of multiple companies, we need to do sum of all the companies follower. 564 45:09.530 --> 45:14.010 So now here they have given us the table personal profiles. 565 45:15.090 --> 45:15.490 Right. 566 45:15.490 --> 45:16.770 Personal profiles. 567 45:20.010 --> 45:21.410 Then employee company. 568 45:22.290 --> 45:30.450 So if you see in the personal profile profile ID name and followers this is individuals followers. 569 45:31.290 --> 45:32.770 Now employee company. 570 45:32.810 --> 45:37.370 So in that personal profile ID and company ID is given. 571 45:38.010 --> 45:44.490 Now here we can join between personal profile ID and profile ID right. 572 45:46.490 --> 45:48.890 Now again one more table is given. 573 45:48.890 --> 45:49.970 Company pages. 574 45:50.890 --> 45:54.450 So in the company pages company id name and follower. 575 45:55.050 --> 45:59.890 So now company id and company ID over here. 576 46:01.010 --> 46:02.730 Company employee company. 577 46:02.930 --> 46:09.890 So all the three table is either some way there is a relationship between all the three tables. 578 46:09.890 --> 46:10.370 Right. 579 46:17.290 --> 46:17.810 Now. 580 46:21.050 --> 46:22.330 Now if you see. 581 46:29.890 --> 46:33.010 Now let me first select the data from all the three tables. 582 46:37.530 --> 46:39.170 This is the personal profiles. 583 46:49.610 --> 46:51.290 This is employee company. 584 46:54.330 --> 46:56.170 And this is company pages. 585 47:06.210 --> 47:08.810 Let me select the data from the all the three tables. 586 47:14.050 --> 47:18.090 So this is the profile ID name and followers of all the individuals. 587 47:18.770 --> 47:23.450 Now in the second table which is company table. 588 47:23.770 --> 47:28.010 So in that all the individuals are working for the different companies, right? 589 47:30.130 --> 47:31.410 So that is given. 590 47:36.010 --> 47:38.050 And now if you see over here. 591 47:41.370 --> 47:49.690 In the final table company pages, all the company different companies are given with name and companies 592 47:49.690 --> 47:50.130 follower. 593 47:50.170 --> 47:50.650 Right. 594 47:59.250 --> 48:08.170 Now if you see over here for the ah personal profile ID three there is working in the two different 595 48:08.170 --> 48:11.170 companies company one and company ten. 596 48:12.290 --> 48:15.890 So in that case we need to do the sum of both the companies. 597 48:16.330 --> 48:19.290 So let me jump to the solution. 598 48:22.250 --> 48:24.170 So this is the first city. 599 48:24.770 --> 48:25.490 Let me. 600 48:29.330 --> 48:30.730 If you see over here. 601 48:39.770 --> 48:41.210 So these are the company. 602 48:41.690 --> 48:43.770 Each of the employee working for. 603 48:43.930 --> 48:44.410 Right. 604 48:45.210 --> 48:46.410 Personal profile ID. 605 48:46.410 --> 48:50.210 And if you see over here. 606 48:52.970 --> 48:54.810 Three is working for one and ten. 607 48:55.770 --> 48:56.170 So. 608 49:02.890 --> 49:03.730 Let me. 609 49:07.970 --> 49:08.370 Okay. 610 49:08.370 --> 49:09.010 Let me. 611 49:13.210 --> 49:13.890 One. 612 49:13.930 --> 49:14.970 This is one right. 613 49:15.530 --> 49:16.330 This is one. 614 49:16.330 --> 49:21.650 Select this plus ten has this one. 615 49:22.170 --> 49:23.290 So this one. 616 49:23.650 --> 49:25.730 So let me do the sum of these two. 617 49:26.490 --> 49:28.130 It is giving this one. 618 49:32.050 --> 49:33.730 Now you will understand. 619 49:40.010 --> 49:40.530 What is. 620 49:40.570 --> 49:42.250 It is giving in the case of three. 621 49:46.970 --> 49:48.610 It is giving this one only. 622 49:48.610 --> 49:49.010 Right. 623 49:49.450 --> 49:50.850 Which I have done the sum. 624 49:51.570 --> 49:53.090 So understand the question. 625 49:53.690 --> 49:58.450 When employees were working for the multiple companies. 626 49:58.930 --> 50:04.970 We need to do sum of all the companies they are working for. 627 50:06.690 --> 50:09.570 We need to do the sum of all the followers. 628 50:09.890 --> 50:12.530 They are particular employees working for. 629 50:13.050 --> 50:15.850 So that is the only thing I have done over here. 630 50:16.650 --> 50:25.090 Select personal profile ID sum of some followers, some followers from employee company and company 631 50:25.090 --> 50:25.730 pages. 632 50:26.890 --> 50:27.810 Then group by. 633 50:33.770 --> 50:39.010 Group by personal profile ID right and we have employee company and company pages. 634 50:39.010 --> 50:42.370 We have company ID is common in both the tables. 635 50:42.890 --> 50:43.130 So. 636 50:43.170 --> 50:45.770 So this will be become our first city. 637 50:46.930 --> 50:54.530 This will become our first city right now what will be our second city? 638 50:55.010 --> 50:57.330 Second city will be very simple. 639 51:03.570 --> 51:12.410 We just need to use the personal profile table and inject personal ID and followers. 640 51:12.410 --> 51:13.890 We need to select right. 641 51:14.410 --> 51:16.250 So this will be our first city. 642 51:16.690 --> 51:21.410 Now what is our requirement when there is a person? 643 51:22.410 --> 51:25.050 When there is a personal persons follower? 644 51:26.090 --> 51:33.370 This one will be greater than they work for the companies follower, right? 645 51:33.810 --> 51:37.690 They work for the company and that company's follower. 646 51:38.730 --> 51:40.970 So that will be considered as a. 647 51:48.530 --> 51:49.850 Power creator, right? 648 51:50.890 --> 51:55.890 So here we have used the concept of multiplicity. 649 51:56.530 --> 51:58.290 This will become our first city. 650 51:58.330 --> 52:00.970 This will become our second city. 651 52:01.690 --> 52:04.570 We can separate the multiple separate city by comma. 652 52:05.330 --> 52:05.850 Right. 653 52:06.690 --> 52:09.930 And then what I have done over here, we have. 654 52:12.010 --> 52:15.930 Over here, personal profile ID and profile ID will be same, right? 655 52:16.570 --> 52:22.330 So we have done we have joined the two cities, city one and city two. 656 52:22.850 --> 52:30.050 Based upon the personal profile ID and profile ID because of we have used over here, we can use it 657 52:30.050 --> 52:31.450 in the joining condition. 658 52:31.730 --> 52:32.690 And then what? 659 52:34.050 --> 52:34.650 Then what? 660 52:34.650 --> 52:38.530 I have shown you over here, I have shown you over here. 661 52:40.610 --> 52:46.930 This one personal profiles follower should be greater than the company they work for, right? 662 52:51.450 --> 52:53.570 So now let me select the data. 663 53:01.130 --> 53:04.010 This one four, five and seven. 664 53:04.650 --> 53:10.370 So now let me show you any couple of examples. 665 53:13.050 --> 53:16.090 If you say personal profile id one. 666 53:18.170 --> 53:19.890 Has this much of follower. 667 53:20.330 --> 53:24.930 Now one is working for which company that we have to check. 668 53:25.570 --> 53:28.810 So one is working for nine company ID nine. 669 53:29.650 --> 53:33.530 Now company ID nine has how many of followers. 670 53:36.290 --> 53:43.130 So that is one means one follower is greater than. 671 53:45.650 --> 53:47.490 He is working for the companies. 672 53:48.450 --> 53:50.170 All the companies follow, right? 673 53:50.570 --> 53:53.570 Now let me take another example. 674 53:55.850 --> 53:56.770 One for five. 675 53:56.810 --> 54:07.010 Let me take any example where multiple are there for four is coming over here only single time five 676 54:07.050 --> 54:09.450 is coming also over single time only. 677 54:13.410 --> 54:16.050 Okay, I think everyone is coming single time only. 678 54:16.610 --> 54:18.890 So let me take any one seven. 679 54:19.650 --> 54:20.050 Right. 680 54:20.050 --> 54:22.530 So seven is 91,000 follower. 681 54:23.570 --> 54:25.930 So if you see over here 91,000. 682 54:26.210 --> 54:28.250 Now we need to go to the middle table. 683 54:28.250 --> 54:35.770 And in the middle table where that seven is coming is a personal profile ID where seven is coming only 684 54:35.770 --> 54:36.250 one time. 685 54:36.250 --> 54:36.650 Right. 686 54:38.050 --> 54:41.090 So now we need to pick the company ID of that seven. 687 54:41.650 --> 54:42.050 Right. 688 54:42.090 --> 54:44.770 So seven we need to now identify over here. 689 54:45.370 --> 54:48.330 So it is only 1000 followers. 690 54:49.690 --> 54:54.690 So definitely this individual follower is greater than the company. 691 54:54.690 --> 54:56.730 They work for followers. 692 54:57.010 --> 54:57.450 Right. 693 54:58.290 --> 55:00.570 So that will will be our solution. 694 55:03.210 --> 55:06.930 So this is the use of multiple cities. 695 55:11.330 --> 55:17.010 Now let me know if you have any doubt or any query in this question. 696 55:20.930 --> 55:22.290 You got it clear, right? 697 55:36.370 --> 55:41.370 If anyone has any doubt, then they can let me know. 698 55:43.810 --> 55:44.130 Hi. 699 55:44.730 --> 55:49.290 I have a doubt in the last select statement. 700 55:49.450 --> 55:50.370 I'm trying to. 701 55:51.410 --> 55:53.890 It's a bit difficult for me to understand that last. 702 55:54.290 --> 55:59.370 Where CPU two followers greater than city city dot f and W. 703 55:59.410 --> 56:05.850 So can you just repeat explaining the last why you are doing the inner join? 704 56:06.770 --> 56:07.530 Okay. 705 56:08.890 --> 56:11.570 So here, uh, what is our requirement? 706 56:12.010 --> 56:15.650 We need to find the individual follower. 707 56:16.410 --> 56:18.010 Uh, individual follower. 708 56:18.370 --> 56:26.850 And then we need to find the follower that, uh, that individual person is working for all the companies, 709 56:26.850 --> 56:27.330 right? 710 56:27.850 --> 56:35.250 So now, for that purpose, we have divided the entire queries into multiple cities. 711 56:35.730 --> 56:38.130 So this will become our first city. 712 56:38.730 --> 56:47.610 So this first city is indicating any individual person is working for all the companies they are working 713 56:47.610 --> 56:48.010 for. 714 56:49.010 --> 56:50.810 So now if you see over here. 715 56:54.170 --> 56:56.210 This individual one, right. 716 56:56.250 --> 57:02.010 This personal profile ID one is working for the number of companies. 717 57:02.010 --> 57:02.410 Right. 718 57:02.450 --> 57:05.810 So if it is working for any one company then. 719 57:05.850 --> 57:06.650 Actually it is. 720 57:06.650 --> 57:08.330 This one is company's follower. 721 57:08.850 --> 57:10.690 Now this one is company's follower. 722 57:11.170 --> 57:14.650 But working for personal profile ID six. 723 57:15.210 --> 57:17.210 So now we need to compare. 724 57:17.250 --> 57:17.650 Right? 725 57:18.410 --> 57:22.130 Now if you see over here this is the second city. 726 57:22.530 --> 57:26.130 This second city is indicating individual follower. 727 57:26.930 --> 57:33.450 Now I have taken personal profile ID and profile ID in this follower. 728 57:33.610 --> 57:35.810 In this individual follower. 729 57:35.850 --> 57:36.290 Right. 730 57:36.610 --> 57:38.330 We need to do comparison. 731 57:39.530 --> 57:45.050 Then only we can identify any person as a power creator. 732 57:45.930 --> 57:54.410 Now if you see over here this is individual follower should be greater than are greater than the company 733 57:54.410 --> 57:56.650 they are working for, right? 734 57:57.250 --> 58:04.210 So this is are all the personal profile ID and this ID will be same, right? 735 58:04.810 --> 58:07.650 This personal profile ID and this ID will be same. 736 58:08.210 --> 58:15.410 Now we need to compare this one, this follower with this follower. 737 58:15.450 --> 58:16.570 They are working for. 738 58:17.170 --> 58:17.610 So. 739 58:19.770 --> 58:22.450 We are joining over here city. 740 58:22.690 --> 58:24.370 This is the first city right. 741 58:25.250 --> 58:27.090 This will be become our first city. 742 58:27.090 --> 58:29.810 And this will become our second city. 743 58:30.370 --> 58:35.890 Now we need to join both of these city based upon some joining condition. 744 58:35.890 --> 58:36.290 Right. 745 58:36.770 --> 58:38.730 So what will be the joining condition? 746 58:38.970 --> 58:46.850 Only personal profile ID is common right across both the city personal profile ID and profile ID. 747 58:47.410 --> 58:50.370 This will be the same right in both the city. 748 58:50.850 --> 58:59.650 So we are joining this both city city one and city two based upon this personal profile ID and profile 749 58:59.650 --> 59:00.090 ID. 750 59:00.850 --> 59:03.130 Now only one thing is. 751 59:05.210 --> 59:05.770 Renaming. 752 59:05.770 --> 59:07.170 Let me select this data. 753 59:07.690 --> 59:08.010 Okay. 754 59:08.010 --> 59:12.090 Let me show you over here. 755 59:12.090 --> 59:13.010 Followers. 756 59:15.090 --> 59:16.050 As in the. 757 59:20.690 --> 59:21.530 Followers. 758 59:21.530 --> 59:22.850 And this follower. 759 59:23.730 --> 59:24.610 We can say that. 760 59:24.610 --> 59:25.130 Company. 761 59:25.130 --> 59:25.770 Follower. 762 59:28.650 --> 59:30.090 Company they work for. 763 59:30.130 --> 59:30.410 Right. 764 59:30.410 --> 59:30.570 So. 765 59:30.570 --> 59:31.250 Company. 766 59:33.330 --> 59:34.210 Followers. 767 59:34.850 --> 59:36.890 Now what is our requirement? 768 59:39.890 --> 59:41.690 Now let me select this data. 769 59:47.450 --> 59:56.170 So now if you see over here in this one one case individual follower is greater than they work companies. 770 59:56.170 --> 59:57.450 They work for follower right. 771 59:57.890 --> 01:00:04,450 So one now over here, if you see it is not satisfied the condition. 772 01:00:04,490 --> 01:00:09,010 Third again not satisfy the condition four is satisfying the condition. 773 01:00:09,010 --> 01:00:09,410 Right. 774 01:00:09,810 --> 01:00:11,850 So this one is greater than this one. 775 01:00:12,170 --> 01:00:17,850 So now we need to identify this column value should be greater than this one right. 776 01:00:18,570 --> 01:00:25,890 So that only we have done over here in the Where clause follow city two. 777 01:00:26,610 --> 01:00:35,410 So this one city two follower city is two follower should be greater than city one follower that only 778 01:00:36,090 --> 01:00:37,490 we have written over here. 779 01:00:37,570 --> 01:00:45,410 So if you now see run the entire query then all these values should be greater than this value. 780 01:00:46,410 --> 01:00:49,090 Then only individual will be considered as a power. 781 01:00:49,090 --> 01:00:50,770 Greater right. 782 01:00:53,130 --> 01:00:53,810 Perfect. 783 01:00:53,930 --> 01:00:55,770 Um, I understood the logic now. 784 01:00:56,010 --> 01:00:56,410 Yeah. 785 01:00:57,130 --> 01:00:57,530 Okay. 786 01:00:58,010 --> 01:00:58,490 Thank you. 787 01:00:58,650 --> 01:00:59,130 Yes, sir. 788 01:00:59,650 --> 01:01:00,770 This is really unique. 789 01:01:01,650 --> 01:01:04,650 I haven't seen as a company question so far. 790 01:01:04,690 --> 01:01:05,290 That's good. 791 01:01:06,010 --> 01:01:06,610 Ah, yes. 792 01:01:06,610 --> 01:01:06,810 Yes. 793 01:01:06,810 --> 01:01:07,490 Thank you. 794 01:01:12,810 --> 01:01:19,930 Now let me know if you want to continue or we will see the rest of the queries tomorrow. 795 01:01:19,970 --> 01:01:22,410 Let me know if you want to continue. 796 01:01:22,610 --> 01:01:23,730 We will continue. 797 01:01:26,330 --> 01:01:27,850 With the next question. 798 01:01:32,490 --> 01:01:34,570 We have three more questions in the folder. 799 01:01:34,570 --> 01:01:34,930 Right. 800 01:01:34,930 --> 01:01:35,930 So let's go. 801 01:01:36,210 --> 01:01:38,010 I mean, if everybody is interested. 802 01:01:40,610 --> 01:01:40,930 Okay. 803 01:01:40,930 --> 01:01:41,370 Okay. 804 01:01:41,410 --> 01:01:41,850 Sure. 805 01:01:42,050 --> 01:01:42,490 Um. 806 01:01:50,130 --> 01:01:52,130 Someone has written tomorrow. 807 01:01:53,250 --> 01:01:55,130 I think someone is saying tomorrow. 808 01:01:55,130 --> 01:01:55,210 Model. 809 01:01:55,250 --> 01:01:55,650 Okay? 810 01:01:57,170 --> 01:01:57,530 Yeah. 811 01:01:57,530 --> 01:01:59,210 If anyone else want to. 812 01:01:59,770 --> 01:02:04,650 Anyway, if we can continue, you can watch the video. 813 01:02:04,690 --> 01:02:09,170 Also later on I will put this video in my YouTube channel. 814 01:02:09,690 --> 01:02:15,410 And if majority wants to do tomorrow then we can do tomorrow. 815 01:02:17,250 --> 01:02:20,170 So will we be able to practice this queries. 816 01:02:20,170 --> 01:02:26,090 Will we get the file, the question file and the and the the create table statements? 817 01:02:26,810 --> 01:02:27,090 Yes. 818 01:02:27,090 --> 01:02:27,450 Yes. 819 01:02:27,490 --> 01:02:34,410 Uh, you can write the email in the chat box or you can do WhatsApp me. 820 01:02:35,210 --> 01:02:36,730 I will mail you everyone. 821 01:02:36,730 --> 01:02:37,770 All this script. 822 01:02:37,770 --> 01:02:38,490 Everything. 823 01:02:41,970 --> 01:02:44,810 All the script with create table, insert statement, everything. 824 01:02:44,810 --> 01:02:46,610 I will email you.