1 00:01.520 --> 00:05.120 Assume you are given the table containing Twitter user sessions. 2 00:05.120 --> 00:05.960 Activities. 3 00:06.640 --> 00:15.560 Write a query that ranks users according to their total session durations in minutes in descending order. 4 00:15.560 --> 00:19.960 For each session, type between the start date and end date. 5 00:20.920 --> 00:26.760 This one output the user ID session type and the ranking of the total session duration. 6 00:27.320 --> 00:31.040 So over here we have the table sessions table. 7 00:31.040 --> 00:33.080 Let me okay. 8 00:33.120 --> 00:35.040 This is already created. 9 00:36.000 --> 00:39.240 Let me check whether the data is there or not. 10 00:40.160 --> 00:43.800 Select star from sessions. 11 00:46.400 --> 00:49.520 Okay I have already inserted the data into the same. 12 00:50.120 --> 00:53.000 Now over here what is our requirement? 13 00:53.640 --> 00:58.960 Write a query that ranks users according to their total session duration in minutes. 14 00:59.880 --> 01:02.160 Total session duration in minutes. 15 01:02.840 --> 01:05.800 Okay, let me try to execute this query. 16 01:08.700 --> 01:09.100 Q. 17 01:13.580 --> 01:17.860 So now over here we need to identify the total session durations. 18 01:18.980 --> 01:19.420 So. 19 01:21.700 --> 01:23.540 This is the duration column right. 20 01:23.580 --> 01:25.500 And which is giving in the minutes. 21 01:26.020 --> 01:28.660 So now for each user ID. 22 01:29.540 --> 01:35.180 For each user ID and session type we need to calculate the total session. 23 01:35.860 --> 01:38.100 Suppose for 111 right. 24 01:39.820 --> 01:41.020 For 111. 25 01:41.060 --> 01:43.060 And session type will be like. 26 01:43.540 --> 01:47.180 So let me execute this particular query only. 27 01:52.500 --> 01:52.820 Okay. 28 01:52.820 --> 01:55.980 Let me execute both of this query. 29 02:01.500 --> 02:06.020 So see for the user 111. 30 02:06.460 --> 02:14.340 And if you are considering the like like then it will be six plus two eight right. 31 02:15.340 --> 02:16.700 Six plus two eight. 32 02:17.120 --> 02:22.200 Now for user 2222 for user 222. 33 02:22.240 --> 02:31.560 Like will be three only right like will be three only for user 333 like will be ten, right? 34 02:31.600 --> 02:37.720 Because we are considering the total duration right now. 35 02:37.720 --> 02:41.480 We need to order this particular. 36 02:43.400 --> 02:49.320 We need to order based upon the duration right based upon this particular query. 37 02:49.320 --> 02:50.840 And we need to give rank. 38 02:51.360 --> 02:54.480 We need to give rank based upon that as well. 39 02:55.160 --> 02:55.600 So. 40 02:57.680 --> 02:59.560 Partition by session type. 41 02:59.560 --> 03:04.040 So so that we are taking this particular query inside the city. 42 03:04.040 --> 03:09.160 And then we are using the partition by session type over here. 43 03:09.560 --> 03:14.720 And then order by Tdu means total session duration in descending order. 44 03:17.520 --> 03:20.320 So how the result set will be generated. 45 03:21.840 --> 03:26.300 So let me try to make the data into the Excel file. 46 03:29.700 --> 03:32.100 Let me open the blank Excel file. 47 03:35.860 --> 03:39.620 And let me copy this data into this Excel file. 48 03:44.380 --> 03:45.060 Okay. 49 03:45.420 --> 03:49.380 And now let me try to generate this particular query. 50 03:55.340 --> 03:58.820 And let me see what result set it is generating. 51 04:01.300 --> 04:02.940 So for 333. 52 04:12.340 --> 04:12.740 Okay. 53 04:12.780 --> 04:14.180 Where is the highest rank. 54 04:14.220 --> 04:16.340 It is generating something else. 55 04:17.820 --> 04:18.860 Just a minute. 56 04:21.780 --> 04:28.460 So now for each session type we need to give the rank based upon based upon the duration duration of 57 04:28.460 --> 04:28.900 minutes. 58 04:28.900 --> 04:29.340 Right. 59 04:29.780 --> 04:34.360 So for that purpose rank over partition by clause we have written it. 60 04:34.360 --> 04:35.520 If you see over here. 61 04:35.560 --> 04:37.080 Now in this Excel file. 62 04:37.920 --> 04:45.480 So if you see for the like which is having the highest duration, it will give rank one for the like 63 04:45.480 --> 04:47.280 which is having the second highest duration. 64 04:47.280 --> 04:48.440 It will give rank two. 65 04:48.920 --> 04:54.200 And for the like which is which is having the third highest duration, it will give rank three again 66 04:54.200 --> 04:58.160 for reply, which is having the first highest duration. 67 04:58.160 --> 05:03.800 It will give rank one for reply, which is having the second highest duration. 68 05:03.800 --> 05:11.040 It will give rank two and for retweet which is having the first highest duration, it will give rank 69 05:11.040 --> 05:15.680 one because there is no second highest duration for retweet, right? 70 05:16.720 --> 05:25.560 So this is how we are calculating the total of duration in the first query for user ID and session type 71 05:25.560 --> 05:25.960 wise. 72 05:25.960 --> 05:34.080 And then we are, uh, reordering the data based upon the partition by clause, right partition by session 73 05:34.080 --> 05:36.160 type and order by tdu. 74 05:40.560 --> 05:41.560 So thank you.