1 00:01.760 --> 00:02.160 Yeah. 2 00:02.160 --> 00:03.000 Hi, everyone. 3 00:03.240 --> 00:06.440 So this is our first query for today. 4 00:08.360 --> 00:15.440 The LinkedIn creator team is looking for the power creators who use their personal profile ID as a company 5 00:15.440 --> 00:17.800 or influencer page. 6 00:18.920 --> 00:26.120 This means that if someone's LinkedIn page has more followers, then all the company they work for, 7 00:26.560 --> 00:30.640 we can safely assume that a person is a power creator. 8 00:31.560 --> 00:37.680 Write a query to return the IDs of this LinkedIn power creators in ascending order. 9 00:38.680 --> 00:40.960 Now these are the assumptions that has been given. 10 00:41.320 --> 00:43.920 A person can work at multiple companies. 11 00:44.440 --> 00:49.960 In case of multiple companies, we need to do some of all the companies follower. 12 00:51.040 --> 00:54.080 So here they have given us the total three tables. 13 00:54.200 --> 01:01.800 This is the first table personal profile table in which profile ID name and followers are given. 14 01:01.800 --> 01:08.760 So I have already created the table and inserted the data into the same then employee company table 15 01:09.680 --> 01:12.000 in which personal profile ID. 16 01:12.000 --> 01:20.600 So if you see over here, this profile ID and personal profile ID is common in both the tables and the 17 01:20.600 --> 01:26.080 company ID in which this individual person is working for. 18 01:28.520 --> 01:35.800 And then again, another table is given is company pages table, in which company ID name and followers 19 01:37.000 --> 01:37.760 are given. 20 01:38.200 --> 01:46.480 So if you see over here in employee company, company ID and company pages, company ID is common column 21 01:46.480 --> 01:48.080 between both the tables. 22 01:51.040 --> 01:54.880 Now let's see the data of all the three tables. 23 01:56.600 --> 02:00.720 So this is the first table which is personal profile table. 24 02:01.280 --> 02:03.840 So here individual followers are given right. 25 02:03.880 --> 02:06.120 Individual person followers are given. 26 02:06.880 --> 02:10.080 Then in the second table which is employee company table. 27 02:11.320 --> 02:20.960 So over here personal individual person is working for which company that is given in the last table 28 02:20.960 --> 02:22.640 is company pages table. 29 02:22.640 --> 02:26.800 So in which individual company wise followers are given. 30 02:26.800 --> 02:38.160 So now over here we need to identify those individual person whose follower is more than whose follower 31 02:38.200 --> 02:39.160 is more than. 32 02:45.400 --> 02:48.960 The company's follower in which they are working for. 33 02:49.840 --> 02:50.360 Right. 34 02:50.400 --> 02:55.600 We need to identify and that person are known as a power creator. 35 02:58.600 --> 02:59.760 Now over here. 36 02:59.760 --> 03:01.000 What we need to do. 37 03:05.640 --> 03:06.720 We need to do. 38 03:10.120 --> 03:12.080 This is the first thing we need to do. 39 03:15.840 --> 03:19.600 We need to identify the individual followers. 40 03:19.600 --> 03:20.080 Right. 41 03:22.120 --> 03:28.360 So if you see over here we need to identify the individual followers first. 42 03:29.000 --> 03:32.160 So for that we need to use personal profile table. 43 03:34.360 --> 03:38.920 So these are the personal profile ID and followers of individual person. 44 03:39.840 --> 03:41.360 Now what we need to do. 45 03:43.680 --> 03:45.000 Now we need to do. 46 03:47.160 --> 03:53.600 If you see over here for personal profile ID one and ten. 47 03:53.600 --> 03:58.120 So if you see over here this three is working for one and ten right now. 48 03:58.120 --> 04:01.440 Let me copy this data into the Excel file. 49 04:28.760 --> 04:29.440 Okay. 50 04:30.680 --> 04:36.440 Now if you see over here personal profile ID three is working for which company? 51 04:36.440 --> 04:37.960 Company ID one and ten. 52 04:38.520 --> 04:41.520 So company ID one means 8000 right. 53 04:42.960 --> 04:45.080 And ten means 734. 54 04:46.960 --> 04:49.760 So here we need to do the summation of these two. 55 04:53.160 --> 04:54.840 This Plus this. 56 04:59.560 --> 05:05.480 And now we need to take the personal profile ID three also. 57 05:05.480 --> 05:09.200 So let me take this data as well. 58 05:16.120 --> 05:17.400 So what is the third. 59 05:17.440 --> 05:18.360 Three follower. 60 05:18.400 --> 05:21.440 Third follower is one lakh 71,000. 61 05:21.960 --> 05:25.440 Is it greater than the summation of these two? 62 05:26.200 --> 05:26.640 No. 63 05:26.640 --> 05:27.200 Right. 64 05:28.560 --> 05:32.680 So it is not a power greater three is not a power creator. 65 05:33.320 --> 05:35.600 Now let me take the example of one. 66 05:36.840 --> 05:40.320 One is working for company ID nine. 67 05:42.240 --> 05:43.680 Any other place one is coming. 68 05:43.680 --> 05:43.880 No. 69 05:43.880 --> 05:44.360 Right. 70 05:51.520 --> 05:52.560 Any other places. 71 05:52.560 --> 05:53.680 One is coming. 72 05:53.680 --> 05:54.040 No. 73 05:54.040 --> 05:55.120 So over here. 74 05:57.800 --> 05:59.160 Once follow over. 75 06:00.640 --> 06:03.600 Individual follower is greater than. 76 06:05.640 --> 06:08.120 The followers of the companies they work for. 77 06:15.560 --> 06:20.200 So over here one is a power creator. 78 06:20.200 --> 06:21.480 We can say that one. 79 06:21.800 --> 06:26.800 And you can say this one is a power creator. 80 06:26.800 --> 06:28.160 Now let's move to two. 81 06:29.440 --> 06:33.640 So two is coming over here one time, then two is coming over here. 82 06:33.680 --> 06:33.920 No. 83 06:33.920 --> 06:34.360 Right. 84 06:35.440 --> 06:38.040 So two is seven lakhs. 85 06:38.040 --> 06:46.680 So two is not a power creator because, uh, one lakh 99,000 is less than seven lakh less than the companies 86 06:46.680 --> 06:48.240 they are working for, right? 87 06:53.640 --> 06:54.800 So now let me. 88 06:55.600 --> 07:00.160 So now over here, we need to do the what we need to do. 89 07:00.200 --> 07:01.680 Can anyone tell me now? 90 07:04.480 --> 07:06.400 We need to use this two table. 91 07:18.920 --> 07:19.840 Inner join. 92 07:31.280 --> 07:33.600 Now, can anyone tell me what we need to do? 93 07:52.800 --> 07:53.320 Hello? 94 08:03.520 --> 08:04.840 Anyone tell me. 95 08:05.040 --> 08:07.160 Uh, tell me what we need to do now. 96 08:10.520 --> 08:12.960 I have explained you in the Excel file, right? 97 08:18.240 --> 08:20.520 You understand the question, right? 98 08:36.360 --> 08:36.840 Hello? 99 08:39.160 --> 08:39.840 Yes, sir. 100 08:39.880 --> 08:40.520 Yes, sir. 101 08:40.960 --> 08:41.440 Yeah, yeah. 102 08:41.480 --> 08:47.400 Now, can you tell me or anyone tell me what we need to do. 103 08:48.200 --> 08:49.240 we need to do? 104 08:54.080 --> 08:54.440 This. 105 08:58.560 --> 09:00.640 Let me tell you. 106 09:03.720 --> 09:12.720 E dot personal profile ID then some of followers as. 107 09:15.320 --> 09:16.400 Company followers. 108 09:16.400 --> 09:16.880 Right. 109 09:17.240 --> 09:18.480 And then group by. 110 09:20.800 --> 09:23.720 We need to do group by personal profile ID right because of. 111 09:24.200 --> 09:26.200 We have used the aggregate function. 112 09:26.200 --> 09:27.800 Now let me run this query. 113 09:34.080 --> 09:35.040 669. 114 09:35.040 --> 09:38.040 This value is coming right in Excel file. 115 09:38.440 --> 09:40.160 Now let me know. 116 09:43.360 --> 09:44.680 These are the two tables. 117 09:44.720 --> 09:47.720 Now What we need to do? 118 09:48.400 --> 09:52.040 Or can anyone tell me what will be our result set? 119 09:59.000 --> 10:00.600 What will be our result set? 120 10:00.600 --> 10:01.520 This is the. 121 10:02.480 --> 10:05.240 Again I am taking this data into Excel file. 122 10:18.680 --> 10:19.800 Now let me know. 123 10:31.120 --> 10:31.880 One. 124 10:32.440 --> 10:34.680 We can say one is a power creator. 125 10:35.920 --> 10:37.720 Then two is not four. 126 10:38.720 --> 10:39.320 Yes, four. 127 10:39.360 --> 10:43.880 We can say power Creator, then five we can say power creator. 128 10:44.240 --> 10:47.040 SM is not seven. 129 10:47.640 --> 10:48.400 Yes, seven. 130 10:48.400 --> 10:51.320 We can say power creator eight is not. 131 10:51.320 --> 10:53.480 Three is not. 132 10:56.120 --> 10:57.280 Now let me know. 133 10:58.760 --> 10:59.840 Is this correct? 134 11:04.440 --> 11:04.880 Yes. 135 11:04.880 --> 11:05.520 Yes sir. 136 11:06.320 --> 11:07.400 This is correct. 137 11:08.000 --> 11:08.440 How? 138 11:10.400 --> 11:11.800 How it is correct. 139 11:15.160 --> 11:16.480 Can you explain? 140 11:19.280 --> 11:20.080 In brief. 141 11:25.320 --> 11:25.600 Sir. 142 11:25.640 --> 11:27.280 One with profile ID. 143 11:28.520 --> 11:34.720 One as an individual is having more followers than as a company profile company. 144 11:36.520 --> 11:37.080 Okay. 145 11:37.400 --> 11:38.000 Yes. 146 11:38.040 --> 11:38.440 So. 147 11:40.560 --> 11:41.160 Okay. 148 11:41.960 --> 11:44.320 So now, Same with the other three. 149 11:44.360 --> 11:45.960 Other three options also. 150 11:46.000 --> 11:52.880 Okay, now from these two queries, we want that data. 151 11:54.360 --> 11:54.960 Yes, sir. 152 11:55.240 --> 11:57.600 Uh, how we can get it? 153 11:59.320 --> 12:01.840 I can say that we can, uh, okay. 154 12:01.880 --> 12:02.880 These two results set. 155 12:02.920 --> 12:04.440 Now, how how we can get it. 156 12:05.080 --> 12:12.000 We can create two cities and compare the join the two cities and give the more than condition in the 157 12:12.000 --> 12:13.280 final query. 158 12:13.800 --> 12:14.720 Perfect. 159 12:17.120 --> 12:18.400 This is the first city. 160 12:19.120 --> 12:20.760 This is the second city. 161 12:24.360 --> 12:27.120 Select star from city. 162 12:28.600 --> 12:30.000 Now over here. 163 12:30.880 --> 12:32.320 We can do inner join. 164 12:32.800 --> 12:33.400 Inner join. 165 12:34.760 --> 12:35.720 Inner join. 166 12:37.040 --> 12:39.160 There is some issue is. 167 12:42.240 --> 12:45.960 Innerjoin g2 on CT dot. 168 12:50.160 --> 12:51.160 Profile id. 169 13:04.320 --> 13:15.760 Is equal to city2 dot right city where now what we need to do? 170 13:20.280 --> 13:21.520 Simple, right? 171 13:24.800 --> 13:31.440 Individual for our is greater than company follower. 172 13:34.560 --> 13:39.360 Now let me take city dot. 173 13:44.080 --> 13:45.120 Profile ID. 174 13:47.360 --> 13:49.200 I want I think this one only. 175 13:49.200 --> 13:50.640 Let me run the query. 176 13:54.400 --> 14:00.720 145145 and 71457. 177 14:00.720 --> 14:01.680 Perfect, right? 178 14:03.320 --> 14:05.120 In Excel file also same thing. 179 14:05.800 --> 14:11.040 We have displayed 14571457. 180 14:17.680 --> 14:23.280 So this is the use case of multiple city right. 181 14:28.440 --> 14:31.680 Now let me know if there is any doubt in this query. 182 14:33.320 --> 14:35.520 Everything is clear right. 183 14:37.240 --> 14:39.040 Or anyone has any doubt. 184 14:50.520 --> 14:51.000 Yes, sir. 185 14:51.000 --> 14:52.680 It's clear, sir. 186 14:53.200 --> 14:53.960 Okay. 187 14:55.560 --> 14:56.920 I mean, that's it. 188 15:00.560 --> 15:02.880 So now let's jump to the next query. 189 15:07.480 --> 15:11.520 Each Facebook user can designate a personal email address. 190 15:12.520 --> 15:13.800 Personal email address. 191 15:14.160 --> 15:16.960 A business email address and a recovery email address. 192 15:17.960 --> 15:21.440 Unfortunately, the table is currently in the wrong format. 193 15:23.560 --> 15:29.320 So you need to transform its structure to show the following columns. 194 15:31.160 --> 15:36.040 User ID personal email, business email and recovery email. 195 15:37.200 --> 15:40.280 Sort your answer by user ID in ascending order. 196 15:41.320 --> 15:44.880 Okay, so this should be our result set. 197 15:45.680 --> 15:50.000 This four column user ID personal email business email and recovery email. 198 15:50.680 --> 15:58.160 So I have created this table create table user underscore emails in that these three columns are given 199 15:58.160 --> 16:00.080 user ID email and email type. 200 16:01.880 --> 16:03.840 Now let me show you the data. 201 16:06.040 --> 16:07.480 User underscore email. 202 16:07.480 --> 16:14.440 So if you see over here these are the table in currently in wrong format. 203 16:15.240 --> 16:25.040 And now our requirement is this one user ID personal email as a one column with the same email as one 204 16:25.040 --> 16:27.120 column and recovery email as a one column. 205 16:27.800 --> 16:29.520 Now how can we get it? 206 16:57.480 --> 16:59.240 So how can we get it? 207 16:59.440 --> 17:01.320 Select star from. 208 17:01.360 --> 17:02.920 I am again writing this. 209 17:07.520 --> 17:09.880 Now if you see the table carefully. 210 17:12.600 --> 17:15.360 We require three columns right. 211 17:15.360 --> 17:16.840 User ID plus three columns. 212 17:16.840 --> 17:18.960 So first column will be user ID. 213 17:23.120 --> 17:29.480 Now if you see the email type is given personal business recovery. 214 17:29.480 --> 17:31.040 Personal business business. 215 17:32.760 --> 17:35.160 So here we need to write the case statement. 216 17:35.160 --> 17:37.200 Case when? 217 17:39.840 --> 17:42.640 Email type is equal to. 218 17:45.880 --> 17:46.640 Personal. 219 17:47.280 --> 17:47.800 Then. 220 17:50.840 --> 17:51.240 Then. 221 17:51.280 --> 17:51.840 Email. 222 18:05.040 --> 18:05.480 As. 223 18:05.520 --> 18:07.360 As personal email. 224 18:11.480 --> 18:12.920 What is wrong in this? 225 18:12.960 --> 18:14.640 Th n right. 226 18:19.080 --> 18:20.440 Now what we need to do. 227 18:24.080 --> 18:26.840 We require business email also. 228 18:26.840 --> 18:29.760 Right then email. 229 18:31.000 --> 18:32.360 Else null. 230 18:32.360 --> 18:33.240 We have to able to write. 231 18:40.760 --> 18:42.360 And then over here. 232 18:42.400 --> 18:44.000 Third one is recover email. 233 18:44.000 --> 18:44.400 Right. 234 19:02.520 --> 19:04.920 Okay so we got the three column. 235 19:04.920 --> 19:06.600 But but what is wrong over here. 236 19:06.640 --> 19:07.840 Can anyone tell me. 237 19:13.440 --> 19:14.080 Yes, sir. 238 19:14.480 --> 19:17.080 It is coming in different, different rows. 239 19:17.080 --> 19:18.280 It should come in one row. 240 19:18.560 --> 19:19.720 Okay, perfect. 241 19:19.720 --> 19:21.080 It should come in one row. 242 19:25.280 --> 19:27.160 Now for that, what we need to do. 243 19:32.200 --> 19:37.400 Means it means one, two, three, one row will be there in all three email addresses should be there. 244 19:37.840 --> 19:46.240 Then 2341 row will be there and respective email address should be there and 789 also one row will be 245 19:46.240 --> 19:48.880 there in respective email address should be there. 246 19:49.240 --> 19:50.840 That is our requirement for that. 247 19:50.880 --> 19:52.080 What we need to do. 248 20:00.000 --> 20:01.400 Simple thing right. 249 20:02.360 --> 20:04.400 We need to use aggregate function. 250 20:06.880 --> 20:08.120 So let me do that. 251 20:08.160 --> 20:09.840 Here we can use Max. 252 20:12.360 --> 20:13.280 Max. 253 20:15.440 --> 20:16.200 Max. 254 20:17.840 --> 20:18.520 And then. 255 20:20.880 --> 20:22.120 Close this bracket. 256 20:22.120 --> 20:23.200 Close this bracket. 257 20:23.200 --> 20:24.360 Close this bracket. 258 20:31.480 --> 20:33.680 Group by user ID. 259 20:39.600 --> 20:40.280 What is wrong? 260 20:40.280 --> 20:40.880 Over here? 261 20:59.320 --> 21:00.320 Perfect, right. 262 21:01.480 --> 21:03.040 Now, let me end of the line. 263 21:03.080 --> 21:06.240 I think that, uh, end of the line. 264 21:06.240 --> 21:08.400 I think personal email is there in every line. 265 21:09.120 --> 21:09.920 Okay, okay, okay. 266 21:09.960 --> 21:15.160 Personal email here we can say business email, right? 267 21:15.400 --> 21:16.000 Yes, sir. 268 21:19.560 --> 21:21.400 And then over here we can say. 269 21:25.040 --> 21:26.120 Recovery email. 270 21:27.280 --> 21:32.480 Now if I execute this one, we will be getting perfect result. 271 21:35.040 --> 21:37.120 Now let me execute this query. 272 21:40.440 --> 21:47.560 So if you see over here this 234 has personal and business email only right. 273 21:47.600 --> 21:48.280 234. 274 21:49.040 --> 21:49.920 It has recovery. 275 21:49.920 --> 21:51.080 Email is not present. 276 21:51.080 --> 21:52.600 So it is over here null. 277 21:54.920 --> 21:57.600 Again 789 only business email is there. 278 21:57.680 --> 22:00.760 No personal email and no recovery email. 279 22:00.760 --> 22:01.760 So it is null. 280 22:02.080 --> 22:03.640 So that is perfect right? 281 22:04.040 --> 22:06.400 Per user we are getting only one row. 282 22:06.920 --> 22:09.840 And for this 123 personal business and recovery. 283 22:09.840 --> 22:12.040 All three emails are present. 284 22:12.920 --> 22:16.080 So 123 is all the three emails IDs are there. 285 22:20.320 --> 22:21.960 So now you got it right. 286 22:21.960 --> 22:22.920 This solution. 287 22:29.520 --> 22:30.240 Previously. 288 22:30.240 --> 22:37.160 Multiple rows are coming for one user ID, but we require only one row per user ID, right? 289 22:38.560 --> 22:44.400 Because of that, we have done the maximum over here we can do minimum also. 290 22:45.400 --> 22:46.560 That is also fine. 291 22:47.520 --> 22:48.480 Let me do it. 292 22:51.080 --> 22:53.560 Because both will work in the same way right. 293 22:56.040 --> 22:57.840 But can we do over here some. 294 22:58.920 --> 23:00.240 Can anyone let me know. 295 23:02.120 --> 23:03.720 Some is possible or not. 296 23:07.720 --> 23:10.080 Will it work or it will generate an error? 297 23:18.280 --> 23:18.760 Okay. 298 23:18.800 --> 23:19.600 Not possible. 299 23:19.600 --> 23:21.800 Let me try to execute. 300 23:22.920 --> 23:26.240 Operand data type is invalid for some operator. 301 23:27.840 --> 23:28.200 Right. 302 23:28.200 --> 23:34.480 So it is not possible, but maximum and minimum is possible because maximum and minimum will be working 303 23:34.480 --> 23:36.920 for varchar data type. 304 23:36.920 --> 23:37.320 Right. 305 23:41.680 --> 23:45.240 Now let me jump to the next query. 306 23:45.520 --> 23:46.680 This is clear right. 307 23:57.920 --> 23:58.320 Okay. 308 23:58.320 --> 24:02.720 Let me this is actually a very long description. 309 24:07.040 --> 24:14.480 So assuming Salesforce operator on a per user pricing model we have a table containing contracts data. 310 24:16.760 --> 24:17.120 Right. 311 24:17.120 --> 24:24.080 A query to calculate the average annual revenue per Salesforce customer in three market segments. 312 24:24.760 --> 24:30.160 So there are three different market segments SMB mid Market segment, and enterprise. 313 24:30.680 --> 24:36.200 Each customer is represented by a single contract format. 314 24:36.200 --> 24:40.080 The output to match the structure shown in the example output section below. 315 24:40.880 --> 24:43.640 The market segments are categorized as. 316 24:46.280 --> 24:47.000 SMB. 317 24:47.440 --> 24:51.080 So when less than one 100 employees are then. 318 24:51.640 --> 24:54.160 Then we can say that SMB market segment. 319 24:54.680 --> 24:59.280 When 100 to 9 nine nine employees are there, then we can say mid market segment. 320 24:59.800 --> 25:05.680 And when greater than 1000 or equal to 1000 are there then enterprise market segment. 321 25:07.680 --> 25:15.160 The terms average deal size and average revenue refer to the same concept, which is average average 322 25:15.200 --> 25:19.840 annual revenue generated per customer in each market segment. 323 25:27.480 --> 25:27.880 Yeah. 324 25:27.880 --> 25:28.280 Hello? 325 25:30.840 --> 25:31.120 Yeah. 326 25:35.920 --> 25:37.280 Actually, not able to. 327 25:39.440 --> 25:40.600 Clearly audible. 328 25:47.240 --> 25:49.800 Please be on mute, everyone. 329 25:56.560 --> 25:58.360 So this is the explanation. 330 25:59.000 --> 25:59.960 SMB average. 331 26:00.000 --> 26:01.320 SMB average revenue. 332 26:01.360 --> 26:04.280 How we can identify over here. 333 26:04.400 --> 26:08.800 So now let me directly show you the table structure. 334 26:13.360 --> 26:18.560 Here we have two different tables are given customers table and contracts table. 335 26:18.560 --> 26:20.960 So let me show you the tables. 336 26:24.400 --> 26:31.400 So if you see over here customer ID name and employee count is given, and then the same customer ID 337 26:31.400 --> 26:33.000 is given into the customer. 338 26:33.200 --> 26:37.560 Another contracts table in which number of sales and yearly. 339 26:40.360 --> 26:41.840 Seat cost is given. 340 26:42.000 --> 26:45.680 So now if you see over here the formula is given over here. 341 26:49.920 --> 26:50.960 Number of seats. 342 26:50.960 --> 26:53.920 Multiply multiply by early seat cost. 343 26:54.400 --> 26:56.120 So that is our formula. 344 27:00.920 --> 27:03.680 Let me write it over here. 345 27:07.000 --> 27:08.600 Or you can say that num. 346 27:11.640 --> 27:12.320 Sits. 347 27:20.760 --> 27:23.800 Number of seats multiplied by early seat cost. 348 27:24.200 --> 27:24.720 Right. 349 27:25.200 --> 27:28.760 So multiply by. 350 27:34.080 --> 27:34.800 Cost. 351 27:36.800 --> 27:41.120 And then we need to do divide by distinct customer ID. 352 27:44.120 --> 27:46.120 So divide by. 353 27:52.320 --> 27:54.680 So this is our basically main formula. 354 27:54.680 --> 27:57.840 We have to apply for each market segment. 355 27:58.040 --> 28:03.560 So if you see over here I have written this is the contract table. 356 28:03.560 --> 28:04.920 And this is the customer table. 357 28:04.920 --> 28:11.400 We have done the inner join with customer ID column is common in both the tables right customer ID column 358 28:12.120 --> 28:12.760 and now. 359 28:15.120 --> 28:17.000 Employee count less than 100. 360 28:17.000 --> 28:18.880 So over here three different buckets. 361 28:18.880 --> 28:22.120 Are there less than 100 employees? 362 28:22.120 --> 28:30.520 Then it will be SMB 100 to 999, then mid-market and greater than or equal to 1000 then enterprise. 363 28:33.520 --> 28:35.640 So over here what I have done. 364 28:39.280 --> 28:41.960 Employee count less than 100. 365 28:42.000 --> 28:45.840 We are we are taking right now what we need to do. 366 28:47.760 --> 28:52.800 We need to do the sum of these two values number of seats and yearly cost. 367 28:52.840 --> 28:56.120 Multiply by this and then we need to do some. 368 29:01.600 --> 29:02.240 Right. 369 29:02.240 --> 29:11.480 So that I have done in the below one sum of sum of number of seats and yearly cost divided by count 370 29:11.480 --> 29:12.680 of distinct customer. 371 29:12.680 --> 29:16.080 So over here customer is one only right? 372 29:16.080 --> 29:17.280 So let me. 373 29:22.320 --> 29:22.840 Okay. 374 29:23.320 --> 29:30.200 So we can see that if you run this first query, what is the sum of these two. 375 29:33.080 --> 29:36.520 We have to do some of number of seats multiply by early seat cost. 376 29:36.560 --> 29:37.040 Right. 377 29:39.280 --> 29:45.920 So sum is equal to 2475. 378 29:45.960 --> 29:46.400 Right. 379 29:47.240 --> 29:50.120 And then what are the distinct customer over here. 380 29:50.880 --> 29:53.040 So I have given the alias over here. 381 29:53.040 --> 29:57.080 If you see this customer ID less than 100. 382 29:57.400 --> 29:58.640 So 2690. 383 29:58.680 --> 30:00.520 Only one customer are there right. 384 30:01.000 --> 30:07.080 So divided by one then 2475 will be our result set. 385 30:11.160 --> 30:12.280 2475. 386 30:12.320 --> 30:12.720 Right. 387 30:16.120 --> 30:16.680 Now. 388 30:16.720 --> 30:17.360 Same thing. 389 30:18.160 --> 30:19.960 Same thing we have to do for. 390 30:22.760 --> 30:32.760 Second bucket which is mid market segment and in which mid market segment we can consider as a greater 391 30:32.760 --> 30:35.960 than equal to 100 and less than equal to 999. 392 30:36.640 --> 30:44.680 So again we have to do some of number of seats multiply by early cost and then distinct customer. 393 30:45.160 --> 30:46.680 So if you see over here. 394 30:49.080 --> 30:50.680 Here distinct customer are two. 395 30:50.720 --> 30:51.080 Right. 396 30:51.080 --> 30:53.160 So we need to do some of all this. 397 30:54.160 --> 30:56.200 Let me write it over here. 398 30:59.520 --> 31:00.920 We need to do some right. 399 31:01.200 --> 31:02.760 So this is the sum. 400 31:03.440 --> 31:04.400 And now two. 401 31:04.560 --> 31:05.000 So. 402 31:07.040 --> 31:08.880 This divided by two are. 403 31:18.080 --> 31:23.200 So I have taken ten two over here. 404 31:24.120 --> 31:26.680 This divided by two. 405 31:28.440 --> 31:31.240 So 34450 will be our result set. 406 31:31.800 --> 31:33.520 So let me execute now. 407 31:33.880 --> 31:35.560 Now what I have done over here. 408 31:38.680 --> 31:40.560 Let me run again these two query. 409 31:41.200 --> 31:45.560 So I have done the sum of all these four and then divide it by two. 410 31:45.920 --> 31:47.440 So 34450. 411 31:47.480 --> 31:48.920 That is coming over here only. 412 31:48.920 --> 31:49.320 Right. 413 31:49.800 --> 31:51.280 34450. 414 31:53.040 --> 31:56.760 And now same thing we have to do for another one. 415 31:59.760 --> 32:07.800 But now let me know I require let me just write this one. 416 32:12.280 --> 32:13.120 Over here. 417 32:13.120 --> 32:15.600 We need to write greater than or equal to 1000. 418 32:15.640 --> 32:16.040 Right. 419 32:32.320 --> 32:36.360 Now let me try to do again this some of these four values. 420 32:41.560 --> 32:42.520 What is the sum. 421 32:43.240 --> 32:44.400 This is the sum right. 422 32:44.760 --> 32:48.520 And divide it by distinct customer ID one and two. 423 32:51.520 --> 32:55.280 So equal to this divided by this 424 32:56.880 --> 33:01.400 131287.5131287. 425 33:02.360 --> 33:03.280 Because of, we have. 426 33:03.280 --> 33:07.880 Consider it as an integer 0.5 is automatically removed. 427 33:10.160 --> 33:11.450 Now let me know. 428 33:13.450 --> 33:15.050 This is our second query. 429 33:18.850 --> 33:21.450 This is our third query. 430 33:25.250 --> 33:26.290 And this is our. 431 33:29.410 --> 33:34.250 First query for SMB mid-market and. 432 33:38.170 --> 33:38.810 Enterprise. 433 33:38.810 --> 33:39.010 Right. 434 33:39.050 --> 33:40.170 We have three queries. 435 33:44.930 --> 33:45.970 This is SMB. 436 33:46.130 --> 33:49.650 This is mid-market and this is enterprise. 437 33:52.650 --> 33:56.530 Now let me know how we can join all these three queries. 438 34:02.730 --> 34:05.770 Or how we can get the result of all these three queries. 439 34:08.210 --> 34:09.290 This one we want. 440 34:09.330 --> 34:11.690 But into the separate three columns. 441 34:21.210 --> 34:22.610 How can we get it? 442 34:39.890 --> 34:40.250 Here. 443 34:40.250 --> 34:40.810 Anyone? 444 34:49.130 --> 34:53.530 We want all these three values, but as a separate three column. 445 34:54.490 --> 34:56.130 How can we get it? 446 35:03.090 --> 35:04.690 It's very simple, guys. 447 35:15.170 --> 35:15.490 I don't. 448 35:28.650 --> 35:29.610 Know how y'all. 449 35:38.890 --> 35:39.290 Yeah. 450 35:39.290 --> 35:40.090 Hello, guys. 451 35:42.610 --> 35:43.570 Simple thing. 452 35:43.570 --> 35:43.850 Right? 453 35:43.890 --> 35:47.570 We need to create three different city. 454 35:52.250 --> 35:54.890 This is the first city, then city two as. 455 36:01.810 --> 36:03.010 In city three. 456 36:04.410 --> 36:05.210 Three as. 457 36:12.490 --> 36:17.330 Now select star from city. 458 36:20.370 --> 36:20.970 Comma. 459 36:21.530 --> 36:22.770 City two. 460 36:22.810 --> 36:23.250 Comma. 461 36:23.290 --> 36:24.330 City three. 462 36:25.330 --> 36:27.850 Now let me execute this. 463 36:31.690 --> 36:32.010 Man. 464 36:42.930 --> 36:43.610 Now this. 465 36:43.610 --> 36:46.090 All these three values are coming as a column, right? 466 36:50.010 --> 36:53.130 Till now we we are using two cities only. 467 36:54.930 --> 36:59.810 But this is the use case of more than two city. 468 36:59.850 --> 37:03.650 Or we can say that three cities we have used over here. 469 37:07.290 --> 37:09.330 Actually, this is a very lengthy question. 470 37:09.330 --> 37:14.890 You can I will give you the description and create an insert table script. 471 37:16.170 --> 37:18.930 You can this is the formula actually. 472 37:18.970 --> 37:21.850 Formula is already given by them. 473 37:21.850 --> 37:24.450 So we have to use the formula only right. 474 37:25.730 --> 37:29.410 We have to understand the formula and we have to use it as a query. 475 37:33.450 --> 37:36.090 Now everything is clear for this question. 476 37:58.690 --> 37:59.010 Okay. 477 37:59.010 --> 38:01.170 Let me jump to the next question. 478 38:08.010 --> 38:10.690 So this is regarding to year over year growth. 479 38:13.330 --> 38:17.130 So it is very frequently asked in power BI. 480 38:17.170 --> 38:21.690 Also we can use this one then in SQL also we can use. 481 38:23.370 --> 38:26.330 So this is the description. 482 38:26.330 --> 38:33.250 Assume you are given a table containing information about Wayfair user transactions for different products. 483 38:34.370 --> 38:39.850 Write a query to calculate the year on year growth rate for the total spend on each product. 484 38:40.850 --> 38:48.570 Write a query to calculate the year on year or year over year growth rate for the total spend on each 485 38:48.570 --> 38:49.250 product. 486 38:49.730 --> 38:58.410 Grouping the results by product ID the output should include this year in ascending order. 487 38:59.610 --> 39:00.730 Product ID. 488 39:05.090 --> 39:09.490 Then current year spend in previous years spend and year on year growth percentage. 489 39:13.330 --> 39:18.050 So this is again the formula formula for year over year growth is over here. 490 39:19.690 --> 39:24.410 Current year revenue minus previous year. 491 39:24.410 --> 39:29.930 Revenue divided by previous year revenue multiplied by 100. 492 39:30.890 --> 39:38.730 So anyway if you found this in the Google you will search it in the Google year over year formula is 493 39:38.730 --> 39:39.370 standard. 494 39:40.090 --> 39:43.290 This is the standard formula for year over year growth. 495 39:44.810 --> 39:48.810 So in which current year revenue minus previous year revenue. 496 39:48.850 --> 39:55.130 What is the previous year revenue divided by previous year revenue multiplied by 100. 497 39:55.130 --> 40:03.090 So this is the table transaction ID product ID spend and Transaction date column is there. 498 40:04.730 --> 40:05.250 Right. 499 40:07.210 --> 40:07.930 One minute. 500 40:12.250 --> 40:18.690 So this is the user transaction table is given transaction ID product ID price and spend and transaction 501 40:18.690 --> 40:19.050 date. 502 40:19.050 --> 40:19.530 Right. 503 40:20.530 --> 40:22.050 I have created the table. 504 40:22.850 --> 40:25.210 Let me show you the data. 505 40:37.730 --> 40:49.410 So if you see over here product ID is given, transaction date is given and spend is given in one product 506 40:49.410 --> 40:51.730 ID is coming multiple times right. 507 40:52.450 --> 40:56.810 Multiple times this product ID is also coming multiple times. 508 40:56.810 --> 40:58.210 This product ID is called. 509 40:58.730 --> 40:59.530 So here. 510 40:59.890 --> 41:05.810 Transaction date is 2019 2020 2021 2022. 511 41:05.930 --> 41:09.210 Again 1920 2021 2022. 512 41:10.130 --> 41:13.530 So here we need to spend. 513 41:14.250 --> 41:16.210 Spend is also given right. 514 41:16.650 --> 41:21.090 But now what is our additional column is over here. 515 41:21.090 --> 41:22.090 It is required. 516 41:29.370 --> 41:33.090 We need to find previous year cell right. 517 41:35.330 --> 41:39.490 Because for our formula purpose it is required. 518 41:39.490 --> 41:39.970 Right. 519 41:40.650 --> 41:44.770 So over here I have taken the transaction date from the transaction date. 520 41:45.290 --> 41:47.930 Our requirement is year only right here. 521 41:48.490 --> 41:53.330 So what I have done here of transaction date I have taken over here. 522 41:54.610 --> 41:59.370 It means it will give me 2019 19, 21, 22 and 23. 523 42:00.250 --> 42:03.090 Now this is the Spanish current year spend. 524 42:05.490 --> 42:07.610 This is the spend is current year spend. 525 42:07.770 --> 42:10.490 But I also want previous year spend right. 526 42:11.490 --> 42:18.090 So what I have done over here if you see the lag function, lead and lag are the SQL functions. 527 42:18.930 --> 42:28.370 Lag is giving us the previous year data or previous raw data, whereas lead is giving next raw data. 528 42:28.810 --> 42:32.610 So I have over here use the lag function. 529 42:35.530 --> 42:41.170 Lack of spend over partition by product ID we require right. 530 42:44.650 --> 42:46.370 Partition by product ID. 531 42:51.610 --> 42:54.170 And then order by year of transaction date. 532 42:58.610 --> 43:01.210 Then only we will get previous year data, right. 533 43:01.850 --> 43:04.010 Order by year of transaction date. 534 43:05.330 --> 43:05.890 Now. 535 43:08.370 --> 43:12.850 So this is the previous year spend we are getting over here because of spend. 536 43:12.890 --> 43:18.770 We want iOS specify in the bracket leg of spend of one. 537 43:21.730 --> 43:24.250 Because each year data we want right. 538 43:24.290 --> 43:25.850 Each year spend we want. 539 43:28.770 --> 43:30.050 Now over here. 540 43:33.730 --> 43:34.890 What I have done. 541 43:38.770 --> 43:41.250 This is the main thing you have to look for. 542 43:42.930 --> 43:44.330 So what is the formula? 543 43:44.970 --> 43:46.250 Current year spend. 544 43:46.650 --> 43:50.570 This is the spend means this spend is the current year spend. 545 43:50.570 --> 43:50.970 Right. 546 43:51.650 --> 43:53.010 This spend is over here. 547 43:53.010 --> 43:54.210 I have written over here. 548 43:54.810 --> 43:56.410 And this is current year span. 549 43:56.610 --> 43:59.410 So current year span minus previous year spend. 550 44:00.010 --> 44:05.850 So these are the entire thing is considered as a previous year spend right. 551 44:05.890 --> 44:14.330 This entire thing is considered as a previous spend the current year spend minus previous year spend. 552 44:14.770 --> 44:22.450 So I have done over written over here previous spend right now divided by previous year spend. 553 44:22.770 --> 44:31.170 So same thing I have written over here divided by previous years spend and then multiply by 100. 554 44:31.170 --> 44:32.250 We have to do write. 555 44:34.690 --> 44:36.050 Multiply by 100. 556 44:36.090 --> 44:36.650 And then. 557 44:40.570 --> 44:45.450 We have done round the result to two decimal places. 558 44:45.770 --> 44:47.690 So let me execute the data. 559 44:59.770 --> 45:04.650 So if you see over here, this is the current year spend right 2019. 560 45:05.130 --> 45:08.930 Then in 2020 this is the current year spend. 561 45:09.170 --> 45:13.610 But what is the what is the previous year spend. 562 45:14.130 --> 45:21.290 This is 2019 is the previous year spend 2019 is the previous year spend. 563 45:21.850 --> 45:26.690 So if you see over here 101,000. 564 45:31.250 --> 45:36.010 1000 minus current year spend. 565 45:36.010 --> 45:39.050 Minus previous years spend. 566 45:41.130 --> 45:43.290 Divided by previous air spend. 567 45:59.410 --> 46:01.610 Now again, let me execute this query. 568 46:06.290 --> 46:07.810 Now you got the result right. 569 46:07.810 --> 46:09.530 Whatever you are wanting. 570 46:10.930 --> 46:13.650 And this one we need to do multiply by 100. 571 46:15.250 --> 46:17.410 So let me again execute this query. 572 46:18.210 --> 46:20.850 This is the actual result I am writing over here. 573 46:21.730 --> 46:22.410 And then. 574 46:26.530 --> 46:28.970 This is the same thing you are getting over here right. 575 46:33.290 --> 46:36.290 33.35 same thing. 576 46:36.290 --> 46:37.530 You are getting over here. 577 46:38.090 --> 46:40.770 But now I don't want this much of digit. 578 46:41.170 --> 46:43.410 I want 33.035. 579 46:43.450 --> 46:45.890 Only then what I can do. 580 46:46.290 --> 46:48.210 Can anyone tell me in this column? 581 46:55.930 --> 46:56.410 Today. 582 46:56.410 --> 46:58.810 You are all very silent, guys. 583 47:07.810 --> 47:09.850 So, uh, simple thing. 584 47:09.850 --> 47:10.290 Right? 585 47:10.290 --> 47:11.730 What we need to do, we can do. 586 47:11.770 --> 47:12.330 Cast. 587 47:14.370 --> 47:15.490 Cast of this. 588 47:19.290 --> 47:22.370 Decimal four, comma two. 589 47:22.410 --> 47:24.770 We can write and then close the bracket. 590 47:25.930 --> 47:28.090 Now, let me execute this query. 591 47:33.810 --> 47:34.930 We are getting right. 592 47:35.450 --> 47:40.250 All this zero are removed now which is not necessary. 593 47:40.250 --> 47:40.650 Right. 594 47:42.250 --> 47:45.570 So these are the year over year growth again for this row. 595 47:45.570 --> 47:49.530 If you see then 1000 this divided by this. 596 47:50.690 --> 47:52.690 So if you write it over here. 597 47:56.850 --> 47:57.890 You can okay. 598 47:57.930 --> 48:00.130 UK we have to write this one over here. 599 48:04.650 --> 48:06.130 This one divided by. 600 48:10.650 --> 48:11.290 This one. 601 48:11.290 --> 48:13.050 Then this one. 602 48:13.050 --> 48:14.290 Multiply by 100. 603 48:15.330 --> 48:19.410 It should give 24.62. 604 48:21.570 --> 48:22.090 Rounding. 605 48:22.090 --> 48:22.530 Right. 606 48:27.370 --> 48:28.530 A yes, Chinmoy. 607 48:29.970 --> 48:30.530 Yes, sir. 608 48:30.650 --> 48:39.650 Sir, if we are dividing the number, then when we are dividing the number number, then why come we 609 48:39.650 --> 48:42.250 again have to cast it to decimal again? 610 48:43.770 --> 48:44.890 No, no, actually. 611 48:47.490 --> 48:47.850 Yes. 612 48:47.890 --> 48:48.330 Round. 613 48:48.330 --> 48:49.770 You already have done round. 614 48:50.250 --> 48:50.850 Yeah I have. 615 48:51.010 --> 48:51.850 It is integer. 616 48:52.330 --> 48:54.810 It is an integer integer format. 617 48:57.090 --> 48:59.010 Let me remove this one again. 618 49:01.650 --> 49:02.170 Okay. 619 49:02.210 --> 49:03.690 This is actually we have done. 620 49:04.770 --> 49:05.490 Yes, yes. 621 49:05.530 --> 49:06.730 What is the wrong. 622 49:13.170 --> 49:14.210 Is it this. 623 49:18.530 --> 49:19.050 Okay. 624 49:19.650 --> 49:22.530 Now if you see this, uh, data. 625 49:23.970 --> 49:27.330 Now, these are so many digits are coming, right? 626 49:27.370 --> 49:30.690 Yes, yes, but we want to remove those those digits. 627 49:31.730 --> 49:33.610 And this is decimal value, right. 628 49:33.770 --> 49:37.930 33.35144 .22. 629 49:38.370 --> 49:40.370 These all are the decimal value, right. 630 49:40.770 --> 49:41.250 Yes. 631 49:41.930 --> 49:45.410 But but here result is a perfect one. 632 49:45.810 --> 49:48.010 but these zeros are extra, right? 633 49:48.010 --> 49:50.170 That is not our requirement. 634 49:51.330 --> 49:52.250 We want after. 635 49:52.290 --> 49:55.090 Point we want only two digit two digits. 636 49:55.090 --> 49:55.890 Yes two digits. 637 49:55.890 --> 49:57.890 So that's why I have done the casting. 638 49:58.370 --> 50:06.930 Casting into decimal cast is decimal because of we want in point right. 639 50:07.530 --> 50:11.690 So that five comma two after. 640 50:11.730 --> 50:13.850 Point we want two digit only right. 641 50:14.250 --> 50:14.690 Yes. 642 50:14.690 --> 50:15.050 Yes. 643 50:15.450 --> 50:18.250 So that uh I have done two. 644 50:18.290 --> 50:19.370 But if we done. 645 50:20.050 --> 50:21.970 Something is wrong over here. 646 50:27.370 --> 50:30.210 And if we do three, then it will give me three digits. 647 50:30.250 --> 50:30.810 Three digits. 648 50:30.930 --> 50:33.570 It will give zero also one zero only. 649 50:34.290 --> 50:35.930 Now there are so many zeros. 650 50:35.930 --> 50:37.130 But now it will give. 651 50:38.410 --> 50:39.810 Something is wrong. 652 50:41.370 --> 50:46.930 So we have to do Require two digit only because zero y. 653 50:47.210 --> 50:48.690 What is the meaning of zero? 654 50:48.730 --> 50:49.010 No. 655 50:49.010 --> 50:49.810 Meaning right? 656 50:52.450 --> 50:54.490 So now it is giving the perfect result. 657 50:56.890 --> 50:58.010 Now the. 658 50:58.050 --> 50:59.650 We cast it into decimal. 659 50:59.770 --> 51:01.890 But what was the previous format? 660 51:04.690 --> 51:08.490 Yeah, actually previous format is also decimal only. 661 51:08.970 --> 51:09.810 Okay okay. 662 51:09.850 --> 51:12.490 Previous format is also decimal only because. 663 51:13.090 --> 51:16.250 But there are so many zeros are coming. 664 51:16.250 --> 51:16.730 Right? 665 51:16.970 --> 51:17.730 Right right right. 666 51:17.770 --> 51:18.770 But we got it. 667 51:18.770 --> 51:19.330 Got it. 668 51:19.370 --> 51:19.650 Yes. 669 51:19.690 --> 51:20.130 Got it. 670 51:20.290 --> 51:20.650 Yeah. 671 51:20.650 --> 51:24.050 We want to remove those zero and we want only two digit. 672 51:25.170 --> 51:27.970 So that is why again we casting into decimal. 673 51:28.570 --> 51:28.890 Okay. 674 51:28.930 --> 51:29.250 Got it. 675 51:29.250 --> 51:29.970 Got it sir. 676 51:31.050 --> 51:31.730 Okay. 677 51:34.730 --> 51:36.690 Now this entire query is clear. 678 51:36.690 --> 51:37.050 Right? 679 51:37.090 --> 51:37.570 Entire. 680 51:37.570 --> 51:38.130 This one. 681 51:40.210 --> 51:40.690 Actually. 682 51:40.690 --> 51:42.130 Formula year over year. 683 51:42.130 --> 51:44.850 Hair growth formula is mainly important. 684 51:44.850 --> 51:49.770 If you don't know the formula, then you cannot write this query. 685 51:52.570 --> 51:57.770 And if you know the formula, then how we can find the previous year cell. 686 51:58.210 --> 52:05.850 So for that lag function is used lag will be giving us the previous year cell. 687 52:06.370 --> 52:11.570 And if we are using the lead over here then lead will be giving us the next year cell. 688 52:13.810 --> 52:16.970 But our requirement is to find the previous cell. 689 52:17.490 --> 52:19.810 So we have used the lag function over here. 690 52:20.130 --> 52:24.370 So this is the first part lag function. 691 52:24.410 --> 52:26.210 Then what are you doing over here. 692 52:26.610 --> 52:28.930 I have just done the multiply by 100. 693 52:29.730 --> 52:31.930 And now this is the final formula. 694 52:33.370 --> 52:35.810 This two line is the final formula. 695 52:36.130 --> 52:37.370 Current year cell. 696 52:38.090 --> 52:39.810 Minus previous year cell. 697 52:39.810 --> 52:43.170 This is the previous year Cell then divided by. 698 52:43.210 --> 52:47.690 I have written over here divided by divided by previous year cell. 699 52:47.690 --> 52:54.050 So again same formula multiply by 100 and then rounding of two. 700 52:57.890 --> 53:05.930 And then after at the end we are doing the casting because we want after point two digit only. 701 53:11.290 --> 53:14.810 So always divide your problem into multiple parts. 702 53:16.090 --> 53:19.610 Then only we will able to write the solution. 703 53:20.810 --> 53:23.410 Now this is the final query for today. 704 53:28.370 --> 53:36.050 In an effort to identify high value customers, Amazon asked for your help to obtain data about users 705 53:36.050 --> 53:38.690 who go on shopping sprees. 706 53:39.290 --> 53:44.850 A shopping spree occurs when a user makes a purchase on three or more consecutive days. 707 53:45.930 --> 53:51.810 List the user ID who have gone on at least one shopping spree in ascending order. 708 53:51.850 --> 53:57.930 Okay, so these are the transaction table is given user ID amount in purchase date. 709 53:57.970 --> 54:03.890 I have already created the table and inserted the data into the same. 710 54:07.290 --> 54:09.010 Now what is our requirement? 711 54:10.290 --> 54:18.050 We want to identify those users who have done the purchase on three or more consecutive days. 712 54:19.810 --> 54:22.970 So what is our what will be our answer over here? 713 54:26.930 --> 54:34.450 From this data to two I think 2567567. 714 54:34.450 --> 54:34.930 Okay. 715 54:34.970 --> 54:38.530 Any other uh, five. 716 54:38.930 --> 54:40.650 Also 28 and 30. 717 54:40.690 --> 54:41.330 Also five. 718 54:41.530 --> 54:43.690 Okay, two and five will be our answer. 719 54:44.690 --> 54:47.050 Right now, how can we. 720 54:51.610 --> 54:53.090 Write the solution. 721 55:06.170 --> 55:06.570 Okay. 722 55:06.570 --> 55:06.970 Let me. 723 55:07.010 --> 55:08.450 Don't take the amount. 724 55:11.530 --> 55:13.890 How can we write the solution for this? 725 55:15.570 --> 55:17.490 Two and five will be our answer. 726 55:17.890 --> 55:19.370 User ID two and five. 727 55:19.410 --> 55:20.650 That is our requirement. 728 55:21.010 --> 55:22.690 Now how can we get it? 729 55:30.530 --> 55:31.450 Any idea? 730 55:40.530 --> 55:42.210 So if you see over here. 731 55:42.250 --> 55:42.610 Two. 732 55:46.170 --> 55:47.210 And five. 733 55:50.770 --> 55:53.570 What is the minimum date of two? 734 55:56.730 --> 55:58.450 It is this one, right? 735 55:58.490 --> 55:58.930 Five. 736 55:58.970 --> 55:59.410 Eight. 737 55:59.890 --> 56:00.450 Five. 738 56:00.690 --> 56:01.090 Five. 739 56:01.130 --> 56:03.130 And what is the maximum date of two? 740 56:04.050 --> 56:04.610 Seven. 741 56:05.290 --> 56:06.370 What is the gap? 742 56:06.410 --> 56:09.130 What is the difference between minimum and maximum? 743 56:09.530 --> 56:09.890 Two. 744 56:12.170 --> 56:12.890 One minute. 745 56:22.610 --> 56:23.050 Okay. 746 56:23.090 --> 56:25.330 Two are two. 747 56:25.410 --> 56:28.250 What is the minimum date is five and seven. 748 56:28.250 --> 56:31.610 What is the difference between these two dates? 749 56:33.730 --> 56:35.610 Minimum date and maximum date. 750 56:37.610 --> 56:39.330 Sir, your screen is not shared. 751 56:51.530 --> 56:53.490 Now you are able to visible. 752 56:53.890 --> 56:54.210 Yes. 753 56:54.210 --> 56:54.690 Yes, sir. 754 56:57.370 --> 56:59.730 Is a differences to coming okay. 755 56:59.730 --> 57:00.290 Difference. 756 57:00.290 --> 57:01.370 Difference is two. 757 57:01.970 --> 57:03.730 And again what is for five. 758 57:06.170 --> 57:07.490 Same difference is two. 759 57:07.650 --> 57:08.970 Difference is two right. 760 57:09.490 --> 57:11.610 So now this we need to identify. 761 57:11.650 --> 57:16.930 Now let me write the query maximum date. 762 57:16.970 --> 57:18.570 Maximum purchase date. 763 57:22.170 --> 57:22.610 Comma. 764 57:22.650 --> 57:25.170 Minimum purchase date. 765 57:27.490 --> 57:29.210 Group by user ID. 766 57:37.330 --> 57:37.890 Right. 767 57:37.930 --> 57:42.210 And now I have to take the R minus. 768 57:48.570 --> 57:49.930 Date diff, I think. 769 57:51.570 --> 57:54.170 Uh, let me try if it is working. 770 57:54.210 --> 57:55.450 No no, no. 771 57:55.970 --> 57:56.530 Having. 772 58:16.730 --> 58:19.010 Invalid operand data type data. 773 58:19.050 --> 58:21.170 Data is invalid for subtract. 774 58:21.170 --> 58:21.770 Operator. 775 58:22.330 --> 58:22.730 Date. 776 58:22.730 --> 58:23.530 Cannot be. 777 58:26.370 --> 58:27.170 Subtracted. 778 58:31.290 --> 58:31.850 We can do. 779 58:31.890 --> 58:32.410 Cast. 780 58:32.410 --> 58:33.370 Cast of. 781 58:35.970 --> 58:39.610 Okay, if function is there, why not? 782 58:41.570 --> 58:41.970 Right. 783 58:43.090 --> 58:47.490 Okay, so let me, uh, show you another way. 784 58:48.650 --> 58:49.570 Uh, date part. 785 58:49.610 --> 58:51.050 I am using date part. 786 58:55.410 --> 58:58.970 Date part of this one in day. 787 59:05.010 --> 59:05.650 Then again. 788 59:05.650 --> 59:07.210 Date part of this. 789 59:07.210 --> 59:08.090 One in day. 790 59:13.290 --> 59:15.290 Now let me execute this data. 791 59:18.690 --> 59:19.010 Two. 792 59:19.050 --> 59:21.330 Two is coming right now. 793 59:32.530 --> 59:34.170 Having this equal to two. 794 59:38.210 --> 59:40.050 Two and five is our result set right? 795 59:40.050 --> 59:40.970 You got it right. 796 59:40.970 --> 59:41.610 This one. 797 59:44.010 --> 59:45.850 I have taken the date part. 798 59:46.130 --> 59:54.050 So date part will be give me this one only seven and five and 30, 30 and 28. 799 59:54.170 --> 59:59.690 Because I have mentioned day in the date part okay. 800 59:59.850 --> 01:00:00,250 Okay. 801 01:00:00,450 --> 01:00:01,330 Day in the date part. 802 01:00:01,370 --> 01:00:07,930 So day means what day means 0506, zero seven, 28, 29 and 30. 803 01:00:08,370 --> 01:00:10,930 But now our requirement is difference. 804 01:00:11,250 --> 01:00:15,410 So group by user ID having the same thing equal to two only. 805 01:00:16,450 --> 01:00:18,170 So now I am getting this one. 806 01:00:19,530 --> 01:00:21,930 Two and five is only two two values right. 807 01:00:23,290 --> 01:00:24,890 What are the values for other. 808 01:00:26,690 --> 01:00:28,370 Uh for one it is 16. 809 01:00:28,770 --> 01:00:32,890 So one if you see right 117 -116. 810 01:00:34,810 --> 01:00:37,730 This is not my requirement for three. 811 01:00:37,770 --> 01:00:39,210 Three what is the difference? 812 01:00:39,610 --> 01:00:42,730 Only one time it is coming so no difference right? 813 01:00:43,090 --> 01:00:51,810 Four is also coming one time 452 and five seven minus five 230 -28 two. 814 01:00:52,690 --> 01:00:53,890 So that is my requirement. 815 01:00:53,930 --> 01:00:55,050 Now what can I do. 816 01:00:58,490 --> 01:01:03,290 I want two and five only right I don't want simple thing right. 817 01:01:03,330 --> 01:01:08,250 Select star from city. 818 01:01:08,250 --> 01:01:09,250 Also we can use. 819 01:01:09,250 --> 01:01:11,050 But let me okay. 820 01:01:15,650 --> 01:01:21,250 Now only I want user id set date difference function can can also be used. 821 01:01:21,810 --> 01:01:23,250 Yeah date different function. 822 01:01:23,250 --> 01:01:26,850 Also we can use now two and five is coming right. 823 01:01:27,330 --> 01:01:29,090 Maybe in the below part I. 824 01:01:31,330 --> 01:01:32,690 Used okay over here. 825 01:01:32,690 --> 01:01:34,570 Also I have used date part only. 826 01:01:36,850 --> 01:01:37,250 Okay. 827 01:01:37,290 --> 01:01:40,370 Can we try with the date difference function? 828 01:01:43,810 --> 01:01:45,170 Select star from. 829 01:01:48,930 --> 01:01:55,210 Select star from this one then user id. 830 01:01:58,090 --> 01:01:58,930 Date diff. 831 01:02:00,450 --> 01:02:02,570 So date diff between. 832 01:02:02,570 --> 01:02:04,930 What makes of. 833 01:02:08,850 --> 01:02:09,610 Purchase date. 834 01:02:09,610 --> 01:02:10,170 Comma. 835 01:02:10,210 --> 01:02:11,250 Minimum of. 836 01:02:20,490 --> 01:02:21,490 Now date. 837 01:02:21,530 --> 01:02:21,850 Date. 838 01:02:21,850 --> 01:02:22,330 Diff. 839 01:02:22,370 --> 01:02:22,890 Over here. 840 01:02:22,890 --> 01:02:25,410 What is wrong in dates? 841 01:02:25,450 --> 01:02:25,890 We want. 842 01:02:25,970 --> 01:02:26,450 right? 843 01:02:31,490 --> 01:02:32,250 Did did. 844 01:02:36,970 --> 01:02:37,290 Did. 845 01:02:37,290 --> 01:02:37,610 Did. 846 01:02:39,570 --> 01:02:40,010 Did. 847 01:02:45,650 --> 01:02:46,170 Perfect. 848 01:02:46,170 --> 01:02:47,810 It is now giving minus two. 849 01:02:48,290 --> 01:02:51,930 So what we can do here we can do minimum. 850 01:02:53,850 --> 01:02:55,570 And here we can do Max. 851 01:02:56,850 --> 01:02:57,290 Max. 852 01:02:57,330 --> 01:02:57,770 Right. 853 01:02:57,970 --> 01:02:59,370 Now what? 854 01:02:59,370 --> 01:03:01,050 We can do more further. 855 01:03:01,050 --> 01:03:02,410 We can do this one. 856 01:03:04,090 --> 01:03:07,210 Having this equal to two. 857 01:03:09,810 --> 01:03:10,210 Right. 858 01:03:11,850 --> 01:03:12,890 Again two and five. 859 01:03:12,890 --> 01:03:14,930 Now again we can right this time. 860 01:03:15,890 --> 01:03:18,050 City with city as. 861 01:03:23,930 --> 01:03:27,450 Select user ID from city. 862 01:03:31,810 --> 01:03:33,450 As difference. 863 01:03:34,370 --> 01:03:35,890 We have to give column name. 864 01:03:35,890 --> 01:03:38,530 Right is alias. 865 01:03:38,530 --> 01:03:40,610 Now two and five is again coming. 866 01:03:41,130 --> 01:03:43,090 Same result right for both queries. 867 01:03:47,490 --> 01:03:48,490 Perfect, right? 868 01:03:50,570 --> 01:03:52,970 In one place we have used date part function. 869 01:03:53,010 --> 01:03:55,490 In one fetch we have used date difference function. 870 01:03:55,970 --> 01:04:02,530 So these are again use cases of date functions with city and derived table. 871 01:04:07,090 --> 01:04:13,170 So you have to identify what kind of uh scenario what kind of possibilities are there. 872 01:04:13,570 --> 01:04:20,410 Here we have identified the difference between we want to identify the three consecutive days. 873 01:04:21,050 --> 01:04:25,450 So maximum date and minimum date difference should be two only, right? 874 01:04:27,290 --> 01:04:29,690 Then only we can say three consecutive days. 875 01:04:30,370 --> 01:04:33,410 Otherwise we cannot say it as three consecutive days. 876 01:04:33,770 --> 01:04:37,770 So how can we identify the three consecutive days. 877 01:04:37,810 --> 01:04:40,730 Now maximum and minimum difference should be two. 878 01:04:41,930 --> 01:04:49,650 Here from this one, maximum of user ID date and minimum of user ID date difference should be two. 879 01:04:51,370 --> 01:04:54,890 That is our requirement that user ID is our requirement. 880 01:05:00,490 --> 01:05:01,370 So clear right. 881 01:05:01,370 --> 01:05:07,490 This is the query we have again done with two different ways. 882 01:05:16,770 --> 01:05:17,970 So thank you everyone. 883 01:05:17,970 --> 01:05:22,770 Now let me know if you have any doubts or any queries or anything.