logo

Back to questions

Duplicate Job Listings

Easy

This is the same question as problem #8 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table below that shows job postings for all companies on the LinkedIn platform. Write a query to get the number of companies that have posted duplicate job listings (two jobs at the same company with the same title and description).

Table:

Column NameType
job_idinteger
company_idinteger
titlestring
descriptionstring

Example Input:

job_idcompany_idtitledescription
248827Business AnalystBusiness analyst evaluates past and current business data with the primary goal of improving decision-making processes within organizations.
149845Business AnalystBusiness analyst evaluates past and current business data with the primary goal of improving decision-making processes within organizations.
945345Data AnalystData analyst reviews data to identify key insights into a business's customers and ways the data can be used to solve problems.
164345Data AnalystData analyst reviews data to identify key insights into a business's customers and ways the data can be used to solve problems.
172244Data EngineerData engineer works in a variety of settings to build systems that collect, manage, and convert raw data into usable information for data scientists and business analysts to interpret.

Example Output:

duplicate_companies
1

Explanation

Because s 945 and 164 are at the same company (345), and the jobs have the same title and description, there is exactly one company with a duplicate job.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.