1 00:01.240 --> 00:04.360 So this is our first query of R today. 2 00:06.960 --> 00:11.800 Write a query that will identify returning active users. 3 00:12.920 --> 00:20.360 Returning active users A returning active user is a user that has made a second purchase within seven 4 00:20.360 --> 00:22.960 days of any other of their purchases. 5 00:24.800 --> 00:28.360 Output A list of user IDs of these returning active users. 6 00:29.880 --> 00:37.080 So here they have given us the table Amazon Amazon transactions table. 7 00:41.000 --> 00:42.080 So let me first. 8 00:42.440 --> 00:47.600 I have already created the table and inserted the data into the same. 9 00:51.480 --> 00:52.480 So, uh. 10 00:55.440 --> 01:02.330 Let me do order by order by user IDN created it. 11 01:05.490 --> 01:11.690 So if you see over here one user has purchased the different items, right? 12 01:12.210 --> 01:17.210 One user has purchased the different items on on different date. 13 01:18.250 --> 01:21.690 Now we need to identify the returning active users. 14 01:22.290 --> 01:35.610 It means that any particular user has purchased the item on item within seven days of of their first 15 01:35.610 --> 01:36.290 purchase. 16 01:37.490 --> 01:38.410 Any user. 17 01:41.650 --> 01:42.770 Any user has. 18 01:45.170 --> 01:49.450 Purchased the second item on. 19 01:51.770 --> 01:55.530 Or within seven days of. 20 01:57.540 --> 02:01.340 Their first purchase. 21 02:01.340 --> 02:06.620 So this is called as this is we can call as a returning active user. 22 02:13.460 --> 02:14.580 Returning active user. 23 02:14.620 --> 02:16.620 Now we need to identify the same. 24 02:17.300 --> 02:24.260 So if you see over here from this data and one more thing is the item should be different. 25 02:24.260 --> 02:27.860 It should not be the same item within seven days. 26 02:28.060 --> 02:34.940 So if it is a bread like on the first day, on the first date is a bread, then it should be not not 27 02:34.940 --> 02:38.100 equal to bread in within seven days. 28 02:38.340 --> 02:40.820 So let me. 29 02:44.700 --> 02:46.540 See over here if 100 is there. 30 02:46.540 --> 02:46.940 Right. 31 02:47.540 --> 02:50.140 So bread is purchased on seven three. 32 02:51.300 --> 02:56.750 Now again 100 user ID has purchased the different item Within seven days. 33 02:57.390 --> 03:04.350 So this should be our active user right then if you see over here 101. 34 03:05.110 --> 03:08.310 So 101 has purchased the two different items. 35 03:08.790 --> 03:10.710 Sorry same item item. 36 03:11.790 --> 03:14.070 And that is also not within seven days. 37 03:14.070 --> 03:16.950 So this will not be our output. 38 03:17.230 --> 03:18.550 So 101 will. 39 03:18.590 --> 03:22.830 Sorry 100101 will not come right. 40 03:23.550 --> 03:27.750 It should be not come in output. 41 03:30.390 --> 03:33.590 Now 102102 is only single record. 42 03:34.150 --> 03:37.870 So 103103 will come. 43 03:37.870 --> 03:42.590 But 102 will not come because 102 is only a single record. 44 03:44.430 --> 03:46.870 So 102 will not come. 45 03:46.870 --> 03:48.390 103 should come. 46 03:54.270 --> 03:55.590 Now we need to. 47 03:56.150 --> 04:03.030 I have already created this table in that ID user id item created date and revenue is column is there. 48 04:03.750 --> 04:06.750 I have already inserted the data into the same. 49 04:11.630 --> 04:13.390 Now what I have done over here. 50 04:14.070 --> 04:23.590 So if you see over here this is just a simple selection, simple selection of all the data from the 51 04:23.590 --> 04:24.110 table. 52 04:24.790 --> 04:31.550 But now in in this entire part will be encapsulated in one city. 53 04:31.830 --> 04:33.950 Now what I want to compare. 54 04:36.510 --> 04:43.790 I want to compare that the same user ID has purchased the another item within seven days. 55 04:44.350 --> 04:47.830 So over here we have taken all the columns. 56 04:47.830 --> 04:48.230 Right. 57 04:48.670 --> 04:52.190 But now in and this will be part of our city. 58 04:52.920 --> 04:55.320 Now again, I am joining the same table. 59 04:55.880 --> 04:57.240 Same table over here. 60 04:57.920 --> 04:59.000 Amazon transaction. 61 04:59.040 --> 05:02.640 Same table with which column with user ID only. 62 05:02.680 --> 05:06.160 Single column with user ID column. 63 05:06.560 --> 05:08.120 And then I am checking. 64 05:09.680 --> 05:15.480 I am checking that this one's created date should be between. 65 05:15.680 --> 05:16.800 Should be between. 66 05:19.000 --> 05:26.280 Our original tables created date and we need to add seven days into the same. 67 05:27.360 --> 05:30.040 So that here dated function is used. 68 05:30.040 --> 05:33.280 And in the dated this is how many days. 69 05:33.320 --> 05:40.200 So here there will be different parameters like day, hour, millisecond, minute, month if you want 70 05:40.200 --> 05:43.120 to month, add month or second, weekday or year. 71 05:43.640 --> 05:48.520 But I want to add day seven days into the created date. 72 05:48.520 --> 05:48.960 Right? 73 05:49.480 --> 05:50.400 Created eight. 74 05:50.400 --> 05:50.410 Ed. 75 05:53.450 --> 05:56.970 So this date should be between. 76 06:02.970 --> 06:04.370 This date should be between. 77 06:06.610 --> 06:12.490 This once created date and end, we need to add 77 days into the same. 78 06:14.170 --> 06:14.650 Right. 79 06:14.850 --> 06:23.490 And one more thing is whichever item, whichever item it has purchased over here, that should not be 80 06:23.490 --> 06:24.570 the same item. 81 06:27.170 --> 06:33.250 So this city dot item not equal to this one user usar dot item. 82 06:34.130 --> 06:36.610 So item should be different. 83 06:37.530 --> 06:39.410 So now if I run this query. 84 06:43.610 --> 06:52.140 So as I have already mentioned over here, if you see over here 10100 should come in our output. 85 06:52.180 --> 06:55.380 Then 101 should not come in our output. 86 06:55.380 --> 06:55.780 Right. 87 06:56.900 --> 06:57.460 Because. 88 06:59.660 --> 07:01.460 Let me copy this data. 89 07:05.620 --> 07:07.540 And run this query again. 90 07:10.940 --> 07:15.660 So 100 will come because bread and banana both are different items. 91 07:16.060 --> 07:18.220 And it has purchased within the seven days. 92 07:18.220 --> 07:18.660 Right. 93 07:19.220 --> 07:23.860 Then 10101101 it has same item. 94 07:24.420 --> 07:25.900 So it should not come right. 95 07:26.300 --> 07:28.860 Then again 102 has only single item. 96 07:29.060 --> 07:30.500 So again it should not come. 97 07:31.020 --> 07:35.860 Then 103103 is one item bread and one item milk. 98 07:36.300 --> 07:39.260 And it has purchased within seven days. 99 07:39.260 --> 07:40.620 So it should come right. 100 07:40.620 --> 07:42.140 So 103 is coming. 101 07:44.940 --> 07:46.660 Then 104 will not come. 102 07:46.830 --> 07:50.950 105 will not come because item is same, right? 103 07:53.710 --> 07:56.110 So this should be our output. 104 07:57.750 --> 07:59.270 If you see the query and. 105 08:01.510 --> 08:03.470 Now this is our second method. 106 08:04.070 --> 08:06.070 In the second method we are doing the. 107 08:08.190 --> 08:09.190 Same same table. 108 08:09.190 --> 08:13.030 Join with two times right. 109 08:13.070 --> 08:13.550 Same table. 110 08:13.550 --> 08:15.990 Join with two times with again join. 111 08:16.030 --> 08:20.710 Joining condition will be the same because here also in the city part. 112 08:20.750 --> 08:24.870 Also here also we are joining with user ID. 113 08:26.430 --> 08:33.870 And also here we are joining with user ID and what we are checking over here item will be not the same 114 08:34.670 --> 08:37.870 between the first first one and second one. 115 08:40.110 --> 08:48.520 And then we are checking that retaining are retaining user created it between first user created date 116 08:48.520 --> 08:54.920 and seven days of plus seven days of user created date. 117 08:55.240 --> 08:59.680 So this should be between these two dates. 118 09:01.880 --> 09:06.200 In order by we that is also same same thing. 119 09:06.560 --> 09:08.480 Order by this column user id. 120 09:08.960 --> 09:11.400 And here we are selecting distinct record. 121 09:12.800 --> 09:14.920 So it will again give the same output. 122 09:14.920 --> 09:17.760 Here also 14 records are showing. 123 09:17.920 --> 09:18.960 And here also. 124 09:21.640 --> 09:24.000 14 records are showing. 125 09:27.640 --> 09:31.560 Now let me know if you have any query in this question. 126 09:37.920 --> 09:39.080 You can speak. 127 09:43.850 --> 09:50.050 You can speak or write in the chat or message if you have any query. 128 10:00.450 --> 10:05.530 So again over here we are doing with two different method. 129 10:05.650 --> 10:07.730 One is city. 130 10:07.770 --> 10:09.490 We have used the common table expression. 131 10:09.490 --> 10:15.050 And over here we have used the simple join conditions only. 132 10:16.210 --> 10:18.530 But we are joining the same table twice. 133 10:24.610 --> 10:31.090 And because of first item should be not similar to the second item but within seven days. 134 10:31.650 --> 10:34.330 So that we have write this condition right. 135 10:40.290 --> 10:41.450 Are you clear guys? 136 10:44.860 --> 10:47.900 You can write in the chat message. 137 10:51.900 --> 10:53.860 Then we will move to the second query. 138 11:07.140 --> 11:07.780 Okay. 139 11:12.740 --> 11:15.180 Now let's move to the second query. 140 11:22.340 --> 11:27.300 So this query has been asked in TikTok SQL interview question. 141 11:27.340 --> 11:29.900 And this is regarding sign up activation rate. 142 11:32.820 --> 11:35.500 So new TikTok users sign up with their emails. 143 11:43.700 --> 11:49.860 New TikTok users sign up with their emails, they confirm them, sign up by replying to the text confirmation 144 11:49.860 --> 11:52.380 to activate their account. 145 11:53.420 --> 12:00.660 Users may receive multiple text messages for the account confirmation until they confirm their new account. 146 12:01.660 --> 12:08.020 A senior analyst is interested to know the activation rate of specified users in the email table. 147 12:08.820 --> 12:17.860 Write a query to find the activation rate and round the percentage to two decimal places, and email 148 12:17.860 --> 12:24.580 table contains the information of user sign up details and text table contains users activation information. 149 12:25.900 --> 12:31.980 So here they have given two different tables email table and text table. 150 12:34.460 --> 12:43.110 So in the email table, email ID, user ID and signup date has been given right in in the text. 151 12:43.150 --> 12:47.990 Table, text ID, email ID and sign up action is given. 152 12:48.230 --> 12:55.350 So here if you see over here confirmed and not confirmed, there are two different sign up action. 153 12:56.590 --> 12:58.670 One is confirm and one is not confirmed. 154 12:59.230 --> 13:01.070 So here we need to find the. 155 13:04.830 --> 13:08.430 Activation rate that how many total users are there. 156 13:08.430 --> 13:13.430 And from that how much has done the confirmation. 157 13:14.110 --> 13:16.750 How many of them has done the confirmation. 158 13:17.470 --> 13:19.190 So if you see over here. 159 13:23.190 --> 13:32.390 Here what we have done here, we have done the simple, simple join between email table and text table. 160 13:32.750 --> 13:36.990 And we are counting the user ID in the email table. 161 13:37.760 --> 13:38.280 So. 162 13:41.480 --> 13:44.240 Let me first select the data from the emails table. 163 13:52.680 --> 13:54.120 So these are the data. 164 13:54.480 --> 13:56.880 You can see over here total six records are there. 165 13:56.880 --> 13:57.240 Right. 166 13:57.920 --> 13:59.840 Six records are there in the email table. 167 13:59.840 --> 14:04.320 And this email ID will be common column in the text table. 168 14:05.200 --> 14:10.840 And in from that out of this six record two are confirmed. 169 14:12.760 --> 14:14.080 So what is the ratio. 170 14:14.120 --> 14:16.120 What is the activation rate. 171 14:16.720 --> 14:21.400 It should be two divided by six right two divided by six. 172 14:22.200 --> 14:29.240 Let me try to do it is zero okay 0.00.00. 173 14:32.080 --> 14:32.440 Okay. 174 14:32.480 --> 14:35.160 Actually it is 0.33 right. 175 14:36.970 --> 14:37.530 Zero. 176 14:37.650 --> 14:45.930 So out of total six user IDs, only two user has done the confirmation that you can see over here in 177 14:45.930 --> 14:47.290 the table over here. 178 14:47.690 --> 14:48.090 Right. 179 14:49.090 --> 14:53.930 So two divided by six should be our answer 0.33. 180 14:54.450 --> 14:55.570 Now how we can. 181 14:56.530 --> 15:01.650 If you see over here this is the first city right. 182 15:01.690 --> 15:04.530 This is the first city in the first city. 183 15:04.570 --> 15:12.850 I am just counting how many total users are there when we are doing the join between email table and 184 15:13.290 --> 15:14.170 text table. 185 15:16.370 --> 15:21.050 So if you see over here, there are total six users are there. 186 15:21.050 --> 15:21.490 Right. 187 15:23.530 --> 15:27.570 Now I this is my first city. 188 15:28.650 --> 15:30.050 This is my first city. 189 15:30.090 --> 15:31.530 Now in the second city. 190 15:32.370 --> 15:33.450 What I am doing. 191 15:34.580 --> 15:44.860 I am just calculating from out of six six users how many has done the email confirmation right? 192 15:46.060 --> 15:48.340 How many has done the email confirmation? 193 15:48.780 --> 15:50.340 So if you see over here. 194 15:55.340 --> 16:00.140 Out of these six user, only two user has done the email confirmation. 195 16:01.340 --> 16:03.300 Rest of the four are not confirmed. 196 16:03.780 --> 16:05.140 Now what we need to do. 197 16:06.540 --> 16:08.260 This should be our second city. 198 16:08.700 --> 16:09.700 Simple, right? 199 16:10.340 --> 16:11.900 This should be our first city. 200 16:13.180 --> 16:14.900 This should be our second city. 201 16:15.460 --> 16:17.220 Now what we need to do. 202 16:24.500 --> 16:25.740 Now we need to do. 203 16:30.020 --> 16:31.580 Confirmed users. 204 16:35.430 --> 16:36.270 Confirm users. 205 16:36.270 --> 16:38.630 How many of them are confirmed? 206 16:38.670 --> 16:42.390 Two are confirmed right and divided by total users. 207 16:47.710 --> 16:48.950 That should be our rate. 208 16:50.190 --> 16:52.670 But now if I execute this one. 209 16:55.990 --> 16:58.670 It is not giving me the proper output, right? 210 16:59.630 --> 17:09.510 Because integer divided by integer is somehow it is eliminating the point after point condition point 211 17:09.830 --> 17:13.750 decimal places because of it is mentioned as integer. 212 17:14.390 --> 17:17.630 So that over here I have done all this thing. 213 17:21.270 --> 17:22.790 So let me. 214 17:32.840 --> 17:34.880 Let me try it step by step. 215 17:52.400 --> 17:53.000 Okay. 216 17:53.000 --> 17:55.720 Let me remove this one and uncomment this one. 217 17:56.200 --> 18:00.440 So here what I have done I have first cast. 218 18:00.480 --> 18:09.520 Cast the confirmed user to decimal two places into decimal value from integer to decimal value in two 219 18:09.520 --> 18:10.120 places. 220 18:10.680 --> 18:13.280 Then same thing over here to total users. 221 18:13.280 --> 18:14.600 I have done the same thing. 222 18:17.360 --> 18:19.000 Same thing to total users. 223 18:19.640 --> 18:27.880 Then again, I am rounding the rounding the answer entire answer by two and then again I am casting 224 18:27.880 --> 18:32.920 it to five comma, two decimal places. 225 18:33.720 --> 18:38.960 Then if I run this query, it will give me the exact output which is required. 226 18:39.440 --> 18:40.560 0.33. 227 18:44.720 --> 18:46.080 This one is wrong one. 228 18:46.320 --> 18:47.440 Let me remove this. 229 18:49.480 --> 18:51.600 So 0.33 is our answer. 230 18:52.160 --> 18:54.160 We have already seen it over here right? 231 18:59.840 --> 19:02.440 Because we want only two decimal places. 232 19:04.160 --> 19:06.480 Two decimal places I have done over here. 233 19:07.720 --> 19:11.280 All these activities count round function. 234 19:11.280 --> 19:13.160 Then passing into the decimal. 235 19:14.680 --> 19:15.640 All this thing. 236 19:18.840 --> 19:24.560 Now let me know if you have any query or question in this solution. 237 19:37.570 --> 19:41.410 Does anyone have any query or they are clear with the solution? 238 19:57.250 --> 19:57.690 Hello? 239 20:04.250 --> 20:04.650 Everyone. 240 20:04.690 --> 20:05.650 Everyone are clear. 241 20:13.570 --> 20:13.970 Okay. 242 20:13.970 --> 20:14.690 One minute. 243 20:22.170 --> 20:22.810 Okay. 244 20:25.220 --> 20:27.620 So this is the our second query. 245 20:29.300 --> 20:35.420 Actually you should be able to uh, this is again we have used the multiplicity right. 246 20:35.860 --> 20:37.620 Multiplicity over here. 247 20:42.460 --> 20:45.220 Here here I have used three cities. 248 20:46.060 --> 20:50.260 And then in the final answer I am retrieving from city three. 249 20:50.780 --> 20:56.020 And over here in the above one we have used the two different cities. 250 20:58.740 --> 21:02.460 So this is the use case scenario of multiple cities. 251 21:06.220 --> 21:11.220 In the analysis portion we are using many a times this kind of scenarios. 252 21:12.500 --> 21:16.740 Then let me move to the next question. 253 21:19.220 --> 21:22.620 This query has been asked in Microsoft uh guys. 254 21:22.620 --> 21:26.670 Is and it is question related to Supercloud customer. 255 21:29.270 --> 21:31.310 So understand the question first. 256 21:31.750 --> 21:40.310 A Microsoft Azure Supercloud customer is defined as a company that purchases at least at least at least 257 21:40.310 --> 21:42.790 one product from each product category. 258 21:44.470 --> 21:51.150 Write a query that effectively identifies the company ID of such Supercloud customers. 259 21:53.510 --> 22:02.350 So here they have given us the table customer contracts and products table customer contracts and products 260 22:02.350 --> 22:02.830 table. 261 22:03.390 --> 22:06.310 I have already inserted the data into the same. 262 22:07.070 --> 22:12.270 So let me select the data from the customer contracts. 263 22:18.030 --> 22:19.110 And products table. 264 22:24.320 --> 22:25.040 So. 265 22:33.880 --> 22:34.640 One minute. 266 22:38.680 --> 22:39.120 Here. 267 22:39.120 --> 22:40.800 Understand the question guys. 268 22:41.480 --> 22:46.120 Here we have different product categories are given in. 269 22:46.160 --> 22:48.760 We want to identify the customers. 270 22:49.720 --> 22:56.200 Those have purchased at least one product from all the different categories. 271 22:57.400 --> 22:59.960 And that should be our supercloud customer. 272 23:01.800 --> 23:04.680 So if you see over here in the both the tables. 273 23:08.800 --> 23:17.720 Customer ID product ID and amount is given, then that same product ID is given in the products table. 274 23:18.280 --> 23:25.370 And over here different product Categories are given analytics category and containers category. 275 23:25.370 --> 23:26.490 Compute category. 276 23:26.530 --> 23:28.610 I think only three categories are given. 277 23:29.730 --> 23:40.970 So we want to identify those customer we we who has purchased at least minimum one product from analytics 278 23:40.970 --> 23:46.090 category, then one product from containers category and one product from compute category. 279 23:46.810 --> 23:47.250 So. 280 24:00.410 --> 24:04.010 So one is two two analytics. 281 24:04.530 --> 24:06.730 Then one is five five compute. 282 24:07.490 --> 24:08.090 And then. 283 24:20.100 --> 24:29.020 Okay, so here what we are doing here also we are using the concept of multiplicity here. 284 24:29.020 --> 24:33.780 Also we are using the concept of multiplicity now. 285 24:39.340 --> 24:46.260 Now if you see over here let me just trying to copy this data into the Excel file. 286 24:54.260 --> 24:59.500 And array and then copy this data into the Excel file. 287 25:09.820 --> 25:13.860 So now if you see over here this. 288 25:21.070 --> 25:24.350 Let's take the random example of customer ID seven. 289 25:25.150 --> 25:27.150 Write customer ID is seven. 290 25:27.830 --> 25:35.350 If you see customer ID seven is coming over here, then coming over here, customer ID seven is purchased. 291 25:35.350 --> 25:38.310 Total total three product ID right. 292 25:39.030 --> 25:43.150 Now we need to check this product ID is coming under which category. 293 25:43.870 --> 25:46.750 So let me jump to the products table. 294 25:46.790 --> 25:48.350 So this is the one. 295 25:49.630 --> 25:53.870 This is the three and this is the six. 296 25:55.510 --> 26:02.190 So if you see over here this one product ID is analytics product category. 297 26:02.750 --> 26:08.630 Then three has continuous product category and six as complete product category. 298 26:09.270 --> 26:11.150 So customer ID seven. 299 26:17.310 --> 26:20.230 Customer ID seven is our super cloud customer. 300 26:21.030 --> 26:24.190 Now let me try to identify for the two. 301 26:24.990 --> 26:27.150 So two is coming for the two to. 302 26:27.190 --> 26:29.870 So two means analytics okay. 303 26:31.270 --> 26:32.310 Now two. 304 26:34.470 --> 26:35.710 Now two is coming. 305 26:35.710 --> 26:36.070 Where. 306 26:36.710 --> 26:38.870 Two is coming only over here. 307 26:39.590 --> 26:40.630 And that is five. 308 26:40.670 --> 26:41.950 Five means compute. 309 26:43.670 --> 26:48.110 But now two has not purchased any product from the containers. 310 26:48.110 --> 26:53.590 Category two has not purchased any product from the containers category. 311 26:54.190 --> 26:57.230 So two should be not our super cloud customer. 312 26:58.350 --> 27:00.310 Now let's move to the three. 313 27:01.070 --> 27:03.110 So three is one done one. 314 27:03.110 --> 27:04.670 So one means analytics. 315 27:05.470 --> 27:07.670 Then three is not coming at all. 316 27:07.950 --> 27:09.550 So three will be not our answer. 317 27:10.310 --> 27:13.760 Now four for one means analytics. 318 27:13.760 --> 27:17.840 Now four is coming over here four five means compute but four is not. 319 27:17.840 --> 27:21.840 Purchase anything from the containers category. 320 27:21.840 --> 27:22.240 Right. 321 27:23.960 --> 27:29.800 So understand understand right now how we are solving this SQL query. 322 27:30.480 --> 27:33.800 So first of all I have found. 323 27:36.160 --> 27:41.360 How many distinct product categories are available with us in the products table. 324 27:41.840 --> 27:48.800 So in the products table we have total analytics container and compute total. 325 27:48.800 --> 27:54.040 Three different distinct product categories are available right. 326 27:54.760 --> 27:58.760 Now we want to join the same thing. 327 28:13.370 --> 28:15.970 We now what we are doing. 328 28:15.970 --> 28:17.570 This is our first city, right? 329 28:18.130 --> 28:21.810 This is our first city now in the second city. 330 28:21.850 --> 28:24.770 What we are doing right. 331 28:25.610 --> 28:30.530 We are doing the count of distinct product category, customer ID wise. 332 28:31.570 --> 28:34.690 So let me see over here. 333 28:36.330 --> 28:37.810 So it should be three right. 334 28:37.810 --> 28:42.490 It should be three count of distinct product category should be three. 335 28:42.530 --> 28:43.210 Because. 336 28:44.130 --> 28:45.210 Because over here. 337 28:45.210 --> 28:45.610 What. 338 28:46.890 --> 28:48.210 Over here it is three. 339 28:51.530 --> 29:01.090 All all the categories is must purchase one item from all categories so that over here customer contracts 340 29:01.090 --> 29:08.010 table joining with products table based on product ID product ID and then count of distinct category 341 29:08.050 --> 29:08.930 should be three. 342 29:09.580 --> 29:11.180 So now this. 343 29:11.180 --> 29:11.620 Pity. 344 29:13.060 --> 29:14.340 Now in the final. 345 29:14.820 --> 29:15.700 Final one. 346 29:15.700 --> 29:19.180 What we are doing in the final one. 347 29:19.980 --> 29:20.700 We are. 348 29:22.740 --> 29:24.140 Matching this city. 349 29:24.180 --> 29:27.860 City means this one is coming as three, right? 350 29:29.420 --> 29:31.420 And this is also coming as three. 351 29:32.100 --> 29:37.140 So we are joining this one with city over here. 352 29:37.140 --> 29:43.620 If you see over here city, city city, this is city two. 353 29:46.460 --> 29:50.220 And now we are just identifying customer ID. 354 29:51.220 --> 29:58.180 So if you see over here we want only this one who has. 355 30:01.180 --> 30:03.300 Total distinct category equal to three. 356 30:03.940 --> 30:05.780 So seven should be our answer. 357 30:06.140 --> 30:08.030 So now if I run this query. 358 30:10.350 --> 30:12.150 I will found seventh one. 359 30:16.870 --> 30:22.230 That should be our Supercloud customer, which I have also explained over here. 360 30:26.630 --> 30:28.150 Customer ID is seven. 361 30:30.030 --> 30:36.390 So now are you guys able to digest the approach? 362 30:36.430 --> 30:38.390 Approach is the main important thing. 363 30:39.310 --> 30:45.470 Understand the question and then how to move towards the solution of the question. 364 30:46.750 --> 30:49.310 That is very important over here. 365 30:51.110 --> 30:53.270 Now let me know if you have any query. 366 30:58.990 --> 31:01.710 You are getting this approach right. 367 31:08.640 --> 31:10.360 You can speak over here or. 368 31:21.440 --> 31:22.280 Or you can. 369 31:27.040 --> 31:27.440 Yeah. 370 31:27.760 --> 31:28.680 It's understandable. 371 31:29.880 --> 31:30.240 Okay. 372 31:30.240 --> 31:30.480 Okay. 373 31:30.480 --> 31:30.880 Okay. 374 31:34.000 --> 31:34.480 Thank you. 375 31:34.520 --> 31:38.560 Now let's move to the next, uh, question. 376 31:45.200 --> 31:46.200 Card launch. 377 31:46.240 --> 31:46.920 Success. 378 31:50.760 --> 31:55.800 So this question is basically asked in the JP Morgan Chase SQL interview. 379 31:57.400 --> 32:01.400 So your team at JP Morgan Chase is soon launching a new credit card. 380 32:05.210 --> 32:10.530 And you are asked to estimate estimate how many cards you will issue in the first month. 381 32:11.170 --> 32:18.610 Before you can answer this question, you want to first get some perspective on how how well new credit 382 32:18.610 --> 32:20.170 card launches typically. 383 32:23.570 --> 32:25.050 Do in their first month. 384 32:25.210 --> 32:32.570 Write a query that outputs the name of the credit card and how many credit cards were issued in its 385 32:32.570 --> 32:33.410 launch month. 386 32:35.970 --> 32:41.770 The launch month is the earliest record in the monthly card issue table for a given card. 387 32:42.970 --> 32:44.530 Order the result. 388 32:47.050 --> 32:49.450 Starting from the biggest issued amount. 389 32:52.410 --> 32:54.050 So here we have one table. 390 32:54.050 --> 32:59.650 They have given us monthly card issued and it has two different. 391 33:02.330 --> 33:03.650 Credit cards are there. 392 33:03.650 --> 33:09.050 One is Chase Sapphire Reserve and one is Chase Freedom Flex. 393 33:12.090 --> 33:14.810 So now let me select the data from the table. 394 33:22.890 --> 33:24.570 So if you see over here. 395 33:40.450 --> 33:43.610 What by which approach I have done. 396 33:44.450 --> 33:46.130 I have used the row number function. 397 33:46.130 --> 33:51.890 We can use the any rank or we can use the rank function as well. 398 33:55.490 --> 33:58.330 So now what is our requirement. 399 33:58.930 --> 34:02.900 Our requirement is that we want to identify the card name. 400 34:03.620 --> 34:11.860 It should amount which is low which is amount in the very first month of its launch. 401 34:15.780 --> 34:20.180 Or we can say that earliest launch month and year. 402 34:21.380 --> 34:22.980 So if you see over here. 403 34:30.740 --> 34:32.380 I think this should be one. 404 34:32.540 --> 34:35.420 This should be and in this case. 405 34:38.380 --> 34:39.180 This should be. 406 34:39.740 --> 34:41.900 So this should be our output. 407 34:44.460 --> 34:52.620 So what I have done over here, I have done the row number over partition by card name because we have 408 34:52.660 --> 34:54.660 total two different card names are given. 409 34:54.660 --> 34:55.060 Right. 410 34:55.820 --> 35:05.790 And we want to identify by both of the card names related details so that I have done the partition 411 35:05.790 --> 35:10.030 by card, name, order by issuer and issue month. 412 35:12.190 --> 35:14.470 So now let me. 413 35:17.550 --> 35:17.870 Okay. 414 35:17.870 --> 35:21.150 Let me select the data from this. 415 35:21.150 --> 35:21.350 Now. 416 35:21.350 --> 35:22.750 Now what is it is giving. 417 35:22.750 --> 35:25.750 It is giving us another rank column. 418 35:26.750 --> 35:32.550 So in that if you see over here one lakh 50,000. 419 35:33.350 --> 35:37.790 So it is basically ordering by issue here and issue month. 420 35:38.550 --> 35:40.750 And by default it is ascending order. 421 35:42.070 --> 35:42.990 So that. 422 35:45.630 --> 35:51.270 Minimum of issuer should come first and minimum of issue month should come first. 423 35:51.990 --> 35:55.670 So over here let me take the month as well. 424 36:02.160 --> 36:03.560 Now execute the data. 425 36:04.200 --> 36:04.720 So. 426 36:05.120 --> 36:06.240 Got it right guys. 427 36:06.240 --> 36:09.000 This one is our first first one. 428 36:09.040 --> 36:11.280 Minimum of tissue here and month. 429 36:11.600 --> 36:16.240 And this is in the Chase Freedom Flex minimum of issue year and month. 430 36:16.720 --> 36:20.120 So now we want to select our key equal to one only right. 431 36:20.920 --> 36:26.960 So we cannot directly write over here where our k equal to one that I have explained yesterday. 432 36:26.960 --> 36:33.080 Also because our k is no particular column because of this reason. 433 36:36.040 --> 36:38.160 What we can do, we can do. 434 36:40.360 --> 36:49.840 Encapsulate this entire block into the c t, and then we can write k equal to one and order by issued 435 36:49.840 --> 36:50.320 amount. 436 36:51.320 --> 36:53.000 So as I have said. 437 36:57.050 --> 37:06.730 This is our output one lakh 50,000in Sapphire Reserve and Chase Freedom Flex 55,000. 438 37:10.810 --> 37:13.450 Now I have one question for you guys. 439 37:13.690 --> 37:17.090 Can we use over here in place of. 440 37:20.570 --> 37:21.970 In place of city. 441 37:22.010 --> 37:23.770 Can we use subquery over here. 442 37:24.890 --> 37:26.170 Can anyone tell me. 443 37:31.290 --> 37:33.450 Yes you can use it. 444 37:34.170 --> 37:34.570 Okay. 445 37:34.570 --> 37:37.090 How are. 446 37:38.450 --> 37:38.770 Oh. 447 37:44.450 --> 37:45.490 I think you are on. 448 37:50.490 --> 37:50.930 Hello. 449 37:53.130 --> 37:53.970 Am I audible? 450 37:53.970 --> 37:53.980 Beautiful. 451 37:54.140 --> 37:56.300 Yes, yes, I'm telling that. 452 37:56.340 --> 37:58.500 We'll put a we'll put. 453 37:58.500 --> 38:04.260 This means the statement outside of city in in in place of city. 454 38:06.380 --> 38:06.780 Okay. 455 38:06.780 --> 38:13.460 Then, uh, then maybe this one can happen. 456 38:21.180 --> 38:22.700 This is our first, first result. 457 38:22.700 --> 38:25.060 Right now we want. 458 38:25.740 --> 38:32.140 Now we'll write from, uh, we'll write by will remove KT, and we'll write from this one. 459 38:34.340 --> 38:34.820 Okay. 460 38:34.860 --> 38:36.860 From this entire box. 461 38:39.740 --> 38:40.180 Okay. 462 38:49.940 --> 38:51.220 Now what we have to do. 463 38:51.260 --> 38:58.230 Select Start from now, this entire table will be our one table. 464 38:58.230 --> 38:58.830 Right. 465 38:59.030 --> 39:04.830 And then we'll let where t dot uh k equal to one. 466 39:04.830 --> 39:05.590 Equal to one. 467 39:05.590 --> 39:07.270 Now let me select. 468 39:10.550 --> 39:11.070 Exactly. 469 39:11.070 --> 39:11.790 Perfect. 470 39:12.990 --> 39:21.670 And now if we don't want the k column then this all this column we have to write it manually in place 471 39:21.670 --> 39:22.390 of star. 472 39:25.870 --> 39:28.190 So this is the replacement of city. 473 39:29.350 --> 39:31.430 Both will give us the same result set. 474 39:33.870 --> 39:34.470 Right. 475 39:39.350 --> 39:43.270 So in this question everything is clear guys. 476 39:47.270 --> 39:55.670 We are we are analyzing the card launch Analysis on his first month of both of the credit card. 477 40:07.430 --> 40:08.910 Everything is clear, right? 478 40:15.390 --> 40:15.790 Now. 479 40:15.870 --> 40:18.470 Let me jump to the next question. 480 40:27.430 --> 40:31.230 So this is the next pharmacy analytics question. 481 40:33.830 --> 40:40.830 CVS health is trying to better understand his pharmacy sales and how well different drugs are selling. 482 40:42.950 --> 40:44.070 Write a query. 483 40:48.070 --> 40:54.920 Write a query to find the top two drugs sold in terms of units sold for each manufacturer. 484 40:56.440 --> 41:04.960 So whenever you see guys like this kind of terms, like each product category or each manufacturer at 485 41:04.960 --> 41:12.800 the same time, you have to just click in your mind that this will become our partition by clause, 486 41:13.640 --> 41:17.360 manufacturer or product category will become our partition by clause. 487 41:18.200 --> 41:25.680 Now let's see the table create table pharmacy sales product ID is given, unit sold is given. 488 41:26.200 --> 41:27.600 Total sales is given. 489 41:27.600 --> 41:32.440 Cogs is given Cogs cost of goods sales. 490 41:32.920 --> 41:34.120 Cost of goods sales. 491 41:34.720 --> 41:36.680 Then manufacturer and drugs is given. 492 41:36.680 --> 41:40.160 I have already inserted the data into the same. 493 41:40.840 --> 41:41.920 All this data. 494 41:45.240 --> 41:46.720 I have already inserted. 495 41:46.720 --> 41:46.850 It. 496 41:48.930 --> 41:51.810 Now let me select the data from the table. 497 41:52.410 --> 41:54.010 It is 182 rows. 498 41:55.450 --> 41:56.890 Product ID units sold. 499 41:56.930 --> 41:57.730 Total sales. 500 41:57.730 --> 41:58.410 Cost of goods. 501 41:58.410 --> 42:00.730 Sales, manufacturer and drug. 502 42:03.370 --> 42:10.770 Now, as I have already told, we have to do partition by manufacturer because. 503 42:14.170 --> 42:21.250 We want to write a query to find the top two drugs sold in terms of units sold for each manufacturer. 504 42:23.530 --> 42:26.050 So now let me come again to the down. 505 42:29.530 --> 42:33.810 Partition by manufacturer and then order by unit sold. 506 42:35.010 --> 42:42.450 So over here we want top two drugs sold based on units sold. 507 42:42.490 --> 42:44.050 So if you see over here. 508 42:49.180 --> 42:51.180 Unit sold column is given, right? 509 42:51.780 --> 42:54.340 So let me do order by over here. 510 42:57.540 --> 43:01.340 Order by manufacturer. 511 43:02.500 --> 43:03.420 Unit sold. 512 43:03.580 --> 43:04.860 And then run the query. 513 43:06.540 --> 43:08.900 And also this should be descending. 514 43:12.060 --> 43:13.620 So if you see over here. 515 43:20.820 --> 43:24.140 This should be in in this category. 516 43:24.620 --> 43:27.180 Now what will be the next category. 517 43:36.900 --> 43:39.420 As we are considering unit sold. 518 43:43.910 --> 43:45.790 So this should be our. 519 43:48.150 --> 43:50.710 Second result set. 520 43:52.310 --> 43:54.630 Now again jump to the next one. 521 43:54.630 --> 43:55.590 Next category. 522 44:01.630 --> 44:03.350 This should be our third result set. 523 44:03.870 --> 44:09.830 So let me let me here also we have used the row number. 524 44:09.830 --> 44:12.910 We can use Denserank function as well over here. 525 44:13.710 --> 44:14.750 There is no issue. 526 44:15.830 --> 44:19.750 Partition by manufacturer and order by unit sold. 527 44:20.510 --> 44:24.150 So you have to just identify. 528 44:24.270 --> 44:26.070 This thing is important, guys. 529 44:26.710 --> 44:35.350 By which column we have to do partition by and by which column we need to do order by, and whether 530 44:35.350 --> 44:38.670 we need to do order by descending or order by ascending. 531 44:39.630 --> 44:41.030 That should be important. 532 44:42.120 --> 44:52.760 Now here we want highest units sold as first first row, then second highest is second row. 533 44:53.520 --> 44:55.960 That's why we have done over here descending. 534 44:56.760 --> 45:04.560 But now what if we require the minimum of order date as first row and then second minimum order date 535 45:04.640 --> 45:05.520 is second row. 536 45:06.680 --> 45:12.240 Then over here we should not use descending right. 537 45:13.600 --> 45:16.200 So now this will be simple. 538 45:17.480 --> 45:20.000 Now over here arc less than equal to two. 539 45:21.760 --> 45:26.800 So top two means first two will become from each manufacturer. 540 45:26.800 --> 45:27.000 Why. 541 45:27.000 --> 45:29.040 So let me execute the this data. 542 45:32.080 --> 45:38.640 So see over here manufacturer above that what I have written over here. 543 45:38.680 --> 45:41.650 Same thing right Ten extra. 544 45:41.690 --> 45:42.410 Vanessa. 545 45:42.890 --> 45:46.290 So banana boat and Miralax. 546 45:47.450 --> 45:48.530 Then what I have written. 547 45:48.530 --> 45:48.850 Bear. 548 45:48.890 --> 45:50.610 Bear can. 549 45:54.170 --> 45:54.530 Die. 550 45:55.010 --> 45:59.050 I think this one is coming as second one. 551 46:00.490 --> 46:02.730 So this two column is only our requirement. 552 46:02.730 --> 46:07.370 If any other column is also requirement, then we can write over here. 553 46:07.530 --> 46:10.930 Additionally, there is no issue like unit sold. 554 46:10.970 --> 46:15.130 We want if we want unit sold then we can write unit sold over here. 555 46:15.130 --> 46:15.410 And. 556 46:17.850 --> 46:19.410 Unit sold will also come. 557 46:21.810 --> 46:23.090 That is what exactly. 558 46:23.090 --> 46:24.610 Same thing over here. 559 46:24.610 --> 46:25.330 Coming right. 560 46:25.690 --> 46:27.970 189925. 561 46:32.210 --> 46:40.100 So these are the most important concepts in our in our day to day life The ranking function. 562 46:40.140 --> 46:40.820 Cities. 563 46:40.820 --> 46:42.100 Multiple cities. 564 46:42.980 --> 46:43.940 Direct query. 565 46:46.740 --> 46:47.780 If you see over. 566 46:49.060 --> 46:51.180 I think I have closed that query. 567 46:59.780 --> 47:00.300 Okay. 568 47:08.900 --> 47:10.300 Please go on mute, guys. 569 47:15.620 --> 47:16.020 Hello. 570 47:16.260 --> 47:16.660 Laptop. 571 47:19.060 --> 47:19.580 Laptop. 572 47:21.740 --> 47:25.620 So if you see over here or this concept. 573 47:25.620 --> 47:28.980 This concept is known as sub query. 574 47:36.300 --> 47:38.740 Then this concept is also known as. 575 47:41.740 --> 47:45.060 Derived table and then. 576 47:45.060 --> 47:48.940 This concept is also known as inline view. 577 47:51.780 --> 48:02.260 In Oracle, because inline view in oracle means when we write. 578 48:04.340 --> 48:12.580 Sub query inside the from clause of a Select statement. 579 48:17.340 --> 48:22.580 Then it is considered as inline view. 580 48:26.140 --> 48:30.100 So now let me know guys uh if you have any query. 581 48:32.500 --> 48:34.020 Let me unmute unmute. 582 48:39.270 --> 48:39.670 Light on. 583 48:41.230 --> 48:42.750 Please go and meet Nupur. 584 48:44.190 --> 48:44.510 Ah. 585 48:48.350 --> 48:48.670 Yeah. 586 48:49.790 --> 48:50.430 Yes, yes. 587 48:50.670 --> 48:51.070 Yeah. 588 48:51.110 --> 48:54.070 Today my other system is creating problem, so I have. 589 48:58.590 --> 49:00.430 You can write in the chat box. 590 49:00.550 --> 49:02.150 Uh, if you have any doubt. 591 49:02.710 --> 49:03.270 Right, right. 592 49:03.270 --> 49:04.750 Okay okay okay, okay. 593 49:09.270 --> 49:15.350 Now let me know others if you have any doubt till now in any of the query. 594 49:17.630 --> 49:20.790 You can write or speak. 595 49:24.550 --> 49:25.790 Everything is clear, right? 596 49:33.960 --> 49:36.400 This is part you are enjoying, right? 597 49:36.440 --> 49:45.000 Right, guys, these are very interesting queries and which is asked in real time scenarios. 598 49:48.880 --> 49:52.600 So now let me I think this one we have already resolved. 599 49:52.600 --> 49:52.960 Right. 600 49:54.000 --> 49:55.240 Pharmacy related. 601 49:55.280 --> 49:55.680 Okay. 602 50:05.120 --> 50:08.120 So now let me jump to the next question. 603 50:15.920 --> 50:24.120 Assume you assume you are given a table containing it's a user transaction, right? 604 50:24.120 --> 50:31.720 A query that retrieves the customers whose first transaction was valued at $50 or more, whose first 605 50:31.850 --> 50:34.450 transaction is $50 or more. 606 50:35.090 --> 50:38.050 I put the total number of users who meet this criteria. 607 50:40.810 --> 50:47.130 So here they have given us the user transactions table, transaction ID, user ID, spend and transaction 608 50:47.130 --> 50:49.690 date column are there. 609 50:52.930 --> 50:58.610 So if you select the data from this user transaction these are the data. 610 51:01.330 --> 51:03.170 Now what what is our requirement. 611 51:05.210 --> 51:08.810 We require we want to identify. 612 51:11.490 --> 51:19.370 How many total users has done the first transaction which is greater than $50. 613 51:21.490 --> 51:27.370 How many user or which user ID has done the first transaction. 614 51:27.370 --> 51:29.170 Which is greater than $50? 615 51:29.690 --> 51:33.100 And we want to count the particular. 616 51:35.460 --> 51:37.140 All the distinct user IDs. 617 51:37.260 --> 51:39.700 So now let me do order by. 618 51:45.500 --> 51:48.020 User ID then. 619 51:50.500 --> 51:51.580 Transaction date. 620 51:56.740 --> 52:04.340 So if you see over here 111 this is our data right. 621 52:07.300 --> 52:11.900 So I think 111 this will not satisfy the criteria. 622 52:12.900 --> 52:13.380 Now. 623 52:17.500 --> 52:23.300 145156156 should satisfy our criteria. 624 52:25.060 --> 52:29.270 So this one then 179 is not satisfying. 625 52:31.030 --> 52:33.150 This one will satisfy our criteria. 626 52:37.350 --> 52:39.470 And this one is satisfy our criteria. 627 52:43.150 --> 52:43.990 So if I. 628 52:46.230 --> 52:47.390 Run this query. 629 52:49.430 --> 52:51.110 Our output should be three. 630 52:56.190 --> 53:05.110 Because now let me explain you this 111 has done the first transaction which is less than $50 right. 631 53:06.110 --> 53:07.670 This is the first transaction. 632 53:07.710 --> 53:08.950 Transaction number one. 633 53:12.670 --> 53:14.030 But not. 634 53:16.190 --> 53:18.350 Greater than $50. 635 53:19.430 --> 53:21.230 So it should be not our result. 636 53:21.630 --> 53:23.470 Now 156. 637 53:24.710 --> 53:35.000 So this is transaction number one for user ID 156 and which is greater than $50. 638 53:35.560 --> 53:37.760 So it is satisfying our criteria. 639 53:39.560 --> 53:42.640 Now directly jump to the 180 user ID. 640 53:43.920 --> 53:52.280 This is the transaction one of 180 user ID which is greater than $50. 641 53:52.880 --> 53:55.400 So again this is satisfying our criteria. 642 53:57.400 --> 53:59.960 So now what we are doing over here in the query. 643 54:02.880 --> 54:04.960 We are again using the rank function. 644 54:05.840 --> 54:09.160 Sometimes rank sometimes rank sometimes row number. 645 54:09.320 --> 54:13.880 So now again we are using rank function partition by user id. 646 54:14.520 --> 54:20.280 We want to check by all the user id how many user IDs are there, right? 647 54:20.640 --> 54:23.040 In order by transaction date ascending. 648 54:23.640 --> 54:35.040 If I do not write over here ascending then also it is fine because ascending is by default order whenever 649 54:35.040 --> 54:42.080 we want to mention, or we want to sort the data based on the descending, then only we need to write 650 54:42.080 --> 54:42.760 descending. 651 54:44.360 --> 54:47.480 But in case of ascending we write or we do not write. 652 54:48.280 --> 54:49.520 Both are same. 653 54:52.520 --> 54:53.120 Right. 654 54:54.640 --> 54:56.880 Now let me select this data. 655 54:59.720 --> 55:03.880 So this is giving 1211. 656 55:08.240 --> 55:13.280 So based on the user id it will generate this first transaction column. 657 55:14.840 --> 55:18.640 Now again 1451156 coming three times. 658 55:18.640 --> 55:19.360 So one two. 659 55:30.410 --> 55:33.130 Partition by user ID order by transaction date. 660 55:40.610 --> 55:41.130 Okay. 661 55:41.170 --> 55:44.050 This there should be same transaction date. 662 55:44.050 --> 55:45.610 Let me write it over here. 663 55:55.890 --> 55:57.050 Ah, you got it guys. 664 55:59.330 --> 56:05.810 It is same transaction date for one five 6 in 4 42022442022. 665 56:05.850 --> 56:09.530 That is why it is giving us the same one rank. 666 56:09.570 --> 56:14.970 And then next time it is skipping the rank two. 667 56:14.970 --> 56:17.610 And it is giving three over here. 668 56:20.930 --> 56:21.610 So now. 669 56:22.740 --> 56:23.900 what is my requirement? 670 56:23.940 --> 56:27.100 I require the first transaction only. 671 56:27.860 --> 56:34.420 So if I use rank in place of rank, if I use row number then also no problem. 672 56:34.420 --> 56:43.060 If I use dense rank then also no problem because I want first transaction only and that is also t dot 673 56:43.060 --> 56:45.420 spend should be greater than $50. 674 56:48.220 --> 56:50.580 T dot spend should be greater than $50. 675 56:50.900 --> 56:53.420 And I want transaction one only. 676 56:53.820 --> 56:56.700 So let me now let me. 677 56:59.220 --> 57:00.740 Select over here star. 678 57:05.660 --> 57:11.140 So if you see over here these are all the required transaction 156. 679 57:12.020 --> 57:14.380 Then this one this one and this one. 680 57:14.980 --> 57:20.590 But now what I want I want how many total users. 681 57:24.670 --> 57:27.870 Write a query that retrieves the customers whose first transaction value. 682 57:28.390 --> 57:31.750 Output the total number of users who meet this criteria. 683 57:32.390 --> 57:33.950 Total number of users. 684 57:34.910 --> 57:35.310 So. 685 57:35.510 --> 57:36.350 So one. 686 57:36.390 --> 57:37.430 This is one user. 687 57:37.470 --> 57:38.950 This is again 156. 688 57:39.470 --> 57:42.110 So it will come only one time 156. 689 57:42.150 --> 57:45.470 It should be distinct then 180 and 286. 690 57:46.070 --> 57:47.950 So now what is what I have to do. 691 57:50.750 --> 57:55.830 Now I have to do in place of this star count of distinct user ID. 692 57:55.870 --> 57:56.550 Simple. 693 57:59.390 --> 58:08.550 And my answer is three total three users are meeting the criteria which is done the first transaction 694 58:08.990 --> 58:11.070 which is greater than $50. 695 58:14.110 --> 58:15.950 So clear guys in this question. 696 58:24.440 --> 58:25.120 In this question. 697 58:25.120 --> 58:26.640 Everything is clear, right? 698 58:27.240 --> 58:28.840 Let me know if you have any doubt. 699 58:51.480 --> 58:53.600 This is our final question of the day. 700 58:54.400 --> 58:56.840 So photos of revenue analysis. 701 58:57.720 --> 59:01.000 This question is asked in Adobe SQL interview question. 702 59:03.160 --> 59:04.080 Adobe company. 703 59:04.520 --> 59:11.600 So for every customer that bought Photoshop, return a list of the customers and the total spend. 704 59:14.000 --> 59:18.130 And the total spend on all products except for Photoshop products. 705 59:19.090 --> 59:22.610 Sort your answer by customer IDs in ascending order. 706 59:23.290 --> 59:26.610 So over here I have one table of transactions. 707 59:26.610 --> 59:31.370 Table is given in that customer ID product and revenue column is given. 708 59:32.730 --> 59:35.450 I have already inserted the data into the same. 709 59:39.610 --> 59:40.130 Now. 710 59:43.850 --> 59:45.450 What is my requirement? 711 59:50.210 --> 59:51.490 So I want. 712 59:57.810 --> 01:00:04,250 For every customer that bought Photoshop, for every customer that bought Photoshop, return a list 713 01:00:04,250 --> 01:00:11,090 of customers and the total spent on all products except for Photoshop products. 714 01:00:13,570 --> 01:00:17,620 So now, first of all, we need to identify. 715 01:00:20,180 --> 01:00:24,180 The customer ID who has bought the Photoshop. 716 01:00:27,860 --> 01:00:30,620 Customer ID who has bought the Photoshop. 717 01:00:31,020 --> 01:00:32,980 So 123 and 913. 718 01:00:34,940 --> 01:00:37,260 Now what I have to do. 719 01:00:39,420 --> 01:00:43,260 I have to find the revenue of these two customers. 720 01:00:44,500 --> 01:00:55,500 But but apart from that Photoshop product revenue, apart from that Photoshop product revenue, that 721 01:00:55,500 --> 01:01:01,020 is what it is mentioned over here for every customer that bought Photoshop. 722 01:01:01,900 --> 01:01:06,660 So for that, we have to find two customers who has bought Photoshop. 723 01:01:06,980 --> 01:01:16,950 But now I want to find the revenue means total spent on all the products apart from the Photoshop product. 724 01:01:17,990 --> 01:01:19,670 So now what I have to do. 725 01:01:22,950 --> 01:01:25,350 I just need to do some of revenue. 726 01:01:25,350 --> 01:01:28,350 Customer ID where customer ID in. 727 01:01:29,270 --> 01:01:36,310 Where customer ID in means it means only these two customer ID who has bought Photoshop. 728 01:01:36,830 --> 01:01:38,030 We need to consider. 729 01:01:41,390 --> 01:01:46,030 So now let me select the data from the table. 730 01:01:49,430 --> 01:01:54,550 Now 123 has has done the Photoshop right. 731 01:01:54,550 --> 01:01:56,030 So this will be considered. 732 01:01:56,590 --> 01:01:58,630 This will be considered in our output. 733 01:01:58,630 --> 01:02:01,950 And 232234 is not by Photoshop. 734 01:02:01,950 --> 01:02:03,470 This one is not by Photoshop. 735 01:02:03,990 --> 01:02:05,910 913 is by Photoshop. 736 01:02:06,550 --> 01:02:11,350 But but I want revenue of this one, two three and 913. 737 01:02:12,030 --> 01:02:13,550 So apart from the Photoshop. 738 01:02:13,550 --> 01:02:15,310 So it should be 150 right? 739 01:02:15,870 --> 01:02:18,230 One 5150. 740 01:02:18,750 --> 01:02:22,870 So 150 and now 102 hundred. 741 01:02:22,870 --> 01:02:23,790 So 300. 742 01:02:24,550 --> 01:02:31,230 So one 150 and 300 should be our output in terms of revenue. 743 01:02:31,350 --> 01:02:39,030 So here I am considering the sum of revenue for these two customers only, but product not in Photoshop 744 01:02:39,030 --> 01:02:44,830 and group by because of aggregate function we have used we need to do group by customer ID. 745 01:02:45,430 --> 01:02:47,990 So now let me execute this query. 746 01:02:51,110 --> 01:02:56,590 So as I have explained one, two three, 150 and 300 should be our answer. 747 01:02:56,590 --> 01:03:00,910 We are not calculating the revenue of photos of category. 748 01:03:03,710 --> 01:03:13,400 In this will be completely eliminated in netting because 234 has not by the photos of product. 749 01:03:17,720 --> 01:03:19,160 So this is clear guys. 750 01:03:27,400 --> 01:03:34,160 So we will stop over here and we will discuss the few more queries from next weekend. 751 01:03:35,680 --> 01:03:37,920 Ah ah yes. 752 01:03:37,920 --> 01:03:38,360 Hello. 753 01:03:38,600 --> 01:03:44,040 Yeah, yeah I want you to tell that, uh, can you, can you please upload this video to YouTube? 754 01:03:45,080 --> 01:03:47,160 Yes, yes, I am uploading. 755 01:03:47,160 --> 01:03:51,000 After the completion of today's class, I will upload it. 756 01:03:52,720 --> 01:03:53,360 Uh, okay. 757 01:03:53,400 --> 01:03:53,720 Okay. 758 01:03:53,720 --> 01:03:54,680 All right, all right. 759 01:03:55,720 --> 01:03:57,120 Yeah, I said this side. 760 01:03:57,120 --> 01:04:01,680 Would you like to, uh, share this data as well for these, you know, tables which we have created 761 01:04:01,720 --> 01:04:03,320 here in YouTube? 762 01:04:04,000 --> 01:04:04,480 Uh, yes. 763 01:04:04,480 --> 01:04:08,690 Yes, I will share everything, but, uh, in one video. 764 01:04:08,690 --> 01:04:18,290 I can't put everything so you all can drop your email ID over here and then I will separately mail you 765 01:04:18,290 --> 01:04:19,210 all the scripts. 766 01:04:19,810 --> 01:04:23,090 So shall I, right there in the, uh, chat box itself. 767 01:04:23,490 --> 01:04:23,970 Uh, yes. 768 01:04:23,970 --> 01:04:24,410 Yes, sure. 769 01:04:24,450 --> 01:04:25,050 Yes, yes. 770 01:04:25,570 --> 01:04:26,090 Okay. 771 01:04:27,810 --> 01:04:31,210 Everyone can write, uh, those who require the script. 772 01:04:34,290 --> 01:04:35,610 Hi, this is Mike. 773 01:04:36,090 --> 01:04:39,890 Uh, can you also mail the scripts which were used yesterday? 774 01:04:41,930 --> 01:04:42,930 Okay, sure, sure. 775 01:04:42,930 --> 01:04:48,010 I will, uh, share the script as well in the email. 776 01:04:49,450 --> 01:04:49,690 Okay. 777 01:04:49,690 --> 01:04:50,290 Thank you. 778 01:04:51,370 --> 01:04:51,650 Uh. 779 01:04:54,530 --> 01:04:56,490 Uh, but thank you for this class. 780 01:04:56,530 --> 01:04:57,930 These questions are very helpful. 781 01:04:59,730 --> 01:05:00,410 Uh, yes. 782 01:05:00,410 --> 01:05:00,810 Sure. 783 01:05:00,850 --> 01:05:01,410 Thanks. 784 01:05:02,090 --> 01:05:07,050 Let me just, uh, please, uh, write the email.