1 00:02.920 --> 00:06.120 So let's continue with the SQL queries. 2 00:08.960 --> 00:16.560 Each Facebook user can designate a personal email address, business email address, and a recovery 3 00:16.560 --> 00:17.480 email address. 4 00:18.360 --> 00:25.920 Unfortunately, the table is currently in the wrong format, so you need to transform its structure 5 00:25.920 --> 00:27.560 to show the following columns. 6 00:28.360 --> 00:34.960 Like we need to show the user ID, personal email, business email, and recovery email. 7 00:36.360 --> 00:39.520 In short, your answer by user ID in ascending order. 8 00:40.200 --> 00:47.560 So if you have seen I have already created the record, created the table, and inserted the data into 9 00:47.600 --> 00:48.040 that. 10 00:48.840 --> 00:53.160 So if you see currently this table has. 11 00:55.280 --> 01:02.410 All the details, all the different type of email address into just one column like email column. 12 01:02.770 --> 01:07.970 In all these addresses, some addresses are personal emails and some are business. 13 01:08.730 --> 01:09.850 Some are recovery. 14 01:09.850 --> 01:10.690 Email address. 15 01:13.690 --> 01:14.410 But now. 16 01:17.970 --> 01:20.170 But now what we require. 17 01:22.010 --> 01:33.410 We require the data to be organized into this format that it should be user ID, then one column for 18 01:33.410 --> 01:39.730 personal email, then one column for business email and one column for recovery email. 19 01:40.930 --> 01:46.850 So from this data we need to convert the result into this format now. 20 01:50.690 --> 01:57.770 Now for that we need different columns different columns for each of the email type. 21 01:59.150 --> 02:05.070 So here we can write the case condition case statement based on the email type. 22 02:05.150 --> 02:11.670 So suppose if uh or we can say that case when email type equal to person personal. 23 02:12.390 --> 02:17.270 So here I have written select user id email type. 24 02:17.630 --> 02:27.830 Now for all these uh personal ID email and recovery email we need to write three different case statement. 25 02:28.710 --> 02:33.830 So case when email type equal to personal then email. 26 02:36.030 --> 02:38.390 Personal then email or otherwise. 27 02:39.150 --> 02:42.710 We do not require anything write in personal email. 28 02:44.070 --> 02:46.390 So otherwise it should be null. 29 02:51.830 --> 02:52.670 One minute. 30 02:54.070 --> 02:54.990 Let's continue. 31 02:57.400 --> 03:01.400 So for that we need to write three different case statement. 32 03:01.680 --> 03:04.160 So case when email type equal to personal. 33 03:04.840 --> 03:07.400 Then we have to write email else null. 34 03:08.200 --> 03:09.720 Then again case. 35 03:09.720 --> 03:15.000 When email type we have three different email type personal business and recovery. 36 03:15.760 --> 03:19.440 So when business then email again we have to write. 37 03:24.560 --> 03:32.440 And then when email type equal to recovery then email because we require email column only write everything 38 03:32.440 --> 03:35.520 is available in email column only. 39 03:36.880 --> 03:38.120 So else null. 40 03:38.280 --> 03:40.560 So this should be recovery email. 41 03:41.360 --> 03:41.840 Now. 42 03:44.200 --> 03:46.640 Now let me execute this query. 43 03:49.480 --> 03:53.480 So if you see over here we have all the columns are available now. 44 03:53.960 --> 03:54.800 But we have. 45 03:55.380 --> 04:06.060 a null value in some of the, uh, some of the rows in all the three, uh, columns, like personal 46 04:06.060 --> 04:08.140 email, business email and recovery email. 47 04:08.620 --> 04:13.820 Because if you see over here this has a big email type is equal to business. 48 04:14.340 --> 04:22.580 So whenever email type is equal to business only business email should have value in all these personal 49 04:22.580 --> 04:26.260 and recovery should be null and vice versa. 50 04:26.260 --> 04:34.220 When email type equal to personal, only personal will have a value and other business email and recover 51 04:34.260 --> 04:35.260 email will be null. 52 04:35.340 --> 04:39.340 So now what should we do now? 53 04:39.460 --> 04:46.420 We need to retrieve maximum of this value in all the columns in group by user ID. 54 04:47.020 --> 04:50.860 So let me do do that. 55 04:50.860 --> 04:52.740 I have already done that over here. 56 04:53.150 --> 04:55.710 Only one change is over here. 57 04:56.190 --> 04:58.190 We have written the maximum of. 58 05:00.670 --> 05:03.630 Each of the human, each of the email type. 59 05:06.870 --> 05:08.430 And then we have ended the. 60 05:15.390 --> 05:15.830 Sorry. 61 05:15.830 --> 05:16.470 Over here. 62 05:16.470 --> 05:17.550 We have to end. 63 05:25.990 --> 05:32.230 So now maximum of email type is required to eliminate the null value. 64 05:32.230 --> 05:34.110 Now let me execute this query. 65 05:35.910 --> 05:40.990 Because we have written the max we have to use the aggregate. 66 05:42.190 --> 05:43.390 We have to do group by. 67 06:03.610 --> 06:05.370 So now, if you see over here. 68 06:10.330 --> 06:13.810 This will be our perfect, perfect output. 69 06:17.170 --> 06:22.450 Now let me compare this output with the original table. 70 06:32.650 --> 06:40.970 So if you see over here user id 123 user ID 123 is personal email address as well as business email 71 06:40.970 --> 06:42.570 as well as recovery email. 72 06:43.170 --> 06:46.610 So hello Nicsell.com. 73 06:47.170 --> 06:48.770 So that is a personal email. 74 06:49.090 --> 06:56.940 So if you if you see over here in user ID personal email, hello nixie.com is available then Nick at 75 06:56.980 --> 07:07.500 that data.com is available in in the business type and then in the recovery email type ns2 SC at the 76 07:07.500 --> 07:12.700 rate virginia.edu is available. 77 07:12.980 --> 07:19.300 But now 234 has only personal email ID is available in business email ID also available. 78 07:19.740 --> 07:27.420 So 234 is personal and business is business email ID is available and recovery email is null because 79 07:27.420 --> 07:31.300 it is not there in original email original table. 80 07:31.860 --> 07:37.620 And this 789 has only business email is there which is available over here. 81 07:37.620 --> 07:39.740 And personal and recovery is null. 82 07:40.260 --> 07:42.180 So this should be our output. 83 07:42.980 --> 07:46.540 We have used the case when we aggregate function over here. 84 07:49.520 --> 07:53.720 So now let me know if you have any doubt in this query. 85 07:58.080 --> 08:02.880 Or if you are clear, then you can write in the chat box or you can speak. 86 08:04.680 --> 08:05.840 Just clear answer. 87 08:07.400 --> 08:08.000 Okay. 88 08:10.360 --> 08:13.440 So let me jump to the next question. 89 08:20.240 --> 08:20.760 Okay. 90 08:22.880 --> 08:33.200 So assuming Salesforce operators own per user pricing model, we have a table containing contracts data. 91 08:35.120 --> 08:41.320 Write a query to calculate the average annual revenue per Salesforce customer in three market segments. 92 08:41.680 --> 08:44.440 So here we have total three different market segments. 93 08:44.440 --> 08:48.210 Are there SMB, Mid-Market and enterprise. 94 08:49.290 --> 08:55.490 Each customer is represented by a single contract format, the output to match the structure shown in 95 08:55.490 --> 08:57.410 the example output section below. 96 08:59.730 --> 09:02.370 So here they have given us some of the assumptions. 97 09:02.850 --> 09:09.250 So when when when SMB when we consider any. 98 09:11.530 --> 09:18.530 Any market segment is SMB when it is less than 100 employees are there. 99 09:19.210 --> 09:25.810 And when 100 to 9, nine, nine employees are there, we can consider as a mid-market segment. 100 09:25.810 --> 09:33.890 And when greater than 1000 employees are there, we can consider as a big, large or enterprise market 101 09:33.890 --> 09:34.610 segment. 102 09:39.090 --> 09:43.090 Now, now here they have given us the explanation. 103 09:43.810 --> 09:50.310 So this is the let me select the data from the required tables. 104 09:50.590 --> 09:52.670 I have already created the tables. 105 09:54.070 --> 09:56.510 I will separately send you through the email. 106 09:58.710 --> 09:59.830 All the scripts. 107 10:07.990 --> 10:09.470 So these are the contracts. 108 10:09.470 --> 10:12.270 Data is available in customers. 109 10:12.270 --> 10:13.470 Data is available. 110 10:14.030 --> 10:19.190 So in the customers table if you see over here employee count column is given. 111 10:21.190 --> 10:27.550 So when there is a less than 100 we can say that less than 100 employee count is there. 112 10:28.030 --> 10:29.030 It is a small. 113 10:30.950 --> 10:33.150 It is a small. 114 10:33.990 --> 10:39.790 Or you can say that SMB market segment when there is a less than 100 employees. 115 10:40.750 --> 10:49.760 So if you see over here Customer ID 2690 has less than 100 employee count. 116 10:49.800 --> 10:57.240 So it is SMB market segment and main market segment 100 to 999. 117 10:57.800 --> 11:08.000 So this 14520 and 78223 can be considered as a mid market segment. 118 11:08.000 --> 11:08.840 And then. 119 11:17.200 --> 11:20.400 Ah rest of the their rest of means. 120 11:21.560 --> 11:29.120 Uh this one and this one will be considered as a large or we can say that enterprise market segment. 121 11:29.640 --> 11:33.160 Now I have copied this data into the Excel file. 122 11:34.400 --> 11:34.760 So. 123 11:38.280 --> 11:41.080 Now here they have given the explanation. 124 11:42.200 --> 11:44.340 This is a simple explanation only. 125 11:44.860 --> 11:45.300 So. 126 11:48.860 --> 11:57.020 Over here SMB market segment is only one customer ID is there and is classified as only SMB customer. 127 11:57.020 --> 12:05.180 In example data, they have a single contract with 50 seats and a yearly seat cost is $25. 128 12:05.900 --> 12:15.900 So here we need to multiply the $2,525, multiply by 50 seats, and we need to divide it by different 129 12:15.900 --> 12:19.260 number of customers they have. 130 12:24.060 --> 12:26.060 Different number of customers they have. 131 12:26.580 --> 12:32.660 So if you see over here 25 multiply by 50. 132 12:33.380 --> 12:36.220 So 1250 divided by one. 133 12:36.220 --> 12:39.220 So it will be 1 to 5 zero only right. 134 12:40.660 --> 12:44.350 So same as mid market segment and enterprise market segment. 135 12:45.190 --> 12:46.590 They've given us the value. 136 12:46.590 --> 12:52.910 Now let me show the data into the Excel file for. 137 12:55.230 --> 13:06.150 So now if you see over here 4520 sorry we can start with 26902690 has 999 employees. 138 13:08.070 --> 13:11.830 So it will be SMB right. 139 13:14.430 --> 13:16.070 SMB market segment. 140 13:16.390 --> 13:18.430 So now we have two different tables. 141 13:18.430 --> 13:20.030 This is customers table. 142 13:23.310 --> 13:24.190 And this is. 143 13:28.110 --> 13:30.750 This is contracts table. 144 13:32.830 --> 13:38.430 And in both the table customer and contracts table customer ID column is common one. 145 13:38.870 --> 13:46.770 So now if you see over here, here there is only one customer which is two, six, nine in the SMB group. 146 13:49.690 --> 13:52.930 2690 is there. 147 13:53.730 --> 13:54.250 Right? 148 14:00.410 --> 14:01.050 And now. 149 14:03.770 --> 14:05.090 These 2690. 150 14:05.130 --> 14:07.530 We need to look up into the contracts table. 151 14:08.210 --> 14:13.290 So in the contracts table 2690 is coming two times right. 152 14:13.610 --> 14:18.570 So now what we need to do as per the explanation they have given over here. 153 14:21.410 --> 14:26.530 As per the explanation they have given over here, we need to multiply the. 154 14:30.050 --> 14:33.050 Number of seats multiply by early seats cost. 155 14:33.570 --> 14:40.420 So that I have already done over here into this column we have multiplied Multiply by 50, multiply 156 14:40.420 --> 14:43.260 by early seed cost, which is 1 to 5 zero. 157 14:43.900 --> 14:48.620 And then again we need to multiply 49. 158 14:48.620 --> 14:52.540 Multiply by 25 which is 1250. 159 14:53.340 --> 15:01.780 Now what we need to do we need to we need to do the sum of these two column, these two columns. 160 15:01.780 --> 15:02.940 We need to do some of. 161 15:02.980 --> 15:11.740 So 1250 plus 1225 okay. 162 15:11.780 --> 15:12.660 We need. 163 15:15.340 --> 15:18.220 So which is 2475. 164 15:18.260 --> 15:18.740 Right. 165 15:18.780 --> 15:20.140 2475. 166 15:21.300 --> 15:26.780 And now how many number of distinct customers are there. 167 15:27.740 --> 15:29.340 Because we need to. 168 15:32.580 --> 15:34.060 We need to calculate the. 169 15:36.960 --> 15:43.680 Calculating value based on the count of distinct customers in each of the different segments. 170 15:45.080 --> 15:53.480 So over here, if you see in the SMB SMB segment, only one customer is available in SMB. 171 15:53.880 --> 15:56.680 How we can say that based on the employee count. 172 15:57.720 --> 16:02.800 So now 2450 should be our output for SMB. 173 16:04.080 --> 16:05.000 Now let me. 174 16:08.080 --> 16:09.800 Go to the mid-market segment. 175 16:09.800 --> 16:12.520 So how we can consider the mid market segment. 176 16:13.080 --> 16:20.000 Whenever employee count is less than 999 right. 177 16:20.440 --> 16:23.320 So are these two for. 178 16:26.640 --> 16:34.320 Mid-Market 4520 and 7832. 179 16:34.920 --> 16:45.170 So these two will be mid market customers because it has less than 1000 employee count and greater than 180 16:45.170 --> 16:46.810 100 employee count. 181 16:47.530 --> 16:51.810 So now we need to look up both of these value into the contracts table. 182 16:52.570 --> 16:54.970 So 4520 is available here. 183 16:54.970 --> 16:57.770 And 782 is available here. 184 16:58.450 --> 17:03.050 Now we need to go to the number of seats and yearly cost column. 185 17:04.090 --> 17:07.170 So these are the number of seats and yearly cost column. 186 17:07.490 --> 17:09.450 I have done the addition over here. 187 17:10.010 --> 17:12.410 Sorry I have done the multiplication over here. 188 17:12.970 --> 17:20.290 So these four value and now this we need to do some of these four value. 189 17:21.690 --> 17:24.130 This one plus this one. 190 17:25.210 --> 17:27.170 Sorry this one. 191 17:35.110 --> 17:35.750 This one. 192 17:35.750 --> 17:40.830 Plus this one plus this one plus this one. 193 17:43.030 --> 17:48.230 So 68,900 right now. 194 17:48.270 --> 17:53.190 How many distinct customers are there available in the mid market segment. 195 17:54.150 --> 17:56.070 That is two distinct segments right. 196 17:56.350 --> 18:00.230 So we need to divide this value by two. 197 18:08.230 --> 18:09.110 I think. 198 18:10.190 --> 18:10.550 Okay. 199 18:10.550 --> 18:11.870 Let me do it over here. 200 18:13.590 --> 18:17.790 This divided by two 3400 201 18:19.510 --> 18:22.590 450 should be our mid market. 202 18:29.070 --> 18:29.750 Answer. 203 18:31.230 --> 18:35.840 And now let me calculate for the large one for enterprise one. 204 18:36.440 --> 18:36.840 So. 205 18:36.880 --> 18:39.600 So if you see over here enterprise. 206 18:43.240 --> 18:46.960 So one greater than 1000 will be enterprise right. 207 18:50.760 --> 18:53.600 Greater than 1000 will be our enterprise. 208 18:54.400 --> 18:54.840 So. 209 18:56.960 --> 19:05.320 25612561 and (252) 352-2352. 210 19:07.720 --> 19:08.800 2352. 211 19:08.840 --> 19:14.280 These two will be our enterprise customers. 212 19:14.960 --> 19:19.720 And we need to look up 2561 and 2352 over here. 213 19:19.720 --> 19:21.560 So same thing I have done over here. 214 19:22.600 --> 19:24.880 So now we need to do addition of this one. 215 19:24.880 --> 19:29.040 This one and this one which I have already done. 216 19:29.920 --> 19:30.500 And It. 217 19:30.660 --> 19:32.580 Let me do it over here again. 218 19:34.860 --> 19:41.020 This one plus this one plus this one plus this one. 219 19:42.180 --> 19:45.180 And now we need to divide it by two. 220 20:00.780 --> 20:07.740 This one plus this one plus this one plus plus this one. 221 20:13.100 --> 20:13.580 Okay. 222 20:13.860 --> 20:17.940 So this should be our answer for the enterprise level. 223 20:21.340 --> 20:24.060 Now let me show you this in the SQL. 224 20:24.940 --> 20:26.380 How I have done. 225 20:27.180 --> 20:30.150 So here if you see over here this is the main table. 226 20:30.190 --> 20:32.870 Customers table and contracts table. 227 20:32.870 --> 20:33.310 Right. 228 20:33.830 --> 20:35.990 Customer table and contracts table. 229 20:37.870 --> 20:39.150 So now we need to. 230 20:46.310 --> 20:47.830 If you see over here. 231 20:53.950 --> 20:55.670 How we can consider the. 232 20:59.910 --> 21:06.230 Customers uh as a SMB customers whenever it has employee count. 233 21:06.270 --> 21:07.230 Less than hundred. 234 21:07.230 --> 21:07.670 Right. 235 21:08.230 --> 21:12.550 So that I have taken over here employee count less than hundred. 236 21:12.550 --> 21:12.950 So. 237 21:18.870 --> 21:20.510 So if you see over here. 238 21:23.470 --> 21:28.680 26902690 from this table, will. 239 21:29.600 --> 21:33.160 It has count less than 100. 240 21:33.440 --> 21:36.160 So we need to look up 2690 over here. 241 21:36.680 --> 21:40.320 So one time it is coming over here 50. 242 21:40.320 --> 21:41.600 Multiply by 20. 243 21:42.280 --> 21:47.120 And then one time it is coming over here 49 multiply by 25. 244 21:48.440 --> 21:50.720 So that only thing I have done over here. 245 21:51.680 --> 21:55.600 So now we need to write the multiple city over here. 246 21:55.600 --> 21:59.240 In this case we need to write multiple city. 247 21:59.280 --> 22:03.520 So this city will be handling the first SMB. 248 22:07.520 --> 22:14.360 For SMB customers this city will work where employee count less than 100. 249 22:14.760 --> 22:20.920 Now what we have to do we have to do number of seats, multiply by early seat cost. 250 22:21.800 --> 22:24.360 And we need to do the sum of these values. 251 22:25.160 --> 22:25.560 Right. 252 22:25.860 --> 22:33.220 Because in the Excel file we have done this some of this value only, but this multiply by this, this 253 22:33.220 --> 22:35.780 multiply by this, this, multiply by this. 254 22:35.780 --> 22:41.180 And then at the end we have done the sum of all this value over here. 255 22:41.180 --> 22:47.780 And then divide it by count of distinct customer ID in the respective market segment. 256 22:49.540 --> 22:53.140 So that is the only thing I have done over here. 257 22:53.780 --> 23:00.100 We have done the sum of number of sales multiplied by early seat cost, divided by count of distinct 258 23:00.660 --> 23:08.860 customer ID in respective market segment, then again by writing the comma again. 259 23:08.900 --> 23:17.820 Second city is mentioned over here in that employee count should be greater than or equal to 100 and 260 23:17.820 --> 23:19.220 less than 999. 261 23:19.740 --> 23:22.260 So at that time it will consider as a. 262 23:24.300 --> 23:26.510 Mid Mid-Market customers. 263 23:31.190 --> 23:33.110 Mid-Market customer segment. 264 23:33.550 --> 23:36.070 And when greater than 1000. 265 23:36.470 --> 23:41.910 Then we will consider it as a large or enterprise market segment. 266 23:47.550 --> 23:49.870 Now over here. 267 23:50.150 --> 23:58.830 Group B is not required because we haven't specify any other column in the Select statement apart from 268 23:58.830 --> 24:01.750 the aggregation column. 269 24:02.510 --> 24:05.510 So group B is not required over here in this case. 270 24:06.150 --> 24:10.790 Now we can individually execute all these three blocks. 271 24:11.510 --> 24:21.950 So 34450I have mentioned over here right in the Excel file 34450 for Mid-Market then. 272 24:24.050 --> 24:25.450 For large enterprise. 273 24:26.170 --> 24:26.490 One. 274 24:26.530 --> 24:26.770 Three. 275 24:26.810 --> 24:27.170 One. 276 24:30.050 --> 24:31.970 That is only I have mentioned over here. 277 24:32.570 --> 24:37.970 We can ignore the point value because it is an integer column as of now. 278 24:37.970 --> 24:40.010 So we can ignore the point value. 279 24:40.690 --> 24:52.930 And for this one small SMB market segment 2475, which is I have already mentioned over here 2475. 280 24:53.410 --> 25:02.010 Now all these three cities when can engage, encapsulate and then absorb the city. 281 25:02.050 --> 25:07.650 We can join by writing the comma condition. 282 25:07.690 --> 25:10.730 This we can say is a cross join. 283 25:11.410 --> 25:13.770 So now let me execute the data. 284 25:15.570 --> 25:23.300 So this will be our output less than 100 less than Nine, nine, nine and greater than. 285 25:29.340 --> 25:30.620 Greater than 1000. 286 25:30.620 --> 25:31.820 More than 1000. 287 25:32.260 --> 25:34.060 This will be our result. 288 25:34.060 --> 25:34.460 Set. 289 25:35.180 --> 25:35.820 Final. 290 25:40.140 --> 25:40.900 So now let. 291 25:40.940 --> 25:43.100 This is the concept of multiplicity. 292 25:43.140 --> 25:43.700 We are. 293 25:44.420 --> 25:46.740 We have already seen it many times. 294 25:46.740 --> 25:48.060 But one more time. 295 25:51.620 --> 25:54.340 We have seen the concept of multiple cities. 296 25:54.380 --> 25:58.940 Now let me know if you have any query or you are clear. 297 26:02.660 --> 26:05.660 You can write in the chat box as well. 298 26:13.660 --> 26:14.940 I know. 299 26:17.780 --> 26:20.520 This ah question is clear Guy, right? 300 26:24.680 --> 26:26.160 Question and explanation. 301 26:35.280 --> 26:36.440 I will send you. 302 26:38.520 --> 26:41.440 In the email after completion of the class. 303 26:45.000 --> 26:48.680 You can create the table and do practice. 304 26:53.480 --> 26:57.520 So let me jump to the next question if you have no doubt. 305 27:02.880 --> 27:05.520 So this is very interesting question. 306 27:05.520 --> 27:07.760 Year over year growth. 307 27:07.760 --> 27:10.080 It is related to year over year growth. 308 27:12.040 --> 27:18.640 So assume you are given a table containing information about Wayfair's user transaction for different 309 27:18.730 --> 27:19.450 products. 310 27:20.770 --> 27:27.690 Write a query to calculate the year on year growth rate for the total spend of each product. 311 27:28.290 --> 27:30.810 Grouping the results by product ID. 312 27:33.530 --> 27:36.490 The output should include the year in ascending order. 313 27:36.490 --> 27:39.370 Product ID current year spent. 314 27:39.810 --> 27:40.450 Previous. 315 27:42.850 --> 27:43.930 Current year spent. 316 27:43.970 --> 27:44.370 Previous. 317 27:44.410 --> 27:46.770 Years spent per year on year. 318 27:46.810 --> 27:47.970 Growth percentage. 319 27:48.610 --> 27:50.610 Round to two decimal places. 320 27:52.330 --> 27:56.530 So here they have given us the table user underscore transactions table. 321 27:56.930 --> 28:03.010 In that transaction ID product ID spend and transaction date column are there. 322 28:04.090 --> 28:06.170 I have already inserted the data. 323 28:07.570 --> 28:13.570 Now let me select the data from the user underscore transactions table. 324 28:16.530 --> 28:23.670 So if you see over here different transaction IDs are there in the table, which is unique one. 325 28:25.230 --> 28:27.790 Now different products IDs are mentioned. 326 28:28.430 --> 28:33.270 So which is repeated one right. 327 28:33.310 --> 28:36.950 Because if you see over here. 328 28:43.590 --> 28:48.910 By different product ID by different year they have spent some amount. 329 28:48.910 --> 28:57.350 So if you see over here in the transaction date 2019 they spent some amount. 330 28:59.470 --> 29:10.430 To 2020 they've spent some amount for 12342 for product ID then again for the same product ID in 2222021. 331 29:11.070 --> 29:15.670 They've spent some amount and 2022 they've spent some amount. 332 29:15.760 --> 29:21.320 So all this distinct product ID, they've spent some amount in 333 29:21.560 --> 29:27.040 201920202021 334 29:27.600 --> 29:29.000 and 2022. 335 29:29.840 --> 29:37.320 So from this data, we need to identify year over year growth for all these product ID. 336 29:38.320 --> 29:43.120 Now how can we calculate the year over year growth. 337 29:43.880 --> 29:53.000 So this this will be the very famous question in the power BI as well in the power BI. 338 29:53.040 --> 29:58.360 Also by writing the Dax function we are calculating the year over year growth. 339 29:58.720 --> 30:03.880 So formula will be the same in power BI as well as in SQL. 340 30:04.920 --> 30:10.520 So current years I have written over the from formula over year. 341 30:11.080 --> 30:14.580 Current year sales minus previous year. 342 30:14.580 --> 30:19.260 Sales divided by previous year sales multiplied by 100. 343 30:20.380 --> 30:28.780 So this is the standard formula, whether it is in power BI or in SQL to calculate the year over year. 344 30:28.820 --> 30:33.700 First, take your current year revenue and then. 345 30:35.740 --> 30:38.220 Subtract the previous year's revenue. 346 30:39.180 --> 30:42.340 This gives you the total change in revenue. 347 30:43.220 --> 30:44.780 Then take the amount. 348 30:44.980 --> 30:49.260 Take the amount and divide it by last year's total revenue. 349 30:50.940 --> 30:53.380 So that is only I have written over here. 350 30:54.380 --> 31:05.020 We need to divide the previous year sales and then take the sum and multiply it by 100 to get your year 351 31:05.060 --> 31:06.420 over year percentage. 352 31:09.700 --> 31:12.820 Now at the end we need to multiply it by 100. 353 31:14.990 --> 31:15.470 Now. 354 31:19.670 --> 31:21.390 Let me show you the data. 355 31:24.870 --> 31:28.150 So I these are the data. 356 31:28.150 --> 31:30.910 Transaction date wise difference. 357 31:30.910 --> 31:35.870 And now what I have done over here, that is important. 358 31:40.030 --> 31:48.070 So from the entire transaction date, I have taken the year of transaction date only because that is 359 31:48.070 --> 31:49.030 my requirement. 360 31:49.030 --> 31:58.030 I need to find year over year growth 2029 how much from 202922020. 361 31:58.590 --> 32:08.030 How much spend has been done in different product ID now for that purpose? 362 32:08.230 --> 32:13.570 Year of transaction date I have taken over here, then product ID then spend. 363 32:14.850 --> 32:18.770 So this normal spend will be the current year spend. 364 32:20.290 --> 32:23.050 Sorry, this spend will be the current year spend. 365 32:23.890 --> 32:31.010 Now to find the previous year spend we need to use the lag function. 366 32:34.410 --> 32:38.690 So lead and lead are the different analytical functions in SQL. 367 32:38.730 --> 32:40.970 Or you can say that relational function. 368 32:42.650 --> 32:44.050 This will give us the. 369 32:46.370 --> 32:51.530 Previous years spend on the different product. 370 32:52.050 --> 32:54.690 Sorry on the same product right. 371 32:55.290 --> 33:06.770 So lag then I have right this spend over here spend of one because I need only next year spend only 372 33:06.770 --> 33:09.490 I don't need to do jump to. 373 33:11.180 --> 33:12.820 two years or three years, right? 374 33:12.860 --> 33:21.180 That's why I have written over here one over partition by product ID because we have similar product 375 33:21.180 --> 33:27.260 ID is given in 2019, 2021, 22. 376 33:27.780 --> 33:34.220 Then again, this product ID has records for 2019, 2021 and 22. 377 33:34.820 --> 33:45.060 So that's why whichever column is repeated values, we have to do partition by that particular column. 378 33:45.500 --> 33:49.060 So that I have done partition by product ID. 379 33:49.820 --> 33:50.860 Now order by. 380 33:51.340 --> 33:59.900 We need to do the transaction date because I need to identify year over year spend. 381 34:00.580 --> 34:01.460 So that. 382 34:03.900 --> 34:06.220 Order by year of transaction date. 383 34:06.660 --> 34:16.320 So this will be whenever you use the leg function, it will give us the previous year value, previous 384 34:16.360 --> 34:18.720 year sales or previous year profit like that. 385 34:23.200 --> 34:25.000 Previous error data lag. 386 34:27.080 --> 34:30.120 And whenever we want to use. 387 34:32.280 --> 34:38.520 Next year data or we want next year sales or next year profit like that. 388 34:39.080 --> 34:41.200 Then we are using lead function. 389 34:42.000 --> 34:44.000 But now our requirement is. 390 34:46.320 --> 34:47.360 Previous year sales. 391 34:47.360 --> 34:47.800 Right. 392 34:47.840 --> 34:49.520 So we have used the lag function. 393 34:50.040 --> 34:56.160 Now what we need to do now we need to go to this formula. 394 34:58.440 --> 35:00.360 We need to go to this formula. 395 35:01.400 --> 35:02.080 This one. 396 35:05.720 --> 35:07.850 So that I am writing it over here. 397 35:11.090 --> 35:11.570 Now what? 398 35:11.570 --> 35:14.530 This formula said current year sales. 399 35:15.010 --> 35:17.930 So here we need current year spend. 400 35:18.770 --> 35:22.250 So this spend will be the current year spend right. 401 35:23.250 --> 35:25.570 This spend will be the current year spend. 402 35:26.090 --> 35:32.850 So that I have written over here spend now minus previous year spend. 403 35:33.530 --> 35:40.450 So this entire thing, this entire thing I have written over here in the minus. 404 35:43.170 --> 35:45.930 Minus part minus. 405 35:45.930 --> 35:47.370 Previous year spend. 406 35:47.890 --> 35:48.530 Current year. 407 35:48.530 --> 35:49.250 Spend minus. 408 35:49.250 --> 35:54.090 Previous year spend divided by previous year spend. 409 35:54.450 --> 35:56.450 So again this same thing. 410 35:56.730 --> 36:06.470 Again this same thing I have written over here after division divided by Previous years spend. 411 36:07.670 --> 36:08.990 Now what we need to do. 412 36:09.710 --> 36:11.230 Only one thing is spending. 413 36:11.230 --> 36:18.230 We need to calculate everything which multiply by 100. 414 36:18.710 --> 36:19.190 Right. 415 36:23.550 --> 36:23.950 So. 416 36:31.070 --> 36:35.190 Let me write this entire thing or. 417 36:47.710 --> 36:48.110 Okay. 418 36:48.110 --> 36:51.390 Let me now try to execute this entire thing. 419 36:57.990 --> 36:58.550 Okay. 420 37:01.390 --> 37:03.960 This is the result of multiply by buy 100. 421 37:15.880 --> 37:18.640 So this is the actually the result. 422 37:19.040 --> 37:19.840 Current year. 423 37:19.880 --> 37:20.280 Year. 424 37:21.360 --> 37:22.000 Current year. 425 37:22.000 --> 37:22.560 Spend. 426 37:22.960 --> 37:32.440 So this is the 2019 for we do not have previous year sale means 2018 sale is not. 427 37:34.520 --> 37:35.320 Available right. 428 37:35.320 --> 37:37.640 So it will be showing over here NULL. 429 37:38.400 --> 37:40.200 It will be showing over here null. 430 37:40.480 --> 37:45.040 Now in the 2020 current year sale is 1000. 431 37:45.520 --> 37:51.040 Previous year sale is 1560 which is of 2019 sale. 432 37:52.960 --> 37:53.400 Right. 433 37:53.720 --> 37:57.360 So current year sales is 1000. 434 37:57.400 --> 37:58.840 Let me write it over here. 435 38:01.200 --> 38:03.220 Minus previous year sales. 436 38:04.580 --> 38:05.700 Minus previous year. 437 38:05.740 --> 38:06.260 Sales. 438 38:12.060 --> 38:14.900 Divided by previous year sales. 439 38:19.300 --> 38:21.100 Multiply by 100. 440 38:25.700 --> 38:28.780 So this is our result right away. 441 38:28.820 --> 38:29.380 Result. 442 38:29.780 --> 38:32.540 Or if we see over here. 443 38:44.740 --> 38:46.660 This 33.35. 444 38:47.020 --> 38:50.060 What I have it is. 445 38:53.140 --> 38:57.540 Given as the rounding rounding value because we have used the round function. 446 38:57.540 --> 38:57.980 Right. 447 38:58.340 --> 39:01.750 So this will be our final result set. 448 39:02.110 --> 39:03.150 Year over year. 449 39:03.190 --> 39:04.590 Growth rate. 450 39:07.990 --> 39:09.070 Now let me know. 451 39:09.110 --> 39:13.470 Are you guys clear with this approach? 452 39:27.150 --> 39:27.990 In power BI. 453 39:28.030 --> 39:36.350 Also we can I think power BI is some inbuilt functions are there in the Dax part so that. 454 39:39.830 --> 39:43.550 We can use it over there the inbuilt functions. 455 39:44.430 --> 39:47.870 But in SQL we can find it with the lag function. 456 39:48.390 --> 39:50.830 We need to first find the current year cell. 457 39:51.790 --> 39:54.230 So it is already given in the table right. 458 39:54.590 --> 40:01.320 But to identify the previous year cell there is a formula is like this one. 459 40:02.040 --> 40:05.400 Current year cell minus previous year cell divided by previous cell. 460 40:05.440 --> 40:07.640 Cells multiply by 100. 461 40:07.960 --> 40:14.160 So that thing we have done over here only write current year spent is the current year cell. 462 40:14.760 --> 40:23.880 Then minus minus log function we have used to identify the previous year cells partition by product 463 40:23.880 --> 40:24.640 ID right. 464 40:27.000 --> 40:30.960 So we have not done the over here descending function. 465 40:31.600 --> 40:35.000 So it is giving us the exact output. 466 40:35.000 --> 40:41.080 If we have write the order by year date descending then it will not give us the proper output. 467 40:43.760 --> 40:48.680 The current year cell minus the previous year divided by previous year cell. 468 40:48.680 --> 40:53.800 So again we have used this entire function entire log function over here. 469 40:54.160 --> 40:57.680 And then multiply by 100 and rounding to two decimal places. 470 40:58.420 --> 41:00.020 This will be our output. 471 41:03.620 --> 41:05.060 So now let me know. 472 41:05.060 --> 41:08.740 Are you guys clear with this approach? 473 41:16.900 --> 41:20.060 You can write in the chat chat message. 474 41:31.660 --> 41:34.460 Or if you have any doubt you can speak over here. 475 41:41.780 --> 41:45.540 If there is no doubt, we can move ahead. 476 42:03.110 --> 42:03.750 Okay. 477 42:03.750 --> 42:04.430 Thanks. 478 42:10.630 --> 42:13.390 So let me jump to the next query. 479 42:26.990 --> 42:33.950 So in an effort to identify high value customers, Amazon asks for your help to obtain data about users 480 42:34.430 --> 42:36.270 who go on shopping sprees. 481 42:37.510 --> 42:44.150 List the user IDs who have gone on at least one shopping spree in ascending order. 482 42:49.510 --> 42:51.670 So here they have given us the data. 483 42:52.190 --> 42:52.910 Create table. 484 42:52.950 --> 42:54.190 Transactions table. 485 42:54.190 --> 42:54.290 Well. 486 43:01.370 --> 43:06.730 Uh, I already created the user ID amount and purchase date columns are given. 487 43:08.210 --> 43:09.970 So if you see the data. 488 43:12.730 --> 43:13.210 Here. 489 43:16.410 --> 43:18.290 I have sorted it by user id. 490 43:18.290 --> 43:25.330 So user ID one has purchased the two purchase only two different dates right? 491 43:25.770 --> 43:29.810 User ID one has purchased only two different date here. 492 43:29.850 --> 43:31.130 One thing is missing. 493 43:31.930 --> 43:35.050 Actually two different purchase has been done within the. 494 43:37.450 --> 43:41.690 Within the two days of the transaction date. 495 43:41.970 --> 43:48.890 Purchase date within the two days of the two days of the purchase date. 496 43:49.250 --> 43:57.380 So if you see over here, there are the purchase date of user ID one is 1st August and then 17th August. 497 43:57.700 --> 44:00.060 So this will not qualify. 498 44:00.100 --> 44:10.220 Right now if you see over here user ID two has done three different purchase purchases on five, six 499 44:10.220 --> 44:22.380 and seven, and in their first purchase and last purchase happened on the within the two days. 500 44:22.380 --> 44:22.660 Right. 501 44:22.660 --> 44:23.780 Within the two days. 502 44:24.460 --> 44:29.660 So it will be considered in our output user ID two. 503 44:30.860 --> 44:32.940 Let me write it over. 504 44:32.980 --> 44:34.260 Copy it over here. 505 44:34.820 --> 44:40.220 Then user ID three is only single purchase, so three and four will not be considered. 506 44:40.860 --> 44:46.260 Four has only single purchase and five again 28, 29 and 30. 507 44:47.020 --> 45:02.280 So five this value will will also be Consider because this 5N2 both have have done purchases is in within 508 45:02.280 --> 45:03.880 the next two days. 509 45:03.880 --> 45:04.400 Right. 510 45:04.880 --> 45:05.840 Next two days. 511 45:06.200 --> 45:08.920 So how we can consider that thing over here. 512 45:11.680 --> 45:15.240 So this is a simple one I have solved it with. 513 45:17.280 --> 45:19.640 With two different methods. 514 45:22.800 --> 45:25.600 So here I have only one column is there right. 515 45:25.640 --> 45:29.120 Purchase date column is there in the original table. 516 45:31.040 --> 45:37.040 So here I need to find the maximum of purchase date and minimum purchase date. 517 45:37.320 --> 45:41.880 And then I have to subtract the maximum of purchase date. 518 45:43.960 --> 45:48.000 I have to do minus minimum of purchase date. 519 45:48.000 --> 45:50.810 And then I have to see the difference. 520 45:51.330 --> 45:54.370 Should be two only, right? 521 45:54.370 --> 45:58.690 Because if you see over here five and seven. 522 45:59.170 --> 46:02.570 So seven minus five will be two again over here. 523 46:02.610 --> 46:04.050 28 and 30. 524 46:04.490 --> 46:06.330 So 30 minus five. 525 46:07.610 --> 46:09.890 Sorry 30 -28 will be two. 526 46:10.370 --> 46:14.690 This will be our max of purchase date for user ID five. 527 46:14.730 --> 46:19.330 And this will be minimum of purchase date for user ID five. 528 46:19.890 --> 46:21.130 Same thing over here. 529 46:21.130 --> 46:24.290 Maximum of purchase date for user ID two. 530 46:24.330 --> 46:30.050 And this will be minimum of purchase date for user ID two. 531 46:30.570 --> 46:34.490 So now if you see over here. 532 46:37.290 --> 46:38.490 User ID one. 533 46:39.370 --> 46:44.530 Maximum date is 17th August and minimum date is 1st August. 534 46:44.930 --> 46:53.350 In the difference between maximum and minimum date is 16, so this will be not considered right now. 535 46:54.190 --> 46:57.990 This has done the on the same date. 536 46:59.070 --> 47:01.950 So only I think only one transaction is there right. 537 47:02.710 --> 47:07.110 For user ID and user ID for has only single transaction. 538 47:07.830 --> 47:15.150 Because of that maximum of purchase date and minimum of purchase date is same and there is no difference. 539 47:15.190 --> 47:15.910 Zero difference. 540 47:15.910 --> 47:16.190 Right. 541 47:16.190 --> 47:19.950 So this again one, three and four will be not considered. 542 47:20.470 --> 47:23.870 Now see the result of two and five. 543 47:24.750 --> 47:33.350 So two is 7th August and five is maximum purchase date and two is 5th August as the minimum of purchase 544 47:33.350 --> 47:33.750 date. 545 47:34.510 --> 47:39.110 The difference is exactly two, right I have write it over here. 546 47:39.870 --> 47:40.830 Maximum. 547 47:44.150 --> 47:46.830 Maximum of purchase date minus. 548 47:46.960 --> 47:48.320 minimum purchase date. 549 47:48.640 --> 47:55.760 So here first I have taken the date part and I have to choose the day column only. 550 47:55.760 --> 47:56.800 So day will be. 551 47:58.840 --> 48:04.240 Seven over here and over here day will be five right. 552 48:04.400 --> 48:06.520 So seven minus five it will be two. 553 48:06.960 --> 48:10.200 And again over here 30 -28 it will be two. 554 48:10.840 --> 48:19.160 So now in this diff value I in this diff column I require only two where it is a two right. 555 48:19.800 --> 48:24.040 So now if I execute this one let me first. 556 48:33.040 --> 48:34.440 First select everything. 557 48:35.880 --> 48:37.760 So these are the result right. 558 48:37.800 --> 48:44.200 Now what is my requirement by requirement is to identify the distinct user IDs. 559 48:45.040 --> 48:49.060 Distinct user IDs who have purchased. 560 48:52.820 --> 48:55.620 So I just need to select the. 561 48:56.180 --> 49:00.740 In place of star I need to write distinct user ID. 562 49:05.340 --> 49:06.500 So two and five. 563 49:08.620 --> 49:15.740 Are the distinct user ID which I have already shown over here who have purchased three or more consecutive. 564 49:18.580 --> 49:21.260 Three or more items on the three array. 565 49:21.300 --> 49:25.900 Sorry, who have purchased on on three or more consecutive days. 566 49:29.700 --> 49:30.980 Five, six and seven. 567 49:30.980 --> 49:34.820 And this one is purchased on 28, 29 and 30. 568 49:38.060 --> 49:42.260 So guys, this solution and question. 569 49:52.470 --> 49:55.190 If there is any doubt, you can ask in the chat box. 570 49:55.190 --> 49:57.230 Otherwise you can speak over here. 571 50:05.950 --> 50:06.230 Yeah. 572 50:06.750 --> 50:07.390 No doubt. 573 50:08.230 --> 50:09.430 Clear any. 574 50:10.750 --> 50:11.350 Thank you. 575 50:12.070 --> 50:12.790 You're welcome. 576 50:14.630 --> 50:17.630 So now let me see the final query for today. 577 50:23.870 --> 50:26.070 As a data analyst at Uber. 578 50:26.230 --> 50:33.150 Uber it's your job to report the latest metrics for a specific group of Uber users. 579 50:34.070 --> 50:35.150 So some ride it. 580 50:35.430 --> 50:41.470 Riders create their Uber account the same day they book their first ride in. 581 50:41.470 --> 50:44.810 The rider engagement team calls them in the moment users. 582 50:47.650 --> 50:48.130 Right. 583 50:48.170 --> 50:55.530 And Uber wants to know the average delay between the day of user signups and the day of their second 584 50:55.530 --> 51:04.330 ride, right to pull the average second ride delay for in the moment, Uber users round the answer to 585 51:04.370 --> 51:05.650 two decimal places. 586 51:07.410 --> 51:13.970 So actually here they have given us the two different tables users table and rides table. 587 51:14.370 --> 51:22.250 So in the users table, user ID in registration date is there in in the rides table right. 588 51:22.290 --> 51:26.490 ID user ID and ride date will be there. 589 51:27.050 --> 51:30.410 So some of the users have done the registration. 590 51:31.930 --> 51:39.090 Some of the users had done the ride on the same date when they have done the registration. 591 51:40.090 --> 51:47.900 So if you see in the users table and rice table some day, some dates will be. 592 51:55.420 --> 51:57.380 Users table and rice table. 593 51:58.180 --> 52:01.020 Some user has done the ride on the same date. 594 52:01.460 --> 52:07.260 They have done the registration, so that will be called as a in-the-moment user. 595 52:08.180 --> 52:17.180 So if you see over here for right ID one, right ID one has done the registration on 15th August and 596 52:17.180 --> 52:20.260 he has done the first ride on the 15th August only. 597 52:22.940 --> 52:23.420 Right. 598 52:24.700 --> 52:28.060 And second right is done on the 16th August. 599 52:30.580 --> 52:33.020 Now for user ID two. 600 52:39.600 --> 52:40.160 Okay. 601 52:40.520 --> 52:50.120 User ID two has registered on the 21st August, but he has done the first ride on 20th September. 602 52:50.560 --> 52:55.240 So this user ID two is not not in the moment user. 603 52:57.800 --> 53:01.240 But now we want second ride. 604 53:02.920 --> 53:03.920 Second ride. 605 53:07.520 --> 53:11.400 Also to identify the second ride delay right. 606 53:12.560 --> 53:19.000 So because of that so so that what I have done over here in the first city. 607 53:21.640 --> 53:28.920 I have identified those user who have done the right on the same date. 608 53:30.280 --> 53:31.960 They have done the registration. 609 53:32.600 --> 53:35.720 So these four users have done the. 610 53:38.650 --> 53:41.450 Right on the same day they've registered. 611 53:42.090 --> 53:49.050 Now this will be considered as a in the moment users table right? 612 53:49.210 --> 53:52.530 Moment user means in the moment user table. 613 53:52.530 --> 53:55.330 This city will be considered as that one. 614 53:55.810 --> 53:57.930 Now this will be our second city. 615 53:58.450 --> 54:00.610 Again we are using the lag function. 616 54:01.330 --> 54:04.250 So over here we need to identify. 617 54:10.850 --> 54:13.330 We need to find the second delay right. 618 54:13.850 --> 54:20.090 So over here lag of right date partitioned by user. 619 54:20.730 --> 54:27.090 Because in the right table multiple one user has done the multiple rise right. 620 54:27.690 --> 54:31.490 So that user ID is repeating over here in the right table. 621 54:32.810 --> 54:35.810 So partition by user ID and order by right date. 622 54:37.790 --> 54:39.830 So that will be our leg. 623 54:39.830 --> 54:40.270 Right? 624 54:40.310 --> 54:40.750 Date? 625 54:44.470 --> 54:44.990 Right. 626 54:46.030 --> 54:53.830 Now we need to identify the difference between the leg right date and right date. 627 54:54.830 --> 54:56.510 So this will be our right date. 628 55:00.750 --> 55:08.190 And this will be our leg right date which has been calculated with the use of leg function. 629 55:09.790 --> 55:13.270 So now this will become our second city. 630 55:21.630 --> 55:24.870 This will be our second city, right? 631 55:24.910 --> 55:25.230 This is. 632 55:25.270 --> 55:27.070 This is the first ride. 633 55:27.070 --> 55:27.350 Right? 634 55:27.350 --> 55:27.630 Date. 635 55:27.630 --> 55:30.830 And this is the second ride. 636 55:30.830 --> 55:31.070 Right? 637 55:31.110 --> 55:31.870 Right date. 638 55:35.120 --> 55:42.880 Now over here, we have calculated the difference between the second right and first right into the 639 55:42.880 --> 55:43.280 days. 640 55:45.400 --> 55:47.040 And we need average delay. 641 55:47.040 --> 55:47.360 Right. 642 55:47.400 --> 55:49.480 Average delay in the second right. 643 55:50.160 --> 55:51.800 Average delay in the second right. 644 55:51.800 --> 55:54.360 So that we have used the average function over here. 645 55:55.360 --> 56:01.240 And then we want to identify the result into the two decimal places. 646 56:01.920 --> 56:08.040 Because of that we have done the cast of decimal by five comma two. 647 56:10.440 --> 56:12.440 So let me execute this query. 648 56:20.760 --> 56:23.080 Zero points 67. 649 56:24.240 --> 56:26.880 This will be the average delay. 650 56:29.880 --> 56:37.900 Because we want the result into two decimal places Write down the answer to two decimal places. 651 56:41.340 --> 56:44.700 And here, if you if you see over here movement users are there. 652 56:44.700 --> 56:45.100 Right. 653 56:45.620 --> 56:48.020 So movement users as users. 654 56:52.180 --> 56:57.780 So so above city has been written over here. 655 56:57.780 --> 56:59.700 And then original table. 656 56:59.700 --> 57:07.300 We have join it with left join and we have user ID will be there in both the tables. 657 57:07.780 --> 57:09.580 That will be common column. 658 57:19.420 --> 57:26.780 So only the only that users will be join over here which has done which is in the movement user. 659 57:28.500 --> 57:31.620 That is why we have taken over here the right date column. 660 57:31.620 --> 57:31.750 Hmm. 661 57:31.990 --> 57:33.790 So this will be right. 662 57:33.790 --> 57:39.910 It will indicate the all those users who have done the right on the registration date. 663 57:42.550 --> 57:52.030 This indicates all the users who has done the right on the. 664 57:54.390 --> 58:00.590 Register date, which means in the moment, users. 665 58:01.110 --> 58:07.270 And now this will be indicating the second ride. 666 58:07.630 --> 58:07.990 This. 667 58:08.710 --> 58:09.910 Or we can say that. 668 58:13.270 --> 58:22.070 This will be indicating the second ride of all the users after the first ride. 669 58:22.430 --> 58:22.870 Now. 670 58:25.230 --> 58:32.850 Now we have simply done over here leg ride date use over here and right now it will be used over here. 671 58:33.210 --> 58:38.250 We have to calculate the difference between these two rights into the. 672 58:38.290 --> 58:40.410 Day day value. 673 58:40.690 --> 58:47.850 And then we have done the average of this all this difference. 674 58:47.890 --> 58:52.130 And then we have done the casting to two decimal places. 675 58:55.330 --> 58:58.490 Now let me know guys this question is clear. 676 58:59.410 --> 59:01.250 Question and solution is clear. 677 59:07.770 --> 59:15.450 So this kind of scenarios you may come across in your day to day work very frequently. 678 59:16.090 --> 59:25.930 While working as a data analyst or data engineer mainly or SQL developer also.