Calculate the percentage of job listings that are duplicates.
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. |
| location | string | Job location. |
| salary | integer | Listed salary. |
| posted_date | date | Date the job was posted. |
Sample Data
| job_id | company_id | title | description | location | salary | posted_date |
|---|---|---|---|---|---|---|
| 1 | 1 | Analyst | Work on data | NY | 60000 | 2024-01-01 |
| 2 | 1 | Analyst | Work on data | NY | 62000 | 2024-01-02 |
| 3 | 2 | Engineer | Build systems | TX | 90000 | 2024-01-03 |
| 4 | 2 | Engineer | Build systems | TX | 92000 | 2024-01-04 |
| 5 | 3 | Manager | Lead team | CA | 100000 | 2024-01-05 |
| 6 | 4 | Analyst | Analyze reports | SF | 70000 | 2024-01-06 |
| duplicate_percentage |
|---|
| 33.33 |
SQL Editor
| duplicate_percentage |
|---|
| 33.33 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH duplicate_rows AS (
SELECT
COUNT(*) AS duplicate_count
FROM job_listings
GROUP BY company_id, title, description
HAVING COUNT(*) > 1
)
SELECT
ROUND(SUM(duplicate_count) * 100.0 / (SELECT COUNT(*) FROM job_listings), 2) AS duplicate_percentage
FROM duplicate_rows;
Explanation
Find duplicate groups using company_id, title, and description.
Sum the rows inside those duplicate groups.
Divide by the total number of listings and round to two decimals.
Related Questions