1 00:01.880 --> 00:02.880 Hello everyone. 2 00:04.040 --> 00:05.080 Good morning all. 3 00:06.320 --> 00:11.160 So let's have continuation of our Fang and Max SQL queries. 4 00:12.160 --> 00:14.600 So this is our first query of today. 5 00:16.560 --> 00:18.880 So we have given the table actions table. 6 00:18.880 --> 00:24.720 And in that user ID post id action date and action and extra column is given. 7 00:25.560 --> 00:27.640 This table may have duplicate rows. 8 00:28.480 --> 00:38.280 The action column is actually it is a worker of data type and it has some values like you like reaction, 9 00:38.280 --> 00:40.920 comment, report, share. 10 00:42.640 --> 00:49.800 The extra column has optional information about the action such as reason for report or type of reaction. 11 00:50.840 --> 00:53.080 Extra is never null. 12 00:54.160 --> 01:00.400 Write a solution to report the number of posts reported yesterday for each report region. 13 01:02.320 --> 01:06.760 Assume today is five 5th July 2019. 14 01:07.840 --> 01:10.360 So this will be our report region right. 15 01:13.480 --> 01:19.800 Write a solution to report the number of posts reported yesterday for each report region. 16 01:22.240 --> 01:22.880 So now. 17 01:28.200 --> 01:30.160 Note they have given us some note. 18 01:30.200 --> 01:35.800 Note we only care about report regions with non-zero number of reports. 19 01:35.800 --> 01:39.720 Do not consider the none report region. 20 01:39.760 --> 01:40.240 Right. 21 01:40.680 --> 01:42.720 So they have given us the table. 22 01:42.720 --> 01:50.880 We have already created the Excel table based on the given schema, and we have already inserted the 23 01:51.960 --> 01:53.680 data into the Excel table. 24 01:54.440 --> 01:58.730 Now let me select first data from the exchange table. 25 01:59.370 --> 01:59.770 So. 26 02:02.170 --> 02:02.730 Okay. 27 02:03.330 --> 02:10.010 Now we have to see which data we have to consider as yesterday date because they have given us assume 28 02:10.050 --> 02:11.290 today's 5th of July. 29 02:11.290 --> 02:11.690 Right. 30 02:12.250 --> 02:14.050 So 4th of July is our. 31 02:16.210 --> 02:17.530 Yesterday date right. 32 02:18.250 --> 02:21.570 So let me select the data from the 4th July. 33 02:26.850 --> 02:28.370 Action date equal to. 34 02:33.210 --> 02:34.290 4th of July. 35 02:34.650 --> 02:42.690 Now we have to count the action which is report only. 36 02:42.690 --> 02:43.170 Right. 37 02:47.770 --> 02:52.010 So that for that we need to add one more filter over here. 38 02:52.050 --> 02:52.810 Wherein. 39 02:56.010 --> 02:59.530 Action n equal to report. 40 03:00.810 --> 03:08.410 Now based on the group by of this extra column on that particular date, how many action has been done 41 03:08.930 --> 03:10.810 for how many posts has been done? 42 03:11.930 --> 03:13.690 How many distinct posts has been done? 43 03:13.730 --> 03:15.690 We have to identify that one, right? 44 03:16.410 --> 03:22.770 So that over here in some of the extra column, they have given us the none value. 45 03:23.290 --> 03:26.010 So we need to eliminate this none value. 46 03:26.530 --> 03:27.850 So let me do that. 47 03:27.890 --> 03:28.570 Extra. 48 03:29.610 --> 03:30.650 Not equal to. 49 03:33.850 --> 03:34.410 None. 50 03:35.090 --> 03:37.170 So let me select the data. 51 03:38.730 --> 03:49.530 So if you see over here on the 4th of July there are two posts has been done by the two different users. 52 03:50.330 --> 03:50.810 Right. 53 03:51.090 --> 04:00.780 And on the again on the 4th of July There are two different posts has been done by same user, but now 54 04:00.780 --> 04:06.020 our requirement is to calculate the number of posts number of distinct posts. 55 04:06.020 --> 04:06.460 Right. 56 04:07.300 --> 04:07.780 So. 57 04:11.300 --> 04:12.700 Let me select the data. 58 04:15.380 --> 04:25.420 We need to identify extra comma count of distinct post ID. 59 04:27.980 --> 04:28.460 As. 60 04:31.700 --> 04:33.060 Distinct post. 61 04:34.100 --> 04:36.180 Now let me select the data. 62 04:39.180 --> 04:39.500 Okay. 63 04:39.500 --> 04:44.180 Because of we have used the aggregate function we need to use group by right. 64 04:48.260 --> 04:58.060 So if you see over here for the racism two distinct posts are are They're run by the same user as we 65 04:58.100 --> 04:59.100 have seen it already. 66 04:59.580 --> 05:08.620 And for spam, there is one distinct post that's been done by for spam extra reason. 67 05:09.380 --> 05:11.420 So this should be our output. 68 05:15.820 --> 05:18.900 Let me know if you have any doubt in this query. 69 05:19.540 --> 05:22.060 Please put it in the comment section of the video. 70 05:22.420 --> 05:24.580 Now let's jump to the second video. 71 05:25.500 --> 05:26.460 Second query. 72 05:30.620 --> 05:33.820 So they have given us the table customers table. 73 05:34.140 --> 05:41.980 And in the customer table they've given us customer ID and customer name column customer ID column with 74 05:41.980 --> 05:42.700 unique values. 75 05:42.700 --> 05:46.500 For this table, customer name is the name of the customer. 76 05:46.500 --> 05:50.020 And second table they have given us is the orders table. 77 05:50.420 --> 05:53.390 So order ID, customer ID, product name. 78 05:54.110 --> 06:01.870 So here customer ID and customer ID column is common column in both the tables customers table and orders 79 06:01.870 --> 06:02.390 table. 80 06:03.590 --> 06:07.350 Order ID is the column with unique values for this table. 81 06:07.350 --> 06:12.390 Customer ID is the ID of the customer who bought the product name. 82 06:15.270 --> 06:18.630 Now this is very interesting question. 83 06:18.630 --> 06:26.390 Write a solution to report the customer ID and customer name of the customer who bought product key. 84 06:27.990 --> 06:37.310 Product A product B but did not buy the product C since we want to recommend them to purchase this product. 85 06:38.710 --> 06:45.670 Return the result table ordered by customer ID the result format is in the following example. 86 06:46.430 --> 06:52.510 So this is the customer table they have given us and this is the orders table they have given us. 87 06:52.510 --> 06:53.870 So if you see over here. 88 06:55.350 --> 07:01.630 Customer ID one has purchased A, B, D and C, but now what is our requirement? 89 07:02.750 --> 07:08.710 We require only customer who has purchased product a, B but not C. 90 07:08.750 --> 07:13.790 So if you see over here this customer ID three has purchased A. 91 07:14.710 --> 07:26.230 Now again this customer ID three has purchased B, this customer ID three has purchased D but it has 92 07:26.230 --> 07:28.070 not purchased C right. 93 07:29.870 --> 07:32.790 It is not purchased the product name equal to C. 94 07:33.270 --> 07:40.790 So this should be three is our output customer ID three name is Elizabeth will be our output. 95 07:41.670 --> 07:47.590 Now let's I have already created the table and inserted this data into the same. 96 07:48.590 --> 07:51.200 Now let me start with the solution. 97 07:53.760 --> 07:55.680 So if you see over here. 98 07:59.240 --> 08:08.920 First we need to identify the customers who has purchased the product A so we can identify with the. 99 08:11.400 --> 08:13.600 Inner join only right one two and three. 100 08:14.600 --> 08:18.320 So if you see again the table. 101 08:20.800 --> 08:22.840 Customers table and orders table. 102 08:22.880 --> 08:27.040 Let me select the data from the same. 103 08:42.400 --> 08:45.360 So now if you see both of this table. 104 08:51.400 --> 08:59.400 Customer ID one has purchased the product, a then customer ID two has purchased the product, a customer 105 08:59.400 --> 09:04.320 ID three has also purchased the product A so that will be given by this query. 106 09:07.200 --> 09:08.120 123. 107 09:08.120 --> 09:16.400 Right now again we need to identify which customer ID product purchase the product B. 108 09:16.680 --> 09:19.080 So one and three only has purchased the product. 109 09:19.120 --> 09:21.680 B two has not purchased the product B right. 110 09:22.360 --> 09:29.240 So one two if you see two is purchased only product A but not product B, so two is eliminated over 111 09:29.240 --> 09:29.640 here. 112 09:30.080 --> 09:37.640 So now we need to identify those customers who have purchased product A and product B as well as both 113 09:37.680 --> 09:38.120 product. 114 09:38.120 --> 09:38.560 Right. 115 09:39.200 --> 09:41.960 So that we have used the intersect over here. 116 09:44.800 --> 09:46.640 And now what is our requirement. 117 09:47.690 --> 09:51.610 So again we need to use this data so that I have. 118 09:54.650 --> 09:59.130 An encapsulated this result or wrap this result as a derived table. 119 09:59.530 --> 10:03.330 And this entire table is now considered as a T table. 120 10:04.170 --> 10:09.810 And now I am again selecting same columns from this entire derived table. 121 10:09.850 --> 10:11.370 Now what is our requirement? 122 10:12.170 --> 10:13.730 Now our requirement is. 123 10:16.450 --> 10:20.450 We need to accept this result set from. 124 10:23.930 --> 10:28.770 Those customers who has not purchased product C. 125 10:29.330 --> 10:31.570 So if you see over here one and four. 126 10:31.930 --> 10:34.330 So now let me again see this data. 127 10:39.210 --> 10:40.330 Customer one. 128 10:50.970 --> 10:56.210 Customer one has purchased the product C and four has purchased the product C, right? 129 10:57.970 --> 11:01.010 But now we do not require that data, right? 130 11:02.770 --> 11:03.570 One and four. 131 11:03.610 --> 11:05.730 We do not require that data. 132 11:05.730 --> 11:12.010 So we require A and B and they have not purchased the product C. 133 11:12.370 --> 11:14.330 So our results should be three. 134 11:14.730 --> 11:18.370 So what what is this result set is given. 135 11:20.170 --> 11:21.690 It is given one and three. 136 11:27.490 --> 11:29.170 And this is given one and four. 137 11:29.730 --> 11:31.530 So three will be our output right. 138 11:31.530 --> 11:35.090 Because one is common in both the tables. 139 11:35.090 --> 11:37.050 So let me execute this result. 140 11:37.890 --> 11:42.610 So three Elizabeth has purchased only product A and B but not C. 141 11:43.290 --> 11:46.420 That will be our output, right? 142 11:48.540 --> 11:51.140 So let me jump to the next question. 143 11:59.300 --> 12:02.180 So here they have given us the activity table. 144 12:02.380 --> 12:09.380 So in the activity table, player ID, device ID, event date and games played column has been given. 145 12:10.220 --> 12:15.740 Player ID and event date is the primary key combination of columns with unique values of this table. 146 12:17.620 --> 12:21.140 This table shows the activity of the players of some games. 147 12:22.540 --> 12:29.060 Each row is record of a player who logged in, played number of games before logged out on some day 148 12:29.060 --> 12:31.420 using some device. 149 12:32.620 --> 12:40.700 Write a solution to report the fraction of players that logged in again on day after the day first logged 150 12:40.700 --> 12:43.340 in Round. 151 12:43.340 --> 12:45.260 The round. 152 12:45.260 --> 12:45.580 The. 153 12:49.860 --> 12:51.700 Round to two decimal places. 154 12:53.140 --> 12:58.340 In other words, you need to count the number of players that logged in for at least two consecutive 155 12:58.340 --> 13:02.780 days starting from their first login date. 156 13:03.260 --> 13:07.180 Then divide that number by total number of players. 157 13:08.580 --> 13:16.760 So here if you see over here in the activity table, player ID one, device ID one event date is 1st 158 13:16.760 --> 13:18.660 March 1st March. 159 13:19.220 --> 13:23.980 It's the same player ID one is again login on the 2nd March. 160 13:24.340 --> 13:26.740 So this we need to consider right. 161 13:28.860 --> 13:32.180 Because login date will be a consecutive date. 162 13:34.620 --> 13:42.390 Or you can say that event date over here is the date. 163 13:44.550 --> 13:49.590 Right over here and over here. 164 13:51.310 --> 13:52.670 Actually, over here only. 165 13:53.230 --> 13:55.030 So now this. 166 13:55.070 --> 13:55.950 This is the player. 167 13:55.950 --> 13:56.990 How many players won? 168 13:57.030 --> 13:57.790 Only one player. 169 13:57.790 --> 14:03.310 Because if you see second player is even date is only one and third player is two. 170 14:03.350 --> 14:04.070 Even date. 171 14:04.550 --> 14:06.430 But for month of gaps. 172 14:06.430 --> 14:06.870 Right. 173 14:07.430 --> 14:10.430 So only one one divided by how many? 174 14:10.750 --> 14:13.190 One, two and three distinct player id. 175 14:13.190 --> 14:19.910 So one divided by three that will be 0.33 will be our answer, right? 176 14:20.790 --> 14:25.710 So only player with ID one logged back in after the first day he logged in. 177 14:26.190 --> 14:30.470 So answer is one divided by 30.33. 178 14:30.470 --> 14:34.790 So I have already created the table and inserted the data into the same. 179 14:35.590 --> 14:44.510 Now here we need to use the Low function over here to identify the next day of the U. 180 14:44.830 --> 14:45.110 Sorry. 181 14:45.150 --> 14:52.190 Lead function over here to identify the next day of the event of the same player id. 182 14:52.470 --> 14:53.910 So so that. 183 14:58.030 --> 15:08.750 Lead function to identify the next value based upon parameter. 184 15:09.870 --> 15:13.670 So here event date is given right. 185 15:13.870 --> 15:18.710 So if you see over here this is the first match then this is the second match. 186 15:18.950 --> 15:21.630 So that second match will be come over here. 187 15:22.230 --> 15:25.150 Then if you see over here for this user. 188 15:27.390 --> 15:29.110 There is no previous value is there. 189 15:29.110 --> 15:30.110 Or there is no next. 190 15:30.190 --> 15:30.430 Sorry. 191 15:30.430 --> 15:31.750 There is no next value is there. 192 15:31.750 --> 15:32.150 Right. 193 15:32.790 --> 15:34.510 So it will be null only. 194 15:34.510 --> 15:38.430 And for this next value value will be 3rd of July. 195 15:39.320 --> 15:41.200 Now what is our requirement? 196 15:41.240 --> 15:42.680 Now our this. 197 15:42.720 --> 15:46.600 So this will be consider into the first city. 198 15:47.400 --> 15:47.920 Right. 199 15:49.240 --> 15:51.560 So now what will be the second city. 200 15:53.760 --> 15:59.080 If you see over here, the second city will be a use of the first city. 201 15:59.080 --> 16:05.040 And we need to now identify the date difference between these two date into the days. 202 16:05.320 --> 16:07.120 And it should be only one right. 203 16:07.400 --> 16:13.360 Because next day, even date should be the next day of the first even date, right? 204 16:14.560 --> 16:19.040 So there will be only one date difference between these two date. 205 16:19.080 --> 16:22.600 Now we need to identify the players. 206 16:25.200 --> 16:34.200 We need to identify the players who has logged in or whose event date is the next day of the event. 207 16:34.480 --> 16:35.320 First event date. 208 16:35.320 --> 16:35.720 Right. 209 16:36.360 --> 16:39.800 So that will be the distinct player will be one. 210 16:40.440 --> 16:43.360 And now how many total number of players are there? 211 16:43.560 --> 16:46.680 So let me copy this data. 212 16:47.080 --> 16:47.400 Sorry. 213 16:47.440 --> 16:49.240 Copy this column. 214 17:08.160 --> 17:09.560 Now let me check. 215 17:24.680 --> 17:27.000 This is extra so we need to remove it. 216 17:27.560 --> 17:31.040 Now let me execute this query again. 217 17:33.640 --> 17:37.170 So if you see one and three is our result, right? 218 17:38.010 --> 17:46.250 So here we need to do one divided by three and then multiply by 100 to identify the percentage. 219 17:49.090 --> 17:58.530 So .33 will be our answer right one divided by three total number of players we have written over here 220 17:58.530 --> 17:59.370 in the division. 221 18:00.010 --> 18:08.650 And those players who has whose event date is next event date from the first event date. 222 18:09.410 --> 18:09.930 Right. 223 18:09.970 --> 18:11.850 So difference should be the only one. 224 18:13.970 --> 18:15.850 So this should be our output. 225 18:15.850 --> 18:17.690 So thank you so much everyone. 226 18:18.370 --> 18:23.730 We will come again with the few fan questions as soon as possible. 227 18:25.210 --> 18:32.090 And if you like this video, please do comment and share as much as possible and do subscribe to my 228 18:32.090 --> 18:33.130 YouTube channel. 229 18:33.130 --> 18:33.850 Thank you.