1 00:01.040 --> 00:02.080 Hello everyone. 2 00:02.920 --> 00:03.920 Good morning all. 3 00:04.880 --> 00:07.040 So let's solve another SQL problem. 4 00:08.640 --> 00:09.160 CVS. 5 00:09.200 --> 00:16.600 CVS health is trying to better understand is pharmacy sales and how well different drugs are selling. 6 00:17.600 --> 00:24.240 Write a query to find the top two drugs sold in terms of units sold for each manufacturer. 7 00:25.160 --> 00:29.960 List your results in alphabetical order by manufacturer. 8 00:31.160 --> 00:38.720 So over here, they told us to find the top two drugs sold in terms of units sold. 9 00:39.240 --> 00:41.040 And for each manufacturer. 10 00:41.400 --> 00:49.800 So whenever there is a word is coming for like each manufacturer or each product or each product category 11 00:50.400 --> 00:57.000 at that time, either you have to use group by or you have to use partition by clause. 12 00:57.760 --> 01:06.920 Right now they have given us this pharmacy sales table in which product ID unit sold Total sales. 13 01:07.440 --> 01:09.160 Cost of goods sold. 14 01:10.280 --> 01:11.200 Manufacturer. 15 01:11.200 --> 01:11.680 Drug. 16 01:12.400 --> 01:16.360 In these three columns are given now. 17 01:16.400 --> 01:19.560 Now let me select the data from this table. 18 01:22.280 --> 01:22.880 Okay. 19 01:23.480 --> 01:26.760 Now let me copy this data into the Excel file. 20 01:36.760 --> 01:37.720 Let's see. 21 01:39.640 --> 01:46.360 Now over here we need to find the top two drugs sold based upon the units sold. 22 01:46.360 --> 01:46.880 Right. 23 01:47.120 --> 01:53.480 So for this manufacturer AbbVie, we can say that this one will be the top two drugs sold. 24 01:53.960 --> 02:04.480 Then again, when a manufacturer is changing from AB V to AstraZeneca at that time, top two drug sold 25 02:04.480 --> 02:05.760 will be this one, right? 26 02:09.080 --> 02:15.790 Then again when Manufacturer uh change top through uh duct sold will be bear. 27 02:15.790 --> 02:16.430 Right. 28 02:16.430 --> 02:23.110 So like this, uh, we need to identify in SQL how we can solve this query. 29 02:25.750 --> 02:34.070 So this is the first way guys what we need to do we need to we need to use the rank Denserank function 30 02:34.070 --> 02:34.710 over here. 31 02:35.350 --> 02:36.710 And in the Denserank. 32 02:37.030 --> 02:45.750 We need to do over partition by because they have told us that we need to identify top two ducts sold 33 02:45.790 --> 02:47.430 for each manufacturer. 34 02:47.430 --> 02:47.750 Right. 35 02:47.790 --> 02:49.430 And based upon units sold. 36 02:50.110 --> 02:50.590 So. 37 02:55.030 --> 02:59.070 So over here let me execute this query. 38 03:01.950 --> 03:03.350 So this one right. 39 03:06.150 --> 03:12.550 They've given us the highest rank to the unit sold as first rank, then second highest unit sold to 40 03:12.910 --> 03:19.910 second rank for each manufacturer Vector and whenever manufacturer is changing, it will generate the 41 03:19.910 --> 03:21.270 rank again, right? 42 03:21.550 --> 03:22.150 Rank one. 43 03:22.150 --> 03:22.910 Rank two. 44 03:23.510 --> 03:23.910 Again. 45 03:23.910 --> 03:30.910 When manufacturer is changing from AstraZeneca to Bayer, it will start from one again, and whenever 46 03:30.950 --> 03:33.430 highest unit sold it will give rank one. 47 03:33.430 --> 03:34.910 Then second is unit sold. 48 03:34.910 --> 03:36.510 It will give rank two. 49 03:36.550 --> 03:37.230 Like that. 50 03:37.510 --> 03:38.550 Now over here. 51 03:38.750 --> 03:40.150 What is our requirement? 52 03:40.950 --> 03:44.150 Our requirement is we want top two only. 53 03:44.590 --> 03:52.950 So we can encapsulate this block inside the city and then outside city. 54 03:52.990 --> 03:53.670 What we can do. 55 03:53.710 --> 04:01.190 Select star from city where R is less than equal to two. 56 04:01.230 --> 04:01.710 Right. 57 04:02.950 --> 04:04.990 Now we want only these three columns. 58 04:04.990 --> 04:05.510 Suppose. 59 04:12.190 --> 04:14.550 So let me select the data. 60 04:17.590 --> 04:20.550 Now for each manufacturer it will give two rows only. 61 04:20.580 --> 04:21.020 right? 62 04:21.620 --> 04:29.780 So if you see this one and in Excel file, if you compare then this one and Excel file result are should 63 04:29.780 --> 04:30.700 be same right. 64 04:31.180 --> 04:33.060 Which we have highlighted in yellow. 65 04:33.180 --> 04:33.700 Right. 66 04:35.500 --> 04:41.860 Then from Jamaica also 289121 right. 67 04:42.100 --> 04:45.300 And 217652 right. 68 04:46.300 --> 04:48.820 So this should be our result right. 69 04:51.260 --> 04:54.300 Now how can we solve this in another way. 70 04:56.060 --> 04:56.460 So. 71 04:58.700 --> 05:00.180 There is another way will be. 72 05:02.700 --> 05:04.140 A derived table right. 73 05:06.740 --> 05:09.580 So this is the another way to solve this query. 74 05:10.060 --> 05:13.500 Same thing only made the changes. 75 05:13.500 --> 05:18.100 We have removed the city and in place of city we have used the derived table. 76 05:18.860 --> 05:19.140 Right. 77 05:19.180 --> 05:21.700 So the so derived table means. 78 05:25.860 --> 05:32.500 Select query in the From clause of the. 79 05:34.820 --> 05:36.460 Select statement. 80 05:36.460 --> 05:36.980 Right. 81 05:39.220 --> 05:42.180 So that is known as select derived table. 82 05:42.820 --> 05:44.220 Now again let me. 83 05:46.380 --> 05:47.900 Use over here descending. 84 05:51.940 --> 05:53.900 So it will give the same result. 85 05:53.900 --> 05:57.260 This both the query first one and this one. 86 05:57.260 --> 06:00.100 Both will be giving the same result right. 87 06:02.820 --> 06:05.860 24 records over here 24 records over here. 88 06:06.500 --> 06:08.420 And if you remove its 48 total. 89 06:08.460 --> 06:08.900 Right. 90 06:10.820 --> 06:13.820 So for each manufacturer it will give two two records. 91 06:14.260 --> 06:17.660 And we have 12 manufacturers right. 92 06:20.060 --> 06:22.060 So thank you so much everyone. 93 06:26.180 --> 06:28.660 We will uh meet in the next video. 94 06:29.180 --> 06:29.900 Thank you.