1 00:01.160 --> 00:02.080 Hello everyone. 2 00:02.880 --> 00:03.800 Good morning all. 3 00:04.600 --> 00:06.600 So let's solve another SQL problem. 4 00:06.640 --> 00:08.400 As in product based companies. 5 00:09.240 --> 00:16.480 Assume you are given a table containing job posting from various companies on the LinkedIn platform. 6 00:17.960 --> 00:24.720 Write a query to retrieve the count of companies that have posted duplicate job listings. 7 00:25.560 --> 00:28.320 So duplicate job listings are defined as. 8 00:31.080 --> 00:38.000 Two job listings within the same company that share identical titles and descriptions. 9 00:38.920 --> 00:42.560 So now we have given the table job listings table. 10 00:43.280 --> 00:48.600 And in that company ID title, job ID and description is given, right? 11 00:49.240 --> 00:52.560 I have already inserted the data into the same. 12 00:53.440 --> 00:57.200 Now how can we consider as the duplicate job listing. 13 00:58.160 --> 01:00.600 So now let me execute this query. 14 01:01.200 --> 01:04.000 So if two different companies has. 15 01:06.600 --> 01:10.120 Title and description. 16 01:11.360 --> 01:18.400 If the two different companies as title and description is same, then that company, we can consider 17 01:18.400 --> 01:23.520 it as a duplicate job listing, right? 18 01:24.160 --> 01:27.680 So now how can we solve that? 19 01:27.680 --> 01:29.080 It is very simple, guys. 20 01:29.440 --> 01:30.400 We need to do. 21 01:33.160 --> 01:34.280 Group by and having. 22 01:34.280 --> 01:34.720 Right. 23 01:35.760 --> 01:36.840 So let me. 24 01:40.400 --> 01:45.720 We need to consider as a title and description should be same right. 25 01:47.680 --> 01:56.160 So title select title then uh come uh company ID title description. 26 01:58.240 --> 02:02.920 So over here we have uh consider the company ID as well. 27 02:02.920 --> 02:04.680 So let me execute this query. 28 02:07.600 --> 02:08.000 Okay. 29 02:08.000 --> 02:12.200 Let me show you the entire table okay. 30 02:12.240 --> 02:15.200 So if you see over here 244 is repeating over here. 31 02:15.200 --> 02:15.640 Right. 32 02:15.960 --> 02:18.080 Company ID is repeating again. 33 02:18.080 --> 02:21.520 Two, four, four is repeating over here. 34 02:22.080 --> 02:22.680 Now. 35 02:25.080 --> 02:26.720 If you see four, three, four, five. 36 02:27.200 --> 02:32.440 So again, apart from the job ID, everything is, uh, same, right? 37 02:33.040 --> 02:36.200 So that we can consider as a duplicate job listing. 38 02:40.040 --> 02:40.600 Right. 39 02:40.680 --> 02:46.200 So now over here, what we need to do, we need to identify the company ID only, right. 40 02:46.240 --> 02:51.680 245345 and 244345 and 827. 41 02:54.600 --> 02:56.280 So that is a very simple thing. 42 02:56.760 --> 03:00.840 We need to encapsulate this block in the select statement. 43 03:00.840 --> 03:09.080 Let me write select company ID from right then enter this thing. 44 03:09.720 --> 03:13.960 Then we can give alias to this particular uh query. 45 03:15.000 --> 03:18.080 Now let me copy this one. 46 03:21.600 --> 03:23.400 And let me execute this one. 47 03:24.880 --> 03:30.920 So it will give (245) 324-4345 and 8 to 7. 48 03:30.920 --> 03:31.360 Right. 49 03:33.400 --> 03:34.630 So over here. 50 03:34.670 --> 03:35.590 Same thing. 51 03:35.630 --> 03:36.510 We have D1. 52 03:37.310 --> 03:43.790 So here we need to identify how many companies write duplicate job listings. 53 03:44.070 --> 03:49.750 Write a query to retrieve the count of companies that have posted duplicate job listings. 54 03:49.750 --> 03:55.430 So here we do not require the company ID, we require the count of the company ID, right? 55 03:56.870 --> 04:02.870 So simply we can do count of company ID as duplicate. 56 04:05.150 --> 04:08.470 Job listings companies. 57 04:09.030 --> 04:10.870 Now let me execute this one. 58 04:10.870 --> 04:14.470 It should give us three right. 59 04:16.710 --> 04:23.470 So this is how you can use the group by and having clause to identify the duplicate data in. 60 04:25.510 --> 04:30.390 Sometimes you may require to delete also the duplicate data. 61 04:30.390 --> 04:34.990 So in that case also group by naming can be useful. 62 04:37.150 --> 04:40.030 So okay thank you so much everyone. 63 04:40.030 --> 04:44.870 We will meet again with the next interview question as soon as possible. 64 04:44.870 --> 04:45.590 Thank you.