LinkedIn: Applicants Holding Every Required Skill

Medium
737.

LinkedIn: Applicants Holding Every Required Skill

MediumSubqueriesJoins

MySQL

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

ColumnType
job_idint
skillvarchar

Primary key (together): job_id, skill.

A skill the job posting requires.

Table: MemberSkill

ColumnType
member_idint
skillvarchar

Primary key (together): member_id, skill.

A skill listed on the member's profile. Skill names are spelt the same way in both tables.

Table: JobApplication

ColumnType
application_idint
job_idint
member_idint

Primary key: application_id.

One row per application; a member applies to a job at most once.

Example

The rows must come back in the order shown.

JobSkill

job_idskill
1SQL
1Python
2Figma

MemberSkill

member_idskill
10SQL
10Python
10Excel
11SQL
12Figma

JobApplication

application_idjob_idmember_id
100110
101111
102212
103210
104311
105113

Output

application_idjob_idmember_id
100110
102212
104311
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
job_idint
skillvarchar

Primary key: job_id, skill.

A skill the job posting requires.

Table: MemberSkill

ColumnType
member_idint
skillvarchar

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

ColumnType
application_idint
job_idint
member_idint

Primary key: application_id.

One row per application; a member applies to a job at most once.

Examples

Example 1

JobSkill

job_idskill
1SQL
1Python
2Figma

MemberSkill

member_idskill
10SQL
10Python
10Excel
11SQL
12Figma

JobApplication

application_idjob_idmember_id
100110
101111
102212
103210
104311
105113

Output

application_idjob_idmember_id
100110
102212
104311

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_id

Another 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 →