1 00:01.160 --> 00:02.160 Hello everyone. 2 00:03.480 --> 00:05.880 So let's solve another SQL problem. 3 00:07.040 --> 00:14.080 This is regarding Photoshop revenue analysis for every customer that bought Photoshop. 4 00:14.760 --> 00:21.680 Return a list of customers and total spent on all the products except for Photoshop products. 5 00:22.760 --> 00:23.200 So here. 6 00:23.200 --> 00:24.560 This is the catch guys. 7 00:24.960 --> 00:35.720 We need to return a list of the customers and total spend of all the customers across all the products, 8 00:36.400 --> 00:38.440 apart from the Photoshop products. 9 00:39.360 --> 00:43.240 Sort your answers by customer IDs in ascending order. 10 00:44.200 --> 00:50.720 Now they have given us this Adobe transactions table and I have inserted the data into the same. 11 00:51.560 --> 01:00.040 Now let me select the data from the table and I have copied already this data into the Excel file. 12 01:00.440 --> 01:10.010 So now over here what we need to do we need to identify those customers who have bought Photoshop products. 13 01:10.850 --> 01:12.610 So that is a first step. 14 01:12.610 --> 01:13.850 One, right? 15 01:14.570 --> 01:20.770 Now this is purchase the Photoshop product 234 doesn't purchase. 16 01:20.770 --> 01:23.810 So this will not include in our result set. 17 01:23.810 --> 01:24.330 Anyhow. 18 01:24.370 --> 01:29.050 Right now this is also not included in our result set. 19 01:29.650 --> 01:31.690 And second one is this one. 20 01:33.370 --> 01:41.810 Now our requirement is we need to identify we have identified the two customer IDs 123 and 913. 21 01:42.330 --> 01:44.570 Who has purchased the Photoshop product. 22 01:44.570 --> 01:45.050 Right. 23 01:46.010 --> 01:48.810 But we don't need the revenue of that one. 24 01:48.810 --> 01:55.450 We need the revenue of other products, some apart from the Photoshop product. 25 01:55.890 --> 01:58.210 So our requirement is this one. 26 02:02.810 --> 02:04.370 This one and. 27 02:07.010 --> 02:07.730 This one. 28 02:08.740 --> 02:13.980 So over here for one, two, three we can say that 150 should be result set. 29 02:15.780 --> 02:20.380 And for 913 we can say that 300 will be result set. 30 02:23.020 --> 02:23.540 Right. 31 02:25.340 --> 02:26.500 So you got it right. 32 02:26.580 --> 02:29.620 Now this is our first step. 33 02:29.620 --> 02:35.180 We need to do analyze our or frame our result set into the Excel file. 34 02:35.180 --> 02:36.380 That is the best thing. 35 02:36.860 --> 02:42.780 After that how we can convert this result set or this data into the SQL. 36 02:43.100 --> 02:44.700 That should be the second step. 37 02:45.300 --> 02:55.420 Now over here we need to do we need to identify those customers who have purchased the Photoshop product. 38 02:55.860 --> 02:57.620 So simple thing. 39 03:00.820 --> 03:07.340 We can say that firstly we need to do this one where product is equal to Photoshop. 40 03:07.340 --> 03:16.110 Then again we need to write the outer query From you can say that Adobe transactions table. 41 03:19.910 --> 03:20.510 Where? 42 03:21.710 --> 03:26.510 Customer ID in this customer IDs we require. 43 03:29.190 --> 03:31.390 And over here we need to write. 44 03:31.710 --> 03:34.590 Where product not equal to. 45 03:38.510 --> 03:39.350 Photoshop. 46 03:39.350 --> 03:42.670 Right now let me select the data. 47 03:43.830 --> 03:50.070 Because we require the sum of all the products apart from the Photoshop products. 48 03:50.470 --> 03:56.190 But that customer must have purchased the Photoshop product that is requirement that. 49 03:56.230 --> 04:03.510 What we have seen in this Excel file right now let me execute this query. 50 04:07.990 --> 04:10.670 So over here this should be our result set. 51 04:10.710 --> 04:21.280 We just need to do group by customer ID, and we need to do the sum of revenue right now. 52 04:24.160 --> 04:25.160 Customer ID. 53 04:25.200 --> 04:30.720 Sum of revenue as total. 54 04:31.400 --> 04:35.080 You can say the spend or spend. 55 04:36.400 --> 04:38.120 And then again because of. 56 04:38.160 --> 04:40.920 We have write the aggregate function sum. 57 04:40.920 --> 04:44.680 We need to do group by customer ID. 58 04:44.720 --> 04:47.600 Now let me execute this query. 59 04:50.760 --> 04:54.560 So this is the same thing I have seen you in the Excel file right. 60 04:54.800 --> 04:57.360 1231 50 913 300. 61 04:57.400 --> 04:57.760 Right. 62 04:59.720 --> 05:04.120 So this is the should be the approach of solving any of the SQL query. 63 05:06.520 --> 05:12.000 So let me know if you have any question you can write in the comment section. 64 05:12.480 --> 05:17.080 Please do like subscribe and share as much as possible. 65 05:17.080 --> 05:17.760 Thank you.