1 00:00.360 --> 00:02.320 Let's see another SQL problem. 2 00:03.080 --> 00:05.720 So find team size for each employee. 3 00:06.520 --> 00:12.040 So below is the problem statement given an employee table that includes a team ID. 4 00:12.280 --> 00:16.680 Write a query to find the team size for each employee. 5 00:17.200 --> 00:22.280 The output should show the employees ID and the number of people on their team. 6 00:22.920 --> 00:23.360 So. 7 00:25.440 --> 00:28.040 So this is my employee team table. 8 00:28.040 --> 00:32.960 So let me copy this data and let me ask the ChatGPT. 9 00:33.440 --> 00:41.960 So I am having the below table and data. 10 00:42.280 --> 00:46.200 I am asking the same question question every time. 11 00:48.880 --> 00:49.360 Okay. 12 00:49.400 --> 00:53.960 Let me put my sample. 13 00:56.040 --> 00:57.640 Data to the ChatGPT. 14 00:57.680 --> 00:58.320 Okay. 15 00:58.360 --> 01:00.440 This is a clean and realistic setup. 16 01:01.760 --> 01:02.320 Great. 17 01:02.360 --> 01:04.210 The team based SQL interview questions. 18 01:04.210 --> 01:06.570 Let's confirm your data. 19 01:06.610 --> 01:07.170 Okay. 20 01:09.570 --> 01:16.530 Now let me ask the my problem statement to the ChatGPT and let's see what happens. 21 01:18.690 --> 01:19.090 Okay. 22 01:19.130 --> 01:20.850 This is my problem statement. 23 01:22.970 --> 01:23.690 Okay. 24 01:23.730 --> 01:24.690 Let's see. 25 01:24.730 --> 01:25.210 Okay. 26 01:25.250 --> 01:28.250 It is done using the window function. 27 01:28.890 --> 01:29.170 Right. 28 01:29.170 --> 01:32.890 So this is the solution one we can say right. 29 01:34.890 --> 01:35.450 So. 30 01:38.970 --> 01:44.610 So this is the same thing what I have written right above. 31 01:45.130 --> 01:51.330 So our solution is matching with the ChatGPT only one extra column they have provided. 32 01:51.330 --> 01:51.850 Right. 33 01:53.730 --> 01:56.010 So solution using the. 34 01:59.170 --> 02:02.090 ChatGPT only one extra column they have provided. 35 02:02.610 --> 02:07.170 Now let's see any other solution they have provided okay using a Self-join. 36 02:08.370 --> 02:10.410 So for SQL beginners. 37 02:10.410 --> 02:12.730 So they provide the second solution. 38 02:12.890 --> 02:15.210 So this is the advantage of ChatGPT. 39 02:15.250 --> 02:21.050 ChatGPT will provide multiple solutions to the same problem right. 40 02:21.090 --> 02:23.810 Or we can say multiple approach to the same problem. 41 02:24.250 --> 02:27.770 So let me execute all these three queries and let's see. 42 02:30.290 --> 02:32.570 Okay this is having. 43 02:34.970 --> 02:35.810 Six rows. 44 02:35.850 --> 02:37.250 This is also having six rows. 45 02:37.250 --> 02:40.130 And this is also having six rows. 46 02:40.170 --> 02:45.130 So almost all the query will give the same same uh. 47 02:47.370 --> 02:48.090 Solution. 48 02:48.090 --> 02:48.570 Right. 49 02:57.450 --> 02:57.770 Okay. 50 02:57.810 --> 03:03.770 Because in the last two query there is additional team ID column is there which is not mentioned in 51 03:03.770 --> 03:05.370 our solution. 52 03:05.370 --> 03:05.770 Right. 53 03:06.170 --> 03:12.340 So we can edit by ourself That is also fine. 54 03:15.140 --> 03:16.260 And if you see now. 55 03:19.060 --> 03:20.420 Our solution is matches. 56 03:20.460 --> 03:23.340 Matches with the ChatGPT solution. 57 03:23.340 --> 03:23.820 Right. 58 03:24.220 --> 03:28.620 Now let's see whether any other solution has been provided by ChatGPT or not. 59 03:30.740 --> 03:31.340 Okay. 60 03:31.380 --> 03:34.420 It hasn't provided any other solution, right? 61 03:35.180 --> 03:38.260 So what this count function will do. 62 03:38.660 --> 03:44.940 Count function will count the number of employee for each team ID, right? 63 03:44.980 --> 03:49.060 For each team ID, it will count the number of employees. 64 03:51.180 --> 03:54.300 And we can say this is a windows aggregate function right. 65 03:54.300 --> 03:56.740 Windows aggregate function. 66 03:57.020 --> 03:59.540 And it has done partition by team ID. 67 03:59.540 --> 04:04.540 So for each team ID it will count the number of employees right. 68 04:06.180 --> 04:06.620 Okay. 69 04:06.660 --> 04:09.300 So this is how we can solve the problem. 70 04:09.300 --> 04:12.380 We will see the next question into the next lecture. 71 04:12.380 --> 04:13.020 Thank you.