Objective

Write a SQL query to count how many companies posted duplicate job listings with the same title and description.

Table Schema:

• job_listings

Task Requirements:

• Identify companies that posted duplicate job listings (same title and description)
• Count how many companies have posted such duplicates
• Return only the count of companies with duplicates

Output columns: duplicate_companies

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(job_id) > 1
) AS duplicates;

Explanation

Step 1: Identify Duplicate Listings (Inner Query)

Group job listings by company_id, title, and description. The HAVING COUNT(job_id) > 1 clause keeps only groups where a company posted the same job more than once.

GROUP BY company_id, title, description
HAVING COUNT(job_id) > 1

Step 2: Count Distinct Companies with Duplicates

The outer query counts distinct company_id values from the subquery. Using DISTINCT ensures each company is counted once even if it has multiple duplicate listings.

SELECT COUNT(DISTINCT company_id) AS duplicate_companies