LinkedIn Easy Anti Join Duplicates

No Duplicate
Job
Listings

Count companies with no duplicate job listings, where duplicates share the same company, title, and description.

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.
department	string	Department for the role.
posted_date	date	Date the job was posted.
employment_type	string	Type of employment.

Duplicate Check	Reason
company_id	Duplicates must be within the same company.
title	The title must match exactly.
description	The description must match exactly.

Sample Data

Input
Output

Sample Input: job_listings

job_id	company_id	title	description	department	posted_date	employment_type
1	1	Analyst	Work on data	Data	2024-01-01	Full-time
2	1	Analyst	Work on data	Data	2024-01-02	Full-time
3	2	Engineer	Build systems	Eng	2024-01-03	Full-time
4	2	Analyst	Analyze	Data	2024-01-04	Contract
5	3	Manager	Lead team	HR	2024-01-05	Full-time
6	3	Manager	Lead team	HR	2024-01-06	Full-time

Expected Output

unique_companies
1

SQL Editor

Run
Query

Waiting for query

unique_companies
1

Hints

Unlock
Clues

Hint 01: First find companies that have duplicate listing groups.

Hint 02: A duplicate group uses GROUP BY company_id, title, description and HAVING COUNT(*) > 1.

Hint 03: Count companies from job_listings whose company_id is not in the duplicate-company list.

Solution

Locked
Answer

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

WITH duplicate_companies AS (
  SELECT
    company_id
  FROM job_listings
  GROUP BY company_id, title, description
  HAVING COUNT(*) > 1
)
SELECT
  COUNT(DISTINCT company_id) AS unique_companies
FROM job_listings
WHERE company_id NOT IN (
  SELECT company_id
  FROM duplicate_companies
);

Explanation

Step By
Step

Group rows by company_id, title, and description to identify duplicate listing groups.

Use HAVING COUNT(*) > 1 to find companies that should be excluded.

Count distinct company_id values from job_listings after removing companies found in the duplicate list.

No DuplicateJobListings

InspectTable

InputOutput

RunQuery

UnlockClues

LockedAnswer

Step ByStep

KeepSolving