LinkedIn Easy Group By Duplicates

Cross Company
Duplicate
Listings

Find job listings with identical title and description across different companies.

Table Schema

Inspect
Table

interactive

Column	Type	Description
job_id	integer	Unique job posting identifier.
company_id	integer	Identifier for the company posting the job.
title	string	Job listing title.
description	string	Job listing description.
location	string	Job location.
salary	integer	Listed salary.
posted_date	date	Date the job was posted.

Sample Data

Sample Input: job_listings

job_id	company_id	title	description	location	salary	posted_date
1	1	Analyst	Work on data	NY	60000	2024-01-01
2	2	Analyst	Work on data	SF	70000	2024-01-02
3	3	Engineer	Build systems	TX	90000	2024-01-03
4	4	Engineer	Build systems	CA	95000	2024-01-04
5	5	Manager	Lead team	NY	100000	2024-01-05
6	6	Analyst	Analyze reports	TX	65000	2024-01-06

Expected Output

title	description
Analyst	Work on data
Engineer	Build systems

SQL Editor

Waiting for query

title	description
Analyst	Work on data
Engineer	Build systems

Hints

Hint 01: The duplicate definition ignores company_id, but companies must be different.

Hint 02: Group by title and description.

Hint 03: Use HAVING COUNT(DISTINCT company_id) > 1.

Solution

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT
  title,
  description
FROM job_listings
GROUP BY title, description
HAVING COUNT(DISTINCT company_id) > 1;

Explanation

Group rows by the listing fields that must match: title and description.

Count distinct company IDs inside each group.

Keep groups that appear under more than one company.