Count how many companies have posted duplicate job listings, where duplicate listings share the same company, title, and description.
Table Schema
| 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. |
| 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
| job_id | company_id | title | description |
|---|---|---|---|
| 248 | 827 | Business Analyst | Business analyst evaluates past and current business data with the primary goal of improving decision-making processes within organizations. |
| 149 | 845 | Business Analyst | Business analyst evaluates past and current business data with the primary goal of improving decision-making processes within organizations. |
| 945 | 345 | Data Analyst | Data analyst reviews data to identify key insights into a business's customers and ways the data can be used to solve problems. |
| 164 | 345 | Data Analyst | Data analyst reviews data to identify key insights into a business's customers and ways the data can be used to solve problems. |
| 172 | 244 | Data Engineer | Data engineer works in a variety of settings to build systems that collect, manage, and convert raw data into usable information. |
| duplicate_companies |
|---|
| 1 |
SQL Editor
| duplicate_companies |
|---|
| 1 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT
COUNT(DISTINCT company_id) AS duplicate_companies
FROM (
SELECT
company_id,
title,
description
FROM job_listings
GROUP BY company_id, title, description
HAVING COUNT(*) > 1
) AS duplicate_jobs;
Explanation
Group rows by company_id, title, and description because all three fields define a duplicate job listing group.
Use HAVING COUNT(*) > 1 to keep only groups where the same company posted the same title and description multiple times.
Count distinct company_id values from those duplicate groups and alias the result as duplicate_companies.
Related Questions