1 00:00.680 --> 00:01.640 Hello everyone. 2 00:02.600 --> 00:04.320 Welcome back to my channel. 3 00:05.000 --> 00:07.920 So let's solve another SQL problem. 4 00:07.960 --> 00:10.360 Ask in frank companies. 5 00:11.280 --> 00:14.000 So this is regarding sign up activation rate. 6 00:15.400 --> 00:18.000 New TikTok users sign up with their emails. 7 00:18.600 --> 00:24.520 They confirm their sign up by replying to the text confirmation to activate their accounts. 8 00:25.200 --> 00:31.760 Users may receive multiple text messages for account confirmations until they have confirmed. 9 00:35.120 --> 00:41.920 Until they have confirmed their new account, a new senior analyst is interested to know the activation 10 00:41.920 --> 00:45.360 rate of specified users in the emails table. 11 00:46.280 --> 00:48.600 Write a query to find the activation rate. 12 00:49.240 --> 00:52.120 Round the percentage to two decimal places. 13 00:53.160 --> 00:55.520 So here is the definition they have given us. 14 00:55.560 --> 01:02.170 Emails table contain the information of user signup details Tales and text table contains the user's 15 01:02.210 --> 01:04.770 activation information. 16 01:05.490 --> 01:10.410 So here they have given us two different tables email table and text tables. 17 01:11.170 --> 01:17.210 The analyst is interested in the activation rate of specified users in the emails table, which may 18 01:17.210 --> 01:21.530 not include all the users that could potentially be found in the text table. 19 01:22.090 --> 01:23.170 So let's see. 20 01:23.170 --> 01:27.090 This is the emails table, email ID, user ID and sign up. 21 01:27.090 --> 01:36.010 Date is given and this is the text table, text ID, email ID and sign up. 22 01:36.330 --> 01:37.650 Action is given right. 23 01:38.410 --> 01:41.370 So here email ID is common in both the tables. 24 01:41.890 --> 01:45.130 So now let me show you the data from both the tables. 25 01:49.730 --> 01:51.330 So if you see over here. 26 01:55.690 --> 01:58.530 There are 123456 total users are there. 27 01:58.530 --> 01:58.970 Right. 28 01:59.790 --> 02:04.350 And out of which total two users have confirmed only right. 29 02:05.070 --> 02:13.470 Because this email ID is present in this email table and in sign up action only confirm is their own 30 02:13.510 --> 02:14.870 two times only, right? 31 02:15.390 --> 02:18.030 So now how can we solve this? 32 02:18.470 --> 02:18.870 So. 33 02:24.910 --> 02:29.470 So here first of all we need to check how many total users are there. 34 02:29.470 --> 02:29.950 Right. 35 02:30.230 --> 02:35.550 So let me show you six total total users are there right. 36 02:35.990 --> 02:39.230 Because of we are joining email table with text table. 37 02:39.830 --> 02:44.950 And all the email table's entries are there in the text table. 38 02:44.990 --> 02:45.390 Right. 39 02:45.430 --> 02:48.670 123456123456. 40 02:49.110 --> 02:55.670 All email ID are present in email ID column of text table right. 41 02:55.870 --> 02:56.510 So total. 42 02:56.790 --> 02:58.950 That's why I have done inner join over here. 43 02:59.280 --> 03:04.320 So it has given me six count total right now. 44 03:04.360 --> 03:06.200 How many of them are confirmed? 45 03:06.520 --> 03:14.360 So if you see over here, total two users have confirmed only right one and two, 125 user ID and 236 46 03:14.360 --> 03:17.040 user ID right now. 47 03:17.400 --> 03:23.120 Over here I have checked confirm in another SQL query. 48 03:24.480 --> 03:29.560 So in which, uh sign up action is equal to confirm. 49 03:29.560 --> 03:31.880 Now what we need to do we need to. 50 03:34.200 --> 03:37.800 Do this result divided by this result. 51 03:38.680 --> 03:40.520 So this is the confirm result. 52 03:40.880 --> 03:42.760 So confirm result. 53 03:42.760 --> 03:46.960 We need to divide it by total users. 54 03:46.960 --> 03:50.560 So for that we need both the data into a single column. 55 03:50.560 --> 03:50.960 Right. 56 03:51.640 --> 03:54.960 So that we can do using the multiple city over here. 57 03:57.160 --> 04:06.650 So this will be our first city where confirmed users will be taken right then, comma city two as this 58 04:06.650 --> 04:08.530 will become our second city. 59 04:09.970 --> 04:11.330 This is the total count. 60 04:11.370 --> 04:12.130 Total users. 61 04:12.130 --> 04:12.570 Right. 62 04:12.970 --> 04:18.050 And now we will divide confirm users divided by total users. 63 04:18.970 --> 04:26.210 So all this thing I have done because of I need result in two decimal places, right? 64 04:26.250 --> 04:27.930 So let me show you. 65 04:30.250 --> 04:31.930 So two divided by six. 66 04:31.930 --> 04:38.810 So if you do over here ideally two divided by six it will give zero. 67 04:38.850 --> 04:45.970 Let me check it because integer the result should be in floating point right. 68 04:45.970 --> 04:48.970 But over here our data type is integer. 69 04:49.290 --> 04:54.570 So what we need to do we need to do 0.00 again 0.00. 70 04:54.610 --> 04:56.010 Let me try this one. 71 04:59.310 --> 05:01.550 So 0.333. 72 05:01.590 --> 05:02.070 Right. 73 05:02.110 --> 05:04.350 Then we need to do multiply by 100. 74 05:05.030 --> 05:07.150 Now let me check again the result set. 75 05:08.310 --> 05:08.790 Okay. 76 05:08.870 --> 05:11.390 And now we want only two values right. 77 05:11.950 --> 05:13.310 So decimal. 78 05:14.390 --> 05:15.350 Then we need to do. 79 05:23.470 --> 05:24.750 Cost of this one. 80 05:28.030 --> 05:28.470 As. 81 05:34.910 --> 05:35.990 Decimal nine. 82 05:35.990 --> 05:36.750 Comma two. 83 05:40.030 --> 05:41.590 Now let me try this one. 84 05:48.430 --> 05:48.910 Okay. 85 05:54.990 --> 05:56.590 Now let me try this one. 86 05:57.120 --> 05:57.920 it will give you. 87 06:02.360 --> 06:02.680 Okay. 88 06:02.720 --> 06:04.960 This is one extra bracket, is there? 89 06:07.280 --> 06:07.800 Okay. 90 06:07.840 --> 06:12.200 Now we need to, uh, simply multiply by 100. 91 06:15.360 --> 06:17.280 Actually, over here, we need to give four. 92 06:17.320 --> 06:18.760 Let me try this one. 93 06:20.440 --> 06:20.720 Okay. 94 06:20.760 --> 06:21.240 Perfect. 95 06:21.240 --> 06:21.680 Right. 96 06:22.160 --> 06:34.080 Now, what is our requirement from this again we can do a cast and then as decimal nine comma two. 97 06:34.120 --> 06:37.800 Let me do this one and show you the result. 98 06:38.600 --> 06:40.760 So it is giving the perfect result right. 99 06:46.040 --> 06:48.400 Now that same thing is done over here. 100 06:48.680 --> 06:51.280 What I have explained you in this one. 101 06:51.840 --> 06:55.160 The same thing I have done over here, we have done the cast of this. 102 06:55.440 --> 07:00.370 So we can have a rounding feeder up to two decimal places. 103 07:00.810 --> 07:04.010 Then I have again done the round and again done the casting. 104 07:04.650 --> 07:10.530 And over here there is no common column is required in both of these result set. 105 07:10.690 --> 07:13.810 So directly we are doing the cross join. 106 07:13.810 --> 07:16.850 So it will give only single record only right. 107 07:22.650 --> 07:25.170 Okay now let's solve another SQL problem. 108 07:25.610 --> 07:28.290 This is regarding to Supercloud customer. 109 07:28.770 --> 07:32.210 And this question is asked in Microsoft SQL interview. 110 07:33.010 --> 07:40.090 So Microsoft Azure Supercloud customer is defined as a company that purchases at least one product from 111 07:40.090 --> 07:41.450 each product category. 112 07:42.410 --> 07:48.330 Write a query that effectively identifies the customer ID of such supercloud customers. 113 07:48.970 --> 07:55.490 So over here we have given the customer contracts table and products table right. 114 07:56.150 --> 07:59.630 So I have inserted data into both the tables. 115 07:59.910 --> 08:02.590 Now need to identity identify. 116 08:05.990 --> 08:12.630 We need to identify those customers who has by at least one product from each product category. 117 08:14.950 --> 08:19.670 So so now let me show you. 118 08:22.870 --> 08:27.950 Need to identify those customers who has buy at least one product from each product category. 119 08:28.390 --> 08:31.510 So now let me take this data into the Excel file. 120 08:34.470 --> 08:37.310 So you can understand it better. 121 08:45.310 --> 08:46.830 Now let me. 122 08:50.510 --> 08:51.990 Take below data as well. 123 09:00.600 --> 09:02.880 So here they have given the two different tables. 124 09:02.880 --> 09:09.880 And we need to identify those customers who buy at least one product from each product category. 125 09:10.440 --> 09:17.280 So if you see for two so two is coming over here only one time right. 126 09:17.840 --> 09:21.960 So over here how many different categories are there. 127 09:22.000 --> 09:22.680 One, two. 128 09:23.080 --> 09:23.600 Sorry. 129 09:24.000 --> 09:25.640 Analytics is one category. 130 09:26.280 --> 09:27.120 We can say. 131 09:27.160 --> 09:29.280 Then containers is one category. 132 09:30.320 --> 09:31.960 Then compute is one category. 133 09:31.960 --> 09:34.320 So three different categories are there right. 134 09:35.360 --> 09:41.240 Now we need to identify those customer who has buy minimum three. 135 09:43.840 --> 09:46.440 Products from all three different categories. 136 09:46.680 --> 09:49.680 So let's see directly for seven. 137 09:50.000 --> 09:52.160 So seven has purchase one right. 138 09:52.600 --> 09:54.810 So one means analytics Category. 139 09:54.850 --> 09:56.490 You can see this is a product ID. 140 09:56.530 --> 10:02.250 This is also a product ID right now seven is purchased again three. 141 10:02.250 --> 10:04.770 So let me highlight it three. 142 10:05.330 --> 10:07.610 Now three is again different category right. 143 10:07.650 --> 10:10.490 Container category right. 144 10:10.970 --> 10:13.290 And seven has purchased six. 145 10:14.730 --> 10:19.010 So six is compute category right. 146 10:20.610 --> 10:27.370 So it means that seven has purchased minimum three products from all three categories. 147 10:27.410 --> 10:31.770 Now let's understand for four right four. 148 10:32.170 --> 10:34.010 So four is purchased from one. 149 10:34.450 --> 10:36.210 So analytics is covered. 150 10:36.250 --> 10:38.130 Then four is purchased from five. 151 10:38.930 --> 10:40.490 So compute is also covered. 152 10:40.490 --> 10:44.410 But four doesn't has purchase any product from container. 153 10:44.410 --> 10:44.890 Right. 154 10:45.290 --> 10:48.290 So only seven should be our result set right. 155 10:49.730 --> 10:50.010 Now. 156 10:50.010 --> 10:52.330 How can we implement into SQL. 157 10:52.810 --> 10:58.470 So over here First of all, we need to identify how many distinct categories are there? 158 11:01.110 --> 11:03.390 How many distinct categories are there? 159 11:03.510 --> 11:06.070 So total three distinct categories are there right. 160 11:06.670 --> 11:17.550 Now we need to identify those customer who has purchased at least three products from all different 161 11:17.550 --> 11:18.350 categories. 162 11:19.150 --> 11:24.190 Or firstly we are identifying how many distinct product categories they have purchased. 163 11:24.510 --> 11:27.110 So let me show you the result set. 164 11:27.750 --> 11:29.270 This is a simple query right? 165 11:29.310 --> 11:35.270 We are joining two tables with product ID common column and then count of distinct product category. 166 11:35.310 --> 11:41.990 We are taking and over here if you see only seven is coming for three. 167 11:44.670 --> 11:48.190 Three count right three distinct product category count. 168 11:48.790 --> 11:52.230 So now what we need to do from this result set. 169 11:52.950 --> 11:54.560 These are two different results set. 170 11:54.600 --> 12:02.200 Now if we join this result set with this result set based upon this HT column and city column, then 171 12:02.200 --> 12:06.120 our result will become automatically right. 172 12:06.120 --> 12:07.080 That is seven. 173 12:07.480 --> 12:10.480 So again we need to do multiple city for the same. 174 12:11.200 --> 12:17.880 And this time we are doing the city multiple city based upon this city column and HT column. 175 12:18.840 --> 12:19.320 Right. 176 12:20.080 --> 12:21.160 So it is very simple. 177 12:21.160 --> 12:22.720 So it will give only one result right. 178 12:22.720 --> 12:24.440 Because three will be joining with three. 179 12:24.920 --> 12:27.480 No other trees are there over here. 180 12:27.880 --> 12:29.560 So seven will be our answer. 181 12:30.080 --> 12:32.720 So let me again show you this result set. 182 12:37.120 --> 12:37.720 Perfect. 183 12:37.720 --> 12:38.200 Right. 184 12:38.200 --> 12:41.320 So this is how you can solve the SQL query. 185 12:46.120 --> 12:48.320 So let's solve another SQL problem. 186 12:48.520 --> 12:51.720 This is regarding card launch success. 187 12:52.080 --> 12:55.890 And it is asking JP Morgan Chase SQL interview question. 188 12:56.730 --> 13:00.850 So your team at JP Morgan Chase is soon launching a new credit card. 189 13:01.770 --> 13:07.770 You are asked to estimate estimate how many credit cards you will issue in the first month. 190 13:08.650 --> 13:16.730 Before you can answer this question, you want to first get some perspective on how well new credit 191 13:16.730 --> 13:23.290 card launches typically do their first month. 192 13:23.570 --> 13:30.370 Write a query that outputs the name of the credit card and what is the issued amount in this launch 193 13:30.370 --> 13:30.850 month? 194 13:31.610 --> 13:37.130 The launch month is the earliest record in the month card issued table for a given card. 195 13:37.490 --> 13:40.250 So here they have given us monthly card issue table. 196 13:40.290 --> 13:46.090 Right in that card name is given, card issued amount is given issue month and issue year is given. 197 13:46.090 --> 13:46.530 Right. 198 13:47.010 --> 13:49.450 So I have inserted the data into the same. 199 13:49.970 --> 13:51.750 Let me show you the table Well. 200 13:52.030 --> 13:52.750 Script. 201 13:55.190 --> 13:55.510 Sorry. 202 13:55.550 --> 13:56.350 Table data. 203 13:56.670 --> 14:02.670 So if you see over here, we need to identify from this record. 204 14:08.350 --> 14:13.070 We need to identify for each product, each each card name. 205 14:13.070 --> 14:20.750 We need to identify how many issued amount has been done on the first month. 206 14:20.750 --> 14:24.230 Or you can say that launch month of a particular card. 207 14:24.670 --> 14:30.910 So this will be 55,000 for this one and one lakh 50,000 for this one. 208 14:31.430 --> 14:36.310 But you if you observe query fully issue here is different right. 209 14:36.350 --> 14:38.590 2021 and 2020. 210 14:41.070 --> 14:50.830 So here what we need to do I have solved this query based upon two different approaches. 211 14:50.830 --> 14:52.920 So let me show you this approach. 212 14:53.920 --> 14:57.880 Here we are doing row number over partition by card name. 213 14:58.200 --> 15:00.600 So partition by card name for this one. 214 15:00.600 --> 15:03.200 It will generate row number for this one. 215 15:03.200 --> 15:05.200 Also it will generate row number again. 216 15:05.680 --> 15:09.480 So now row number over partition by card name order by issue here. 217 15:09.480 --> 15:10.440 And issue month. 218 15:11.640 --> 15:20.040 So if you see over here it will give row number like this 1234. 219 15:20.080 --> 15:22.840 Again 123 and four. 220 15:23.320 --> 15:25.440 So all this one is our requirement right. 221 15:25.440 --> 15:27.120 Let me show you this result. 222 15:29.160 --> 15:30.720 12341234. 223 15:30.720 --> 15:34.480 Right which I have explained you in this Excel file. 224 15:34.480 --> 15:34.960 Right. 225 15:35.800 --> 15:45.040 So now from here I need to identify only rank one data. 226 15:45.040 --> 15:45.240 Right. 227 15:45.240 --> 15:48.880 Because issue one should be minimum as per the year. 228 15:50.410 --> 15:51.170 Right. 229 15:51.570 --> 15:57.690 So I need only rank rank value one so that we can get it through the derived table. 230 15:57.690 --> 16:06.370 So this one you can say that derived table in SQL server an inline view in. 231 16:08.530 --> 16:10.410 Oracle you can say that. 232 16:10.890 --> 16:13.530 So this is our result set. 233 16:13.890 --> 16:17.410 Now this one will become our one table. 234 16:17.410 --> 16:21.370 So I have given this particular query as table t. 235 16:21.530 --> 16:23.730 So this t will become now our table. 236 16:24.210 --> 16:33.370 So select this card name issue month issuer issued amount from this particular table where arc equal 237 16:33.370 --> 16:33.690 to one. 238 16:33.690 --> 16:36.170 Because we want arc equal to one only right. 239 16:41.530 --> 16:43.930 So this will give arc equal to one. 240 16:49.830 --> 16:54.710 Now, another approach to solve this question is CTE right? 241 16:55.070 --> 16:58.030 So same thing we have mentioned over here. 242 16:58.030 --> 17:03.870 And write the CTE above it in encapsulate this result into the city. 243 17:03.870 --> 17:09.910 And then select all the columns where k equal to one right. 244 17:12.030 --> 17:15.030 So this is just the basic explanation. 245 17:18.390 --> 17:23.430 So this is how we can solve the one problem with multiple different approaches. 246 17:23.430 --> 17:23.910 Right. 247 17:29.030 --> 17:31.070 So thank you so much everyone. 248 17:31.990 --> 17:39.510 If you like the way of my teaching then please do like comment, subscribe and share with your friends. 249 17:40.470 --> 17:44.310 And please uh, do repost in LinkedIn as well. 250 17:45.310 --> 17:46.430 Thank you so much. 251 17:47.510 --> 17:49.310 We will meet again in the next video. 252 17:49.310 --> 17:49.950 Thank you.