Assume you're given a table containing job postings from various companies. Write a query to retrieve the count of companies that have posted duplicate job listings (same title and description).
This data quality problem is typical of LinkedIn's focus on maintaining a clean, trustworthy platform. Duplicate detection is crucial for user experience—job seekers shouldn't see the same position multiple times. This question tests your ability to use GROUP BY with multiple columns and HAVING clauses to identify duplicates, then count distinct entities—a pattern used extensively in data cleaning and quality assurance workflows.
Concepts tested: GROUP BY with multiple columns (company_id, title, description), HAVING clause for post-aggregation filtering, COUNT() with conditions, nested queries (subquery in FROM clause), and COUNT(DISTINCT) for final aggregation. Understanding the difference between counting rows vs counting distinct values is critical for accurate duplicate detection.
LinkedIn's data quality teams use similar queries to: detect and merge duplicate job postings to improve search results, identify companies that may be spamming the platform, generate data quality reports for internal monitoring, flag potential policy violations for review, clean up legacy data during migrations, and maintain the integrity of their professional network graph.
When tackling this LinkedIn problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT COUNT(DISTINCT company_id) AS duplicate_company_count
FROM (
SELECT company_id
FROM job_listings
GROUP BY company_id, title, description
HAVING COUNT(job_id) > 1
) AS duplicatesThis approach groups job listings by company_id, title, and description to identify exact duplicate combinations. HAVING COUNT(job_id) > 1 filters to groups with more than one posting. The outer query counts distinct company_ids that appear in at least one duplicate group. DISTINCT is important because a single company could have multiple different duplicate pairs (e.g., duplicate 'Engineer' AND duplicate 'Analyst' roles), and we want to count it only once. Clean, readable, and performant with a composite index on (company_id, title, description).
WITH duplicate_groups AS (
SELECT company_id
FROM job_listings
GROUP BY company_id, title, description
HAVING COUNT(job_id) > 1
)
SELECT COUNT(DISTINCT company_id) AS duplicate_company_count
FROM duplicate_groupsThe CTE version separates duplicate detection from the final count, making each step independently readable and debuggable. The named CTE duplicate_groups explicitly communicates that it identifies company-title-description groups with more than one job posting, and the outer SELECT counts distinct companies. Functionally equivalent to the subquery approach — modern optimizers treat them identically by inlining the CTE. Preferred in team environments for clarity, and the CTE can be reused if you also need to inspect the raw duplicate rows alongside the count.
The key performance driver is the GROUP BY on (company_id, title, description). A composite index on these three columns allows the database to group without a full sort, using an index scan instead of a hash aggregate over the full table. However, description is often a long-text column, making index storage expensive and comparisons slower — hash aggregation over long strings consumes more memory than over integers. For very large job_listings tables, consider whether description needs to be in the GROUP BY at all if a shorter unique identifier is available. The outer COUNT(DISTINCT company_id) adds minimal overhead since the inner result after HAVING is typically a small set. Both subquery and CTE versions produce identical query plans in most modern databases — the optimizer inlines the CTE. If you need the actual duplicate listings rather than just the count, joining back to job_listings is O(n) but necessary; a covering index helps avoid heap lookups.
The most common mistake is grouping only by company_id and title, omitting description — this falsely identifies listings as duplicates when they share a title but have different descriptions (e.g., two 'Software Engineer' roles with distinct responsibilities). Always group by all fields that define uniqueness per the problem statement. Forgetting COUNT(DISTINCT company_id) in the outer query is another frequent error — using COUNT(*) counts the number of duplicate pairs, not the number of companies, inflating the answer. Candidates sometimes attempt a self-join instead of GROUP BY/HAVING, which is valid but significantly more complex and prone to double-counting. Remember: a company with three identical listings still counts as one company, not three.
A single company could have multiple distinct duplicate pairs — for example, duplicate 'Software Engineer' postings AND duplicate 'Data Analyst' postings. Without DISTINCT, each duplicate group for that company is counted separately. COUNT(DISTINCT company_id) ensures each company is counted exactly once regardless of how many different duplicate pairs it has, matching the problem's intent of counting affected companies rather than affected job combinations.
Join the original table back to the duplicate groups: SELECT jl.* FROM job_listings jl JOIN (SELECT company_id, title, description FROM job_listings GROUP BY company_id, title, description HAVING COUNT(job_id) > 1) dups ON jl.company_id = dups.company_id AND jl.title = dups.title AND jl.description = dups.description ORDER BY jl.company_id, jl.title. This returns all individual rows that participate in a duplicate group for auditing or cleanup.
Yes. ROW_NUMBER() OVER (PARTITION BY company_id, title, description ORDER BY job_id) assigns sequential numbers within each duplicate group. Rows with row_number > 1 are the duplicate copies. This is most useful when you need to identify which specific rows to delete while retaining one canonical copy per group. For simply counting companies with duplicates, GROUP BY/HAVING is simpler. Use ROW_NUMBER for deduplication or audit workflows.