1 00:00.920 --> 00:01.960 Hello everyone. 2 00:02.680 --> 00:03.720 Good morning all. 3 00:04.800 --> 00:09.640 So today we will solve another SQL problem which is asked in product based company. 4 00:10.680 --> 00:13.200 So let's directly jump to the question. 5 00:14.000 --> 00:21.080 CVS health is trying to better understand its pharmacy sales and how well different products are selling. 6 00:22.080 --> 00:25.400 Each drug can only be produced by one manufacturer. 7 00:26.280 --> 00:33.080 So write a query to find the top three most profitable drugs sold and how much profit they made. 8 00:33.880 --> 00:41.440 Assume that there are no ties in the profit, and display the result from the highest to the lowest 9 00:41.440 --> 00:42.560 total profit. 10 00:44.280 --> 00:49.680 Now here the definition of the some of the keywords has been given. 11 00:49.720 --> 00:59.440 Cox means stands for cost of goods sold, which is the direct cost associated with produce, the producing 12 00:59.440 --> 01:05.720 the drug and total profit equal to total sales minus cost of goods sold. 13 01:06.640 --> 01:06.880 Right. 14 01:06.920 --> 01:10.440 So here they have given us the formula to find the total profit. 15 01:11.200 --> 01:15.600 And we have to find the total top three most profitable drugs. 16 01:15.600 --> 01:16.080 Right. 17 01:16.560 --> 01:19.600 So here they have given us the pharmacy sales table. 18 01:19.600 --> 01:25.520 In which product ID unit sold total sales. 19 01:25.520 --> 01:28.240 Cogs means cost of goods sold. 20 01:28.520 --> 01:29.720 Manufacturer. 21 01:31.800 --> 01:32.480 Then drug. 22 01:32.520 --> 01:33.480 Total loss. 23 01:33.920 --> 01:35.400 These columns are given. 24 01:35.840 --> 01:40.120 Now let me show you the table data. 25 01:40.880 --> 01:43.640 Actually how we can find the total profits. 26 01:43.880 --> 01:46.000 So here they have given us the formula. 27 01:46.000 --> 01:46.440 Right. 28 01:47.320 --> 01:50.120 Total sales minus cost of goods sold. 29 01:50.160 --> 01:54.480 So this total sales minus cost of goods sold we need to do right. 30 01:54.800 --> 01:58.080 And then third column is considered as a profit. 31 01:59.280 --> 02:01.840 So this is known as a calculated column right. 32 02:02.360 --> 02:03.440 We are deriving. 33 02:03.480 --> 02:08.000 Deriving this column from the already given columns in a table. 34 02:08.000 --> 02:08.440 Right. 35 02:10.040 --> 02:11.800 So this is a calculated column. 36 02:11.800 --> 02:14.000 And then all the columns we are selecting. 37 02:15.000 --> 02:17.800 So let me select the data. 38 02:19.360 --> 02:24.000 So our requirement is top three most profitable drugs. 39 02:24.400 --> 02:31.000 So over here what I have done order by I can do order by total sales minus cogs s descending. 40 02:31.000 --> 02:39.040 Or also I can do as order by profit descending right. 41 02:40.240 --> 02:42.760 Let me comment this one. 42 02:42.760 --> 02:45.240 So this both will give the same result. 43 02:46.280 --> 02:49.920 And now our requirement is top three most profitable drugs. 44 02:50.280 --> 02:57.320 So from this data this one this one and this one that is our only requirement right. 45 02:58.840 --> 03:01.960 So we can find it in three different ways. 46 03:01.960 --> 03:06.520 I will show you three different ways to find this top three rows. 47 03:07.160 --> 03:14.950 So one thing what we can do is we can directly write the top three drug manufacturer and then total 48 03:14.950 --> 03:22.230 sales minus Cogs s total profit from this table in order by this one total profit. 49 03:22.230 --> 03:24.510 This is the first way which is very simple one. 50 03:24.510 --> 03:24.950 Right. 51 03:26.470 --> 03:35.510 We are just doing top three in order by data right now. 52 03:35.510 --> 03:36.750 The second way is. 53 03:38.870 --> 03:44.030 This is the first way now second way is we are using the ranking function. 54 03:44.390 --> 03:45.550 Rank function. 55 03:46.070 --> 03:47.070 So over here. 56 03:51.350 --> 03:53.750 Let me show you the result again. 57 03:54.910 --> 03:55.310 Sorry. 58 03:55.310 --> 03:56.870 Let me write the inner query. 59 03:56.870 --> 03:57.470 First. 60 03:59.190 --> 04:01.070 Let me execute the inner query first. 61 04:01.550 --> 04:05.110 Now let me copy this data into the Excel file. 62 04:15.750 --> 04:17.150 Now over here. 63 04:17.150 --> 04:18.270 What it is doing. 64 04:21.390 --> 04:23.630 So let me go to the SQL server. 65 04:24.350 --> 04:27.790 So over here what we have done we have we are giving the ranking. 66 04:28.190 --> 04:29.430 Ranking over. 67 04:30.270 --> 04:34.070 Then in the ranking function order by clause is mandatory right. 68 04:34.470 --> 04:38.750 So order by what we are doing order by we are doing the same thing. 69 04:39.150 --> 04:41.070 Total sales minus Cogs. 70 04:41.550 --> 04:43.630 And again it is in descending order. 71 04:44.150 --> 04:52.710 We are doing right because we want highest profit on the first row, then second highest profit on the 72 04:52.710 --> 04:55.830 second row, third highest profit on the third row. 73 04:55.870 --> 04:56.470 Like that. 74 04:56.470 --> 04:56.990 Right. 75 04:57.910 --> 05:05.390 So over here it will generate the rank, generate the rank based upon total sales minus cogs. 76 05:07.190 --> 05:10.630 So let me again go to the Excel. 77 05:11.030 --> 05:12.950 So over here what it has done. 78 05:12.950 --> 05:15.670 It has given the rank to the highest profit. 79 05:16.270 --> 05:19.270 First rank, then second highest profit second rank. 80 05:19.630 --> 05:20.590 Third highest profit. 81 05:20.630 --> 05:21.150 Third rank. 82 05:21.190 --> 05:21.870 Like that. 83 05:22.190 --> 05:25.230 And from this data, our requirement is this one only. 84 05:25.230 --> 05:25.630 Right. 85 05:28.550 --> 05:30.630 Our requirement is top three we want. 86 05:30.670 --> 05:31.070 Right. 87 05:31.230 --> 05:32.630 So now what we can do. 88 05:36.070 --> 05:37.990 This data we already got right. 89 05:38.030 --> 05:42.710 Now we can write the sub query. 90 05:43.350 --> 05:47.270 And again we are selecting the data from this table. 91 05:47.510 --> 05:54.750 So over here what we have done we have mentioned the alias to this particular query right to this inner 92 05:54.790 --> 05:55.230 query. 93 05:55.270 --> 05:57.550 We have mentioned the alias which is t. 94 05:58.070 --> 06:04.790 So now T will become become one table for us T will become one table for us. 95 06:05.190 --> 06:07.190 And now we are select select. 96 06:07.230 --> 06:12.510 We can select any of the columns from this particular table. 97 06:12.510 --> 06:17.510 So we are selecting t dot drop, t dot manufacturer and t dot p. 98 06:18.230 --> 06:20.590 Now this alias we can use over here. 99 06:20.830 --> 06:21.310 Right. 100 06:22.430 --> 06:30.190 And now one ranking one where condition we have to mention which is where r k less than equal to three. 101 06:30.230 --> 06:30.550 Right. 102 06:30.550 --> 06:32.750 Because that is only our requirement. 103 06:35.630 --> 06:36.190 Right. 104 06:36.230 --> 06:40.470 So now let me execute this query. 105 06:42.750 --> 06:48.110 So these are the top three which is matching with our Excel file result right. 106 06:48.990 --> 06:50.830 So this should be our result set. 107 06:52.990 --> 06:57.310 Now in SQL server it is known as a derived table. 108 06:58.190 --> 06:58.870 This is. 109 07:00.910 --> 07:04.070 Derived table meaning is select query. 110 07:08.510 --> 07:23.180 Inside the From clause of the Select statement that is known as derived table. 111 07:23.180 --> 07:23.580 Right. 112 07:25.340 --> 07:34.300 So over here this Select query is particularly existed inside this from clause of this Select statement. 113 07:34.300 --> 07:34.740 Right. 114 07:35.140 --> 07:36.940 That is known as a derived table. 115 07:36.940 --> 07:40.740 In Oracle it is known as a inline view. 116 07:40.980 --> 07:45.740 In Oracle it is known as a inline view right. 117 07:47.260 --> 07:54.740 Now lest you show you another third way, this is the third way to solve this query. 118 07:58.660 --> 08:01.220 And third way is simple city, right? 119 08:01.620 --> 08:09.180 We can, uh, writing the Select statement as it is, which is written over here in the above query. 120 08:09.180 --> 08:12.140 And then we can write the city. 121 08:15.620 --> 08:19.700 In that uh, select statement and mention the result. 122 08:19.700 --> 08:20.140 Right. 123 08:20.140 --> 08:26.420 So which city is then this select query, and outside of the city block we can mention. 124 08:27.980 --> 08:31.420 Select from city where less than or equal to three. 125 08:34.380 --> 08:34.980 Right. 126 08:34.980 --> 08:40.060 So this city we can use outside of this particular block over here. 127 08:44.100 --> 08:44.660 Right. 128 08:46.220 --> 08:50.420 So this is how we can solve the problem in three different ways. 129 08:50.420 --> 08:55.420 If I run all these three query it will give the same result. 130 08:56.460 --> 08:58.900 Let me show you it is giving the same result right. 131 08:58.940 --> 08:59.380 Profit. 132 08:59.980 --> 09:01.860 You can see the profit column. 133 09:02.940 --> 09:07.220 So all the three queries giving the same result right. 134 09:07.580 --> 09:09.260 So thank you so much everyone. 135 09:09.740 --> 09:13.460 I will put the select sorry. 136 09:13.460 --> 09:19.540 I will put the create table script and insert statement script into the description of the video. 137 09:22.100 --> 09:22.860 Thank you.