1 00:00.280 --> 00:02.360 Let's see another SQL question. 2 00:02.680 --> 00:05.720 Find the second most recent activity. 3 00:06.000 --> 00:10.440 So below is the problem statement given a table of user activities. 4 00:10.960 --> 00:15.200 Write a query to find the second most recent activity for each user. 5 00:15.720 --> 00:21.240 If a user has only one activity, then that activity should be returned. 6 00:21.480 --> 00:22.680 Okay, fine. 7 00:22.880 --> 00:27.400 So these are the table structure and data with me. 8 00:28.040 --> 00:32.560 So let's see I am having. 9 00:35.800 --> 00:41.840 Having the below table structure and data. 10 00:42.520 --> 00:47.400 Now it will ask to please share your table structure and data with us right. 11 00:47.600 --> 00:50.720 So I am sharing my table structure and data with the ChatGPT. 12 00:51.600 --> 00:56.920 So it will populate the data in the tabular format. 13 00:56.920 --> 01:05.690 And that will be easy to analyze or easy to visualize right now, let me ask the question as well or 14 01:05.690 --> 01:07.370 the problem statement as well. 15 01:08.850 --> 01:09.130 Right. 16 01:09.130 --> 01:12.570 So I am sharing my problem statement now. 17 01:16.010 --> 01:17.690 And let's see okay. 18 01:18.090 --> 01:18.570 So. 19 01:22.650 --> 01:27.010 It is provided the solution using the window function. 20 01:27.010 --> 01:30.850 And it is almost matching with our solution as well. 21 01:31.130 --> 01:34.330 So we can say solution. 22 01:36.930 --> 01:37.690 By the. 23 01:39.890 --> 01:43.250 ChatGPT right solution provided by ChatGPT. 24 01:44.210 --> 01:45.450 Now we want. 25 01:47.650 --> 01:57.290 Second uh, we want second most activity or whenever the second most activity is not available. 26 01:57.290 --> 02:03.190 In that particular case we want the single activity or the first activity only. 27 02:03.190 --> 02:03.630 Right? 28 02:04.230 --> 02:05.590 That is our requirement. 29 02:06.230 --> 02:12.470 So they have solved this problem using the row number function row number over partition by username 30 02:12.470 --> 02:13.830 order by start date. 31 02:14.110 --> 02:19.830 So whenever the start date is highest, it will give the rank one. 32 02:20.270 --> 02:26.270 Whenever the start uh start date is second highest, it will give the rank two for each username. 33 02:26.270 --> 02:26.750 Right. 34 02:26.990 --> 02:34.310 So if you execute this particular query, if you execute this particular query see it is giving the 35 02:34.310 --> 02:40.190 rank right rank one, two and three for each distinct username. 36 02:40.190 --> 02:40.630 Right. 37 02:41.150 --> 02:43.430 Now that is fine. 38 02:43.750 --> 02:46.230 So we want our n equal to two. 39 02:46.670 --> 02:52.310 But what another thing we want we want another thing is total activities. 40 02:52.310 --> 02:59.590 So wherever whenever this total activity equal to one total activity equal. 41 03:02.640 --> 03:04.800 Equal to one that data also we want. 42 03:04.800 --> 03:05.200 Right. 43 03:05.680 --> 03:11.760 So uh, this second call uh derived column is used for total activity. 44 03:11.760 --> 03:15.600 And first derived column is used for giving the row number. 45 03:16.080 --> 03:16.600 Right. 46 03:18.680 --> 03:26.120 So we have to put the Or condition over here whenever r n equal to two or total activity is equal to 47 03:26.160 --> 03:26.560 one. 48 03:26.920 --> 03:32.800 Because whenever the total activity is one there is no r n equal to two, value will be there right. 49 03:35.640 --> 03:39.040 So that is why we have derived the two columns over here. 50 03:39.040 --> 03:42.280 One is r n and another one is total activities. 51 03:42.760 --> 03:43.280 Right. 52 03:44.000 --> 03:47.440 Now let's see whether ChatGPT has provided any other solution. 53 03:47.920 --> 03:53.000 I think not only the one solution ChatGPT has provided. 54 03:53.000 --> 03:57.120 Right, which is almost matching with our solution. 55 03:57.120 --> 03:57.560 Right. 56 03:58.160 --> 04:00.520 So we will see the next question into the next lecture. 57 04:00.520 --> 04:01.160 Thank you.