logo

Back to questions

Duplicate Job Listings [Linkedin SQL Interview Question]

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.

Clarification:

  • Duplicate job listings refer to 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:

co_w_duplicate_jobs
1

Explanation

Because job IDs 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.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14