1 00:03.160 --> 00:04.440 Good afternoon all. 2 00:05.240 --> 00:10.640 So let's have the continuation of our Fang and Max SQL queries. 3 00:11.720 --> 00:12.120 So. 4 00:15.200 --> 00:22.840 They have given us the table activity table in in the activity table, player ID, device ID, event 5 00:22.840 --> 00:31.080 date and games played column are given in player ID and event date is primary key of this table. 6 00:31.880 --> 00:35.960 This table shows the activity of players of some games. 7 00:37.080 --> 00:41.120 Each row is a record of a player who logged in and played a number of games. 8 00:41.760 --> 00:45.760 Before logging out on some day using some device. 9 00:48.040 --> 00:51.040 Write a solution to report for each player and date. 10 00:51.600 --> 00:54.400 How many games played so far by the player. 11 00:57.560 --> 01:05.380 That is the total number of games played by the player until that date, and check the example for clarity. 12 01:05.980 --> 01:07.780 Return the result table in any order. 13 01:07.780 --> 01:10.620 The result format is in the following example. 14 01:11.500 --> 01:14.420 So here is the input table. 15 01:15.340 --> 01:18.100 Player ID device ID event date in games played. 16 01:18.780 --> 01:27.580 So one player is playing a game on different dates and he is playing different games on one particular 17 01:27.580 --> 01:28.060 date. 18 01:28.740 --> 01:31.260 Suppose on 1st March he has played five games. 19 01:31.540 --> 01:39.380 Then 2nd May he has played six games, then 25th of June he has played one game right. 20 01:40.220 --> 01:42.140 So here is our requirement is. 21 01:44.220 --> 01:44.740 Right. 22 01:44.740 --> 01:46.820 Solution to report for each player. 23 01:46.820 --> 01:47.460 End date. 24 01:47.860 --> 01:50.740 How many games played so far by the player. 25 01:52.180 --> 01:52.700 Right. 26 01:54.100 --> 02:01.160 So on a particular date How many games has been played by each of the player. 27 02:01.720 --> 02:04.160 So let me show you the. 28 02:07.960 --> 02:08.800 Input table. 29 02:08.800 --> 02:11.640 This is the input activity table they have given us. 30 02:12.160 --> 02:13.920 And below is the output table. 31 02:13.920 --> 02:22.360 So if you see over here on the first match user is player one has played the five five games right on 32 02:22.360 --> 02:23.600 the first match. 33 02:28.080 --> 02:30.080 Then on the 2nd May. 34 02:32.200 --> 02:37.480 This user this player has placed the five plus 11. 35 02:38.120 --> 02:40.200 So it is 16 games right. 36 02:40.880 --> 02:43.040 And on the 25th of June. 37 02:54.760 --> 02:57.630 So let me show you the output first. 38 03:07.190 --> 03:09.510 So on the on the 20th. 39 03:10.270 --> 03:10.710 Sorry. 40 03:11.110 --> 03:12.070 Over here. 41 03:12.590 --> 03:16.270 Five then five plus six is equal to 11 will come. 42 03:16.550 --> 03:23.550 And then over here on the 25th of June this one player ID one has played one more game. 43 03:23.710 --> 03:25.950 So 11 plus one equal to two. 44 03:28.430 --> 03:28.990 Right. 45 03:29.510 --> 03:32.790 So this should be the output now. 46 03:36.230 --> 03:43.430 Here they have given us the explanation for the player with ID one five plus 611 games played on 22nd 47 03:43.430 --> 03:48.870 of May and five plus six plus 112 games played on 25th of June. 48 03:49.590 --> 03:50.190 Right. 49 03:50.190 --> 03:53.510 So I have created the table and inserted the data into the same. 50 03:54.090 --> 03:57.170 Now for this one we have to use the. 51 03:59.970 --> 04:01.770 Aggregate function sum with. 52 04:04.050 --> 04:07.770 With the use of ranking function over and partition by clause. 53 04:07.970 --> 04:09.330 Because we have to do. 54 04:10.050 --> 04:16.570 Identify the data based upon each of the player ID right like player ID one, player ID three like that. 55 04:16.970 --> 04:22.290 So for that we need to do partition by player ID and then order by even date. 56 04:22.290 --> 04:24.090 So if you run this query. 57 04:27.450 --> 04:35.890 You will get most of the results like first player ID one on 1st May is five game right, then player 58 04:35.890 --> 04:44.570 ID one on 2nd of May it will be a 11 game like right five plus six. 59 04:44.890 --> 04:51.810 So if you run this both query together then you will able to get to know five. 60 04:52.070 --> 04:54.150 then five plus six is equal to 11. 61 04:54.750 --> 04:58.430 Then again on 25th June, one more game has been played. 62 04:58.430 --> 05:01.390 So 1211 plus 112. 63 05:01.990 --> 05:03.710 Then this playwright is different right? 64 05:03.710 --> 05:07.630 So it is zero starting with zero and zero plus five equal to five. 65 05:08.310 --> 05:17.470 So for this purpose we have used the sum of games played because we need to do some of these games played 66 05:17.470 --> 05:18.230 column right. 67 05:19.150 --> 05:23.510 And then over partition by player ID and order by event date. 68 05:23.990 --> 05:29.470 And then we have just done the order by player ID and games played so far. 69 05:31.830 --> 05:40.510 So this output is exactly match with the output has been given over here in the question. 70 05:40.510 --> 05:40.790 Right. 71 05:40.830 --> 05:42.430 Five 1112 zero five. 72 05:43.510 --> 05:45.430 So this is our first query. 73 05:46.550 --> 05:48.710 Now let's jump to the second query. 74 05:51.570 --> 05:59.090 So they have given us the enrollment table over here and in the enrollment table, student ID, course 75 05:59.090 --> 05:59.530 ID. 76 06:02.570 --> 06:05.570 Student ID, course ID and grade column is given. 77 06:07.090 --> 06:13.810 Right solution to find the highest grade with its corresponding course for each student. 78 06:17.210 --> 06:24.090 In case of a tie, you should find the course with the smallest course ID in the result table ordered 79 06:24.090 --> 06:26.130 by student ID in ascending order. 80 06:27.450 --> 06:28.130 So here. 81 06:30.330 --> 06:39.490 We need to find the highest grade based upon the course student id id in the which of the course that 82 06:39.490 --> 06:44.930 particular student has got the highest grade that course ID also we need to mention. 83 06:44.930 --> 06:54.790 So for if you see for Student ID, one student ID one, course ID two and 99 is the highest grade ten 84 06:54.790 --> 06:55.870 student ID two. 85 06:58.950 --> 07:01.790 95 is coming two times, right? 86 07:02.270 --> 07:07.030 So in that case, we need to mention the lowest of the course ID right. 87 07:08.670 --> 07:11.670 Lowest of the course ID which is mentioned over here. 88 07:12.030 --> 07:16.870 In case of a tie you should find the course with smallest course ID. 89 07:17.350 --> 07:19.630 So in this case this will be written. 90 07:19.630 --> 07:24.190 And over here in 382 will be written right. 91 07:27.390 --> 07:28.390 So now let's. 92 07:31.190 --> 07:32.430 Jump to the solution. 93 07:32.950 --> 07:37.190 I have already created the enrollments table and insert the data into the same. 94 07:37.790 --> 07:39.830 Now let's jump to the solution. 95 07:40.550 --> 07:47.850 So here we need to use the rank function rank over partition by student ID and order by grade descending 96 07:47.850 --> 07:52.090 because we want highest grade at the first side. 97 07:52.090 --> 07:52.370 Right. 98 07:52.410 --> 07:54.770 And course ID right. 99 07:55.210 --> 07:57.090 So if you see over here. 100 08:01.770 --> 08:08.530 Select student ID course ID grade rank over partition by student ID order by grade. 101 08:09.690 --> 08:12.450 So here we have used the rank function. 102 08:12.450 --> 08:13.450 Rank rank function. 103 08:13.450 --> 08:13.810 Right. 104 08:14.210 --> 08:21.530 And we have done the partition by student ID because we need to we require data based on the each of 105 08:21.530 --> 08:22.930 the student ID right. 106 08:25.050 --> 08:28.450 So party by my student ID and then descending course ID. 107 08:28.850 --> 08:30.170 Now what is our requirement. 108 08:30.170 --> 08:32.690 We require only the number one. 109 08:33.490 --> 08:40.930 So over here for 295 where the course ID is less that value is coming over here right. 110 08:46.800 --> 08:52.400 So from this result set, our requirement is all the one right? 111 08:52.400 --> 08:55.480 All the one, whichever has the rank ID. 112 08:59.880 --> 09:01.080 And then this one. 113 09:06.920 --> 09:08.800 So this will be our output. 114 09:13.600 --> 09:17.200 Let me organize this table. 115 09:19.240 --> 09:19.800 Okay. 116 09:21.240 --> 09:28.560 So now from this result set I require only r k equal to one data. 117 09:28.600 --> 09:36.720 So I cannot write it over here because if I write k equal to one then it will generate the error right. 118 09:37.280 --> 09:39.800 Because we cannot use r k column over here. 119 09:40.160 --> 09:46.340 So that I have encapsulated this block into the city and then outside the city. 120 09:46.340 --> 09:50.860 I have mentioned the filter condition where arc equal to one. 121 09:53.380 --> 09:55.420 So this will be our result set. 122 09:56.900 --> 09:59.420 Now let's jump to the next query. 123 10:03.340 --> 10:06.620 So here they have given us the products table in the product ID. 124 10:07.020 --> 10:09.860 Product ID is the primary key column for this table. 125 10:10.180 --> 10:13.500 Low phase is in the low phase. 126 10:13.500 --> 10:19.780 Actually the value of yes or no where Y means this product is low fat and means it is not. 127 10:20.220 --> 10:26.860 And same as for the recyclable where y means this product is recyclable means it is not. 128 10:27.340 --> 10:33.620 So write a solution to find the IDs of the products that are both low fares and recyclable. 129 10:34.380 --> 10:36.660 Return the result table in any order. 130 10:37.380 --> 10:39.820 The result format is in following order. 131 10:40.020 --> 10:46.800 So here we need to identify the IDs of the products that are both in low phase and recyclable. 132 10:46.960 --> 10:50.200 So I if you see this is the table data. 133 10:50.200 --> 10:56.160 And over here if you see we need low phase equal to yes and recyclable equal to yes. 134 10:56.640 --> 10:57.640 So this one. 135 11:01.480 --> 11:02.440 And this three. 136 11:02.680 --> 11:05.000 Both have both have. 137 11:05.040 --> 11:05.480 Yes. 138 11:05.480 --> 11:06.720 In both the columns. 139 11:06.720 --> 11:07.080 Right. 140 11:07.120 --> 11:07.800 Low phase. 141 11:08.240 --> 11:08.600 It is. 142 11:08.600 --> 11:09.000 Yes. 143 11:09.560 --> 11:10.640 And recyclable. 144 11:10.680 --> 11:11.560 Or also it is. 145 11:11.560 --> 11:11.920 Yes. 146 11:11.920 --> 11:15.400 So this should be our output one and three product. 147 11:15.960 --> 11:17.520 Product ID one and three. 148 11:17.880 --> 11:20.520 So how can we write it into the SQL. 149 11:20.920 --> 11:24.440 I have already created the table and inserted the data into the same. 150 11:25.200 --> 11:32.040 So here we just need to mention the filter condition where low phase equal to yes and recyclable equal 151 11:32.040 --> 11:33.600 to yes means why. 152 11:34.480 --> 11:39.900 So product ID one and three are the low phase and recyclable Products. 153 11:42.260 --> 11:42.700 Right. 154 11:43.100 --> 11:44.340 So thank you so much. 155 11:44.340 --> 11:46.820 Let's me jump to the next question. 156 11:48.900 --> 11:51.220 So here they have given us the twist table. 157 11:51.460 --> 11:54.020 So tweet ID is the primary key for this table. 158 11:54.380 --> 11:57.780 And this table contains all the tweets in the social media app. 159 11:58.940 --> 12:02.860 So write a solution to find the IDs of tweets. 160 12:03.740 --> 12:06.460 So how can we identify the invalid tweets. 161 12:07.340 --> 12:13.660 The tweet is invalid if the number of characters used in the content of the tweet is strictly greater 162 12:13.660 --> 12:14.580 than 15. 163 12:15.340 --> 12:18.140 Return the result table in any order. 164 12:19.300 --> 12:22.700 So here tweet id and content is given. 165 12:22.700 --> 12:28.180 So in the content they have some kind of string has been given. 166 12:28.660 --> 12:38.280 So whenever this string length is greater than 15 at that time we can say that it is a invalid tweet 167 12:38.280 --> 12:39.680 or invalid content. 168 12:40.320 --> 12:43.040 So this is a very simple one. 169 12:44.920 --> 12:48.480 I have already created the table and inserted data into the same. 170 12:48.800 --> 12:52.960 Here we need to use the length function which is inbuilt SQL function. 171 12:53.440 --> 12:57.240 And in that length function we can give content as parameter. 172 12:57.720 --> 13:02.440 And whenever the content greater than 15 it will be invalid. 173 13:02.680 --> 13:03.240 Tweet. 174 13:03.680 --> 13:09.560 So this tweet id two has the invalid length of characters in the. 175 13:12.240 --> 13:13.520 Content column. 176 13:13.760 --> 13:17.440 So this will be treated two is invalid treat. 177 13:19.120 --> 13:20.200 So thank you so much. 178 13:20.200 --> 13:22.440 Let me jump to the next question. 179 13:26.360 --> 13:31.760 So here they have given us the store table ID is the primary key for this table. 180 13:31.760 --> 13:38.020 And each row contains information about amount of 1 billion the in the customer associated with it. 181 13:38.740 --> 13:44.220 Write a solution to report the number of customers who had at least one bill with an amount strictly 182 13:44.220 --> 13:45.740 greater than 500. 183 13:46.500 --> 13:47.940 So here, if you see over here. 184 13:47.980 --> 13:50.780 Bill ID, customer ID and amount. 185 13:51.340 --> 13:58.580 So if you see for this bill amount we can say that it is greater than 500 right. 186 13:58.980 --> 14:04.780 So at least at least one bill which should be greater than 500. 187 14:06.060 --> 14:06.580 Right. 188 14:06.620 --> 14:08.820 So for this one it is not qualified. 189 14:08.820 --> 14:09.180 Right. 190 14:09.700 --> 14:16.700 Because it is not greater than it is not greater than 500. 191 14:16.740 --> 14:16.900 Right. 192 14:16.940 --> 14:22.740 And for this one again it is amount greater than 500. 193 14:23.700 --> 14:29.660 So bill ID six and bill ID four. 194 14:32.420 --> 14:36.970 Sorry bill ID 11 has number of two different customers. 195 14:37.890 --> 14:40.130 Which bill amount is greater than 500? 196 14:40.530 --> 14:44.450 So customer one has two bills with amount strictly greater than 500 right. 197 14:44.970 --> 14:50.490 Customer two does not have any bills with amount strictly greater than 500. 198 14:50.650 --> 14:55.850 Customer three has one bill with amount strictly greater than 500. 199 14:56.210 --> 14:59.770 So I have already created the table and inserted data into the same. 200 15:00.290 --> 15:05.050 Here we need to do group by customer ID and amount should be greater than 500. 201 15:05.490 --> 15:08.610 So one and three two customer ID are there right. 202 15:08.930 --> 15:14.010 Now we need count of this this two this customer ID column. 203 15:14.490 --> 15:17.250 So count of so we cannot. 204 15:20.090 --> 15:23.530 Let me try to do count over here itself. 205 15:33.790 --> 15:39.350 So it because of the way we use the group by this is two distinct customer ID. 206 15:39.390 --> 15:43.030 It is coming two times but we do not require that right. 207 15:43.470 --> 15:49.350 We require only two is a count customer ID one and customer ID. 208 15:53.230 --> 15:53.670 Three. 209 15:54.070 --> 15:54.590 Right. 210 15:54.870 --> 15:58.070 So that I have encapsulated this block into the CTE. 211 15:58.790 --> 16:05.110 And then outside the city I have just mentioned the count of customer ID is reached count from city. 212 16:05.550 --> 16:08.030 So it will give me reach count equal to two. 213 16:09.710 --> 16:11.550 So thank you so much everyone. 214 16:13.030 --> 16:18.790 We will come again with the few more questions as soon as possible. 215 16:20.150 --> 16:26.230 If you have liked this video, please do like, comment and share as much as possible and please kindly 216 16:26.230 --> 16:28.670 subscribe to my YouTube channel. 217 16:28.670 --> 16:29.470 Thank you.