1 00:01.200 --> 00:01.920 Hello all. 2 00:02.640 --> 00:03.800 Good morning everyone. 3 00:05.000 --> 00:07.320 So let's solve another SQL problem. 4 00:08.520 --> 00:14.680 So here they have given us the activity table in the activity table, player ID, device ID, event 5 00:14.680 --> 00:23.480 date and games played by each of the player has been given player ID and event date is the primary key 6 00:24.800 --> 00:25.840 of this table. 7 00:27.480 --> 00:32.200 This table shows the activity of players of some games. 8 00:33.720 --> 00:36.720 Each row is a record. 9 00:36.720 --> 00:40.640 Each row is a record of a player who logged in and played a number of games. 10 00:41.000 --> 00:50.120 Before logging out on some day using some device, write a solution to report the right solution to 11 00:50.120 --> 00:52.240 report the device that is first logged in. 12 00:52.280 --> 00:56.520 For each player, you can return the result table in any order. 13 00:57.400 --> 00:59.840 The result table is in following example. 14 01:02.320 --> 01:04.360 So this is the activity table. 15 01:05.040 --> 01:14.270 Now we have to identify the device ID of the first event date, right? 16 01:15.950 --> 01:20.630 So let me show player ID one. 17 01:20.630 --> 01:28.270 Player ID one was played on 1st of March in its device ID is two. 18 01:28.630 --> 01:30.470 So that should be our result set. 19 01:31.350 --> 01:40.670 Then player id ID two is has only one event date, so automatically three will be the device ID of that. 20 01:40.670 --> 01:41.110 Right. 21 01:41.910 --> 01:43.350 And then player ID three. 22 01:44.030 --> 01:47.950 So its first event date is 2nd of March. 23 01:48.510 --> 01:50.310 So one is the device ID of. 24 01:54.150 --> 01:55.230 Player ID three. 25 01:55.950 --> 01:59.830 So I have created the table and inserted the data into the same. 26 02:00.630 --> 02:01.110 Now. 27 02:03.310 --> 02:10.750 Here what we need to do here we need to do either of the any rank function we have to use, whether 28 02:10.750 --> 02:12.870 rank or dense rank or row number. 29 02:13.190 --> 02:14.670 Anyone we can use. 30 02:15.670 --> 02:16.110 So. 31 02:17.690 --> 02:24.410 here we need to do partition by player ID so partition by player ID because we want to find the each 32 02:24.410 --> 02:27.370 of the players first event date. 33 02:27.370 --> 02:27.810 Right. 34 02:28.250 --> 02:34.330 So first event date means it is a minimum of event date not the maximum of event date. 35 02:34.810 --> 02:38.890 So we need to do partition by player ID and then order by event date. 36 02:39.290 --> 02:45.970 And here we do not need to use descending because if we use descending then it will give the maximum 37 02:45.970 --> 02:46.850 of event date. 38 02:46.850 --> 02:52.330 But our requirement is to have minimum of event date right. 39 02:53.810 --> 02:57.850 So this will be give us minimum. 40 02:58.690 --> 03:02.250 This will give us the rank of each of the player ID. 41 03:04.650 --> 03:12.770 With its event date based on the ascending order, because by default it is ascending order. 42 03:13.370 --> 03:19.610 So one two, then player two is only one event date. 43 03:19.610 --> 03:21.250 So it has given only one rank. 44 03:21.250 --> 03:21.690 Right? 45 03:22.010 --> 03:25.680 And player ID three is one two different event dates. 46 03:25.680 --> 03:31.080 So minimum of event date it is given one rank and second minimum. 47 03:31.080 --> 03:35.520 Or you can say that maximum of event date it is given second rank. 48 03:36.000 --> 03:40.040 Now our requirement is to r k equal to one. 49 03:40.560 --> 03:48.720 So because of this r k is not a specific column is there in the our main table. 50 03:49.800 --> 03:53.600 So we I have wrap up this code into the city. 51 03:54.280 --> 04:03.880 And then outside city I have mentioned r k equal to one because we want only minimum of even date device 52 04:03.880 --> 04:04.520 ID right. 53 04:05.040 --> 04:12.080 So now I will select the player ID and device ID from the main table. 54 04:13.280 --> 04:16.000 So these are the player ID and device ID. 55 04:19.440 --> 04:22.280 Of the minimum event date by each player. 56 04:24.080 --> 04:26.080 So thank you so much everyone. 57 04:27.000 --> 04:30.200 We will meet again in the next video as soon as possible. 58 04:30.200 --> 04:31.000 Thank you.