1 00:01.680 --> 00:02.600 Hello everyone. 2 00:02.960 --> 00:05.720 So this is our second problem. 3 00:06.960 --> 00:13.720 CVS health is trying to better understand its pharmacy sales and how well different drugs are selling. 4 00:14.640 --> 00:22.920 Write a query to find the top two drugs sold in terms of units sold for each manufacturer last year. 5 00:22.960 --> 00:26.800 Results in alphabetical order by manufacturer. 6 00:27.800 --> 00:31.920 So again this same table is created right. 7 00:31.960 --> 00:33.440 Created with already. 8 00:34.120 --> 00:36.080 So pharmacy sales. 9 00:36.080 --> 00:45.080 And we need to identify top two drugs sold in terms of units sold. 10 00:45.560 --> 00:49.920 So over here we if you see for each manufacturer right. 11 00:50.200 --> 00:53.440 So if you see over here this is our pharmacy sales table. 12 00:53.440 --> 00:56.200 Order by manufacturer and units sold. 13 00:57.040 --> 01:06.830 So for this particular Abbv manufacturer these are the top two drugs sold in terms of units sold. 14 01:07.310 --> 01:16.230 Now, once the manufacturer is changed right from above to something other than extra send, then these 15 01:16.230 --> 01:20.550 two are the top two drugs sold. 16 01:20.950 --> 01:25.910 So we want for each manufacturer, which are the top two? 17 01:28.310 --> 01:32.110 Drugs sold for each manufacturer. 18 01:32.110 --> 01:32.550 Right. 19 01:32.950 --> 01:38.910 So now we can do the ranking function over here. 20 01:39.790 --> 01:41.950 We can use the rank function over here. 21 01:42.670 --> 01:47.190 So partition by manufacturer in order by units sold. 22 01:47.830 --> 01:53.350 So it will give me just execute this query right. 23 01:53.390 --> 01:55.710 Just execute this query first one. 24 01:58.070 --> 02:00.870 So it will give me some result right. 25 02:01.350 --> 02:04.350 So like for above which are the first two. 26 02:06.750 --> 02:07.710 Units sold. 27 02:13.020 --> 02:14.180 Order by units sold. 28 02:14.180 --> 02:17.420 And we can do descending over here. 29 02:17.420 --> 02:17.780 Right? 30 02:19.580 --> 02:24.860 We can do descending over here, and then we can see what it will giving us. 31 02:24.900 --> 02:25.380 Okay. 32 02:25.980 --> 02:30.780 So now for every these two one and two is our requirement. 33 02:31.100 --> 02:36.460 Now again for some other manufacturer one and two is only our requirement right. 34 02:37.060 --> 02:40.700 So now we can encapsulate this block into the city. 35 02:41.420 --> 02:44.500 We can encapsulate this block into the city. 36 02:44.900 --> 02:46.540 So which city is. 37 02:48.100 --> 02:49.420 And then. 38 02:51.460 --> 02:54.100 Select star from city. 39 02:54.100 --> 02:54.540 Where. 40 02:55.220 --> 02:55.860 Arc. 41 02:59.580 --> 03:01.100 Less than equal to two. 42 03:01.420 --> 03:06.940 But now our requirement is these three columns only, not the rank column. 43 03:06.940 --> 03:11.620 So let me mention this three column only. 44 03:12.020 --> 03:14.420 And now let me execute this query. 45 03:17.120 --> 03:18.280 So perfect. 46 03:18.280 --> 03:19.880 This should be our result set right. 47 03:20.160 --> 03:28.040 If I execute above query in this query as well, then see from over here we have order by already units 48 03:28.040 --> 03:28.480 sold. 49 03:28.800 --> 03:31.520 So 2 to 2 and 189. 50 03:31.520 --> 03:34.880 So that should be in our result for every. 51 03:35.360 --> 03:41.720 Then once the manufacturer is changed then 289 and 217. 52 03:41.720 --> 03:47.480 So 289 and 217 is again coming over here for another manufacturer right. 53 03:47.840 --> 03:50.480 So that should be our result. 54 03:51.080 --> 03:54.680 Now we can write this as a derived column as well. 55 03:55.240 --> 03:59.600 So for that purpose how we can write it, it is already written below. 56 03:59.600 --> 04:03.720 But let me show you how we can so we can write it over here. 57 04:03.760 --> 04:04.840 Select star. 58 04:04.840 --> 04:09.080 From now this one will become one. 59 04:09.600 --> 04:10.480 Our table right? 60 04:10.480 --> 04:12.120 This one will become our table. 61 04:13.440 --> 04:17.760 And now we can give where our k less than equal to two. 62 04:19.080 --> 04:21.310 Now let me execute the query. 63 04:24.430 --> 04:24.790 Okay. 64 04:24.830 --> 04:26.510 It is giving me the perfect result. 65 04:26.550 --> 04:32.390 Now our requirement is this three column only, not the last column. 66 04:32.950 --> 04:37.990 So again let me in place of star let me put this three column. 67 04:40.310 --> 04:42.670 And let me check the result. 68 04:43.830 --> 04:46.710 So again it is giving me the 24 rows only. 69 04:47.150 --> 04:47.630 Right. 70 04:48.270 --> 04:54.070 So this is known as a derived table concept right. 71 04:54.110 --> 04:59.590 We are writing the select query inside the From clause of another Select statement. 72 05:00.430 --> 05:05.830 Select query this select query inside the From clause of the another Select statement. 73 05:06.550 --> 05:09.510 That is why it is known as a derived query, right? 74 05:13.230 --> 05:14.870 And this is simple city. 75 05:15.630 --> 05:20.430 So this is how we can solve the problem right. 76 05:22.350 --> 05:23.470 So thank you. 77 05:23.510 --> 05:25.150 Let's solve another problem.