1 00:01.320 --> 00:04.440 Visa is analyzing its partnership with Apple Pay. 2 00:05.160 --> 00:11.720 Calculate the total volume for each merchant where the transaction was performed via Apple Pay. 3 00:12.480 --> 00:18.680 Or put the merchant ID in the total transactions for merchants with no Apple Pay transactions. 4 00:18.720 --> 00:23.760 Output their total transaction volume as zero okay. 5 00:24.080 --> 00:27.320 Display the result in descending order of transaction volume. 6 00:27.840 --> 00:30.640 So we are having this particular transaction table. 7 00:31.280 --> 00:40.400 And if you see over here there are some of the merchant is paying through the Apple Pay. 8 00:40.400 --> 00:43.480 And some of the merchant is not paying through Apple Pay. 9 00:44.400 --> 00:45.520 So over here. 10 00:46.200 --> 00:48.480 So let me order this data. 11 00:48.640 --> 00:49.720 It is already ordered. 12 00:49.760 --> 00:50.040 Okay. 13 00:50.080 --> 00:51.320 Merchant ID wise. 14 00:52.280 --> 00:52.840 Okay. 15 00:53.480 --> 01:01.320 So if you see over here for two there is no no transaction has been paid through the Apple Pay. 16 01:01.560 --> 01:03.840 So it will be a zero in case of two. 17 01:03.880 --> 01:04.360 Right. 18 01:04.680 --> 01:06.260 Because what they have mentioned. 19 01:08.420 --> 01:15.020 For merchants with no Apple Pay transactions output, their total transaction volume is zero, right? 20 01:15.620 --> 01:25.860 So now over here we can do merchant ID then sum of transaction amount where lower of payment method 21 01:25.860 --> 01:28.300 is equal to Apple Pay and not equal to Apple Pay. 22 01:28.540 --> 01:33.100 So where there is a not equal to Apple Pay at that time, we need to take. 23 01:35.820 --> 01:37.820 Z take zero by default right. 24 01:37.820 --> 01:39.860 We need to take zero by default. 25 01:40.740 --> 01:42.100 Now what we need to do. 26 01:46.300 --> 01:50.180 So merchant ID sum of amount okay. 27 01:50.220 --> 01:55.220 So we need to do union between these two particular data right. 28 01:55.220 --> 01:56.460 We need to do union. 29 01:56.820 --> 02:01.500 So now if we see over here we are getting multiple records right. 30 02:02.100 --> 02:04.380 Because for two it is having zero. 31 02:04.740 --> 02:08.540 But for three also it is having zero and this particular record. 32 02:08.820 --> 02:11.040 So again we need to do some right. 33 02:14.160 --> 02:18.440 So again we need to do some over here based upon the merchant ID wise. 34 02:18.440 --> 02:18.960 Right. 35 02:19.880 --> 02:20.680 So now. 36 02:23.440 --> 02:31.200 We have taken the first block where we are considering only Apple Pay in second block in which we are 37 02:31.200 --> 02:32.680 not considering Apple Pay. 38 02:32.880 --> 02:35.280 And now we are considering the union. 39 02:35.640 --> 02:40.520 And then again we need to do some of merchant ID comma. 40 02:40.720 --> 02:41.080 Sorry. 41 02:41.120 --> 02:44.080 Select merchant ID comma sum of transaction amount. 42 02:44.480 --> 02:45.560 Which amount it is. 43 02:45.560 --> 02:47.440 It is this particular amount right. 44 02:51.720 --> 02:56.320 And then we are doing summing up and then we are calculating. 45 02:56.720 --> 03:00.480 So see for two it will be having zero. 46 03:00.480 --> 03:03.640 And for all other it will be showing only one one record only. 47 03:03.640 --> 03:04.080 Right. 48 03:04.920 --> 03:06.960 So this should be our result set. 49 03:11.240 --> 03:14.600 So if you run all these three query then you will. 50 03:16.300 --> 03:17.180 get to know. 51 03:19.300 --> 03:26.380 See for one, if we consider for one it is having 850 plus 500. 52 03:26.420 --> 03:28.780 So there are 1350, right. 53 03:30.460 --> 03:32.380 But for two there is no Apple Pay. 54 03:32.420 --> 03:34.980 If you see so it will be only zero. 55 03:35.900 --> 03:36.900 Now for three. 56 03:36.940 --> 03:40.180 If we consider 31100. 57 03:40.180 --> 03:42.780 So let's see where it is for three. 58 03:43.020 --> 03:43.500 Okay. 59 03:43.540 --> 03:46.180 One minute for three. 60 03:46.860 --> 03:48.460 How many records are there. 61 03:52.220 --> 03:52.620 Okay. 62 03:52.660 --> 03:54.660 For three we are considering this one. 63 03:55.260 --> 03:57.100 So 3650. 64 03:57.140 --> 03:57.340 Right. 65 03:57.340 --> 03:58.940 3650 for three. 66 03:59.860 --> 04:03.700 And that will be coming in our result set 3650. 67 04:06.820 --> 04:07.340 Right. 68 04:08.820 --> 04:16.460 So this is how this you can give the proper alias over here and everything else will be sorted.