1 00:00.760 --> 00:02.760 Let's see another SQL problem. 2 00:03.360 --> 00:05.840 So it is related to gameplay analysis. 3 00:06.320 --> 00:12.880 Given a table of players activity, right to query to report the first login date for each player and 4 00:12.880 --> 00:15.560 the device they use on that first login. 5 00:16.200 --> 00:20.600 Okay, so we are having this particular table structure and data. 6 00:21.640 --> 00:24.480 So let me ask the ChatGPT I am. 7 00:26.880 --> 00:30.280 Having the below table. 8 00:33.400 --> 00:34.520 Structure and data. 9 00:35.680 --> 00:37.960 And let's see what's happening. 10 00:40.960 --> 00:43.440 Okay I have shared my table structure and data. 11 00:44.120 --> 00:44.920 Let's see. 12 00:46.120 --> 00:49.760 Now let me ask the problem statement as well. 13 00:50.480 --> 00:52.680 So this is my problem statement. 14 00:57.360 --> 00:57.960 Okay. 15 00:58.200 --> 01:00.800 So let's see the solution. 16 01:01.120 --> 01:04.520 So this time they are giving the multiple solutions over here. 17 01:04.880 --> 01:08.320 First solution is using a subquery right. 18 01:08.840 --> 01:10.320 So let's see. 19 01:14.250 --> 01:18.730 Solutions provided by the. 20 01:22.410 --> 01:23.370 ChatGPT. 21 01:23.410 --> 01:25.170 Right now. 22 01:25.690 --> 01:29.210 See where we want the first login date. 23 01:31.690 --> 01:32.490 Uh, sorry. 24 01:32.730 --> 01:38.770 Uh, we want the first login date for each player in the device they use on that first login. 25 01:39.450 --> 01:46.210 So over here what we are doing, we are selecting some particular columns from the activity table. 26 01:46.210 --> 01:54.810 And we are saying that where a dot event date equal to select minimum of event date from activity table 27 01:54.810 --> 02:03.770 where player ID equal to a dot player ID, it means it will check for each each inner query. 28 02:03.810 --> 02:09.370 It will check in the outer query, and it will compare that particular player ID, and it will give 29 02:09.370 --> 02:13.050 that minimum of event date for each player ID. 30 02:15.410 --> 02:15.970 Right. 31 02:16.290 --> 02:23.020 And once the minimum of event date it has been given, it will give that player ID even date in the 32 02:23.020 --> 02:26.020 device ID that player has used, right? 33 02:26.500 --> 02:29.700 So this one is we can say correlated subquery. 34 02:30.180 --> 02:34.020 It means we cannot run the inner query individually. 35 02:34.460 --> 02:35.540 We cannot execute. 36 02:35.540 --> 02:35.740 Right. 37 02:35.780 --> 02:37.300 It will give some error. 38 02:38.740 --> 02:41.740 So let me execute both of these query together. 39 02:41.740 --> 02:42.540 And let's see. 40 02:45.580 --> 02:46.540 Okay perfect. 41 02:46.540 --> 02:50.300 So both of the query has given the same answer right. 42 02:51.460 --> 02:57.500 Let me check whether any other solution has been provided by the Czech ChatGPT or not. 43 02:57.780 --> 02:58.300 Yes. 44 02:58.340 --> 02:59.020 Perfect. 45 02:59.460 --> 03:03.500 So this is the second solution provided by ChatGPT over here. 46 03:07.260 --> 03:15.020 In which it has used the window function that is row number window function partition by because we 47 03:15.020 --> 03:15.620 want to. 48 03:18.380 --> 03:21.980 Find the event date for each of the player right ID right. 49 03:22.100 --> 03:26.820 So that is why it is doing partition by player ID and order by event date ascending. 50 03:27.340 --> 03:29.500 And now it will generate the row number two. 51 03:30.820 --> 03:37.230 It will generate the row number to each of the player C for player ID one. 52 03:37.470 --> 03:40.310 Wherever the minimum of even date is there. 53 03:40.630 --> 03:47.870 It will generate the row number one, then second minimum even date it will generate the row number 54 03:47.870 --> 03:48.230 two. 55 03:48.670 --> 03:55.070 Whenever the player ID change, it will generate the row number from one again, right. 56 03:55.190 --> 04:02.350 So now our requirement is we want the data where minimum of even date right. 57 04:02.750 --> 04:10.150 It means our n should be one right R n should be one that record only we want. 58 04:10.510 --> 04:14.070 So that outside the city what we are doing. 59 04:14.630 --> 04:17.750 We are selecting the data from this rank city. 60 04:17.750 --> 04:21.910 And we are giving the filter where r n should be equal to one. 61 04:21.910 --> 04:22.430 Right. 62 04:25.150 --> 04:27.950 So this will also give the same result right. 63 04:28.470 --> 04:32.710 Now let's see whether any other solution has been provided by the ChatGPT or not. 64 04:33.230 --> 04:35.270 No it hasn't provided any other solution. 65 04:35.270 --> 04:35.750 Right. 66 04:36.190 --> 04:40.030 So we will see the next question into the next lecture. 67 04:40.310 --> 04:41.030 Thank you.