LinkedIn: Applicants Holding Every Required Skill
Recruiters can filter applicants to the ones who already list every skill the job asks for. Rebuild that filter.
- Difficulty: Medium
- Topics: Subqueries, Joins
- Dialect: MySQL
- Problem: #737
Problem statement
Recruiters can filter applicants to the ones who already list every skill the job asks for. Rebuild that filter.
Return application_id, job_id and member_id for every application whose member lists all of the job's required skills on their profile. A job with no required skills is satisfied by every applicant. Extra skills on the profile are fine. Order the rows by application_id.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: JobSkill
| Column | Type |
|---|---|
| job_id | int |
| skill | varchar |
Primary key: job_id, skill.
A skill the job posting requires.
Table: MemberSkill
| Column | Type |
|---|---|
| member_id | int |
| skill | varchar |
Primary key: member_id, skill.
A skill listed on the member's profile. Skill names are spelt the same way in both tables.
Table: JobApplication
| Column | Type |
|---|---|
| application_id | int |
| job_id | int |
| member_id | int |
Primary key: application_id.
One row per application; a member applies to a job at most once.
Examples
Example 1
JobSkill
| job_id | skill |
|---|---|
| 1 | SQL |
| 1 | Python |
| 2 | Figma |
MemberSkill
| member_id | skill |
|---|---|
| 10 | SQL |
| 10 | Python |
| 10 | Excel |
| 11 | SQL |
| 12 | Figma |
JobApplication
| application_id | job_id | member_id |
|---|---|---|
| 100 | 1 | 10 |
| 101 | 1 | 11 |
| 102 | 2 | 12 |
| 103 | 2 | 10 |
| 104 | 3 | 11 |
| 105 | 1 | 13 |
Output
| application_id | job_id | member_id |
|---|---|---|
| 100 | 1 | 10 |
| 102 | 2 | 12 |
| 104 | 3 | 11 |
How to solve LinkedIn: Applicants Holding Every Required Skill
This is relational division: keep the applications whose member's skill set contains the job's whole skill set. SQL has no "for all", so the solution uses the double negative — an application qualifies when there is no required skill of the job for which there is no matching skill on the member's profile. The inner NOT EXISTS finds a missing skill; the outer one keeps the application only if no such skill exists.
The empty case falls out correctly: a job with no rows in JobSkill has no required skill that could be missing, so every applicant passes — "all of nothing" is true. A member with no skills at all fails any job that requires something.
The alternative counts instead. LEFT JOIN each application to its job's required skills, then LEFT JOIN each of those to the member's matching skill; COUNT(js.skill) is the number required and COUNT(ms.skill) the number matched, and they are equal exactly when nothing is missing (both 0 for a job without requirements). The counting version relies on the primary keys preventing duplicate skills; the NOT EXISTS version does not.
Reference solution (MySQL)
SELECT a.application_id, a.job_id, a.member_id
FROM JobApplication a
WHERE NOT EXISTS (
SELECT 1 FROM JobSkill js
WHERE js.job_id = a.job_id
AND NOT EXISTS (
SELECT 1 FROM MemberSkill ms
WHERE ms.member_id = a.member_id AND ms.skill = js.skill
)
)
ORDER BY a.application_idAnother way
SELECT a.application_id, a.job_id, a.member_id
FROM JobApplication a
LEFT JOIN JobSkill js ON js.job_id = a.job_id
LEFT JOIN MemberSkill ms ON ms.member_id = a.member_id AND ms.skill = js.skill
GROUP BY a.application_id, a.job_id, a.member_id
HAVING COUNT(js.skill) = COUNT(ms.skill)
ORDER BY a.application_id← LinkedIn: Jobs Reposted Within 30 Days · LinkedIn: Premium Free-Trial Conversion by Trial Month →