Skills Match

ASKED IN INTERVIEW

4pts

Linked In

Problem Statement

LinkedIn wants to improve its job recommendation engine. A candidate is considered a strong match for a job if they possess at least 80% of the skills listed in the job requirements.

Write an SQL query to find all pairs of candidates and jobs where the candidate meets this 80% threshold.

Rules:

A candidate must possess at least 80% of the specific skills required for a job.
Only include jobs that have at least one skill requirement.
Output columns: candidate_id, job_id.
Order the result by candidate_id in ascending order, then by job_id in ascending order.

Table Schema:

Candidates: Table containing unique candidate metadata.
Candidate_Skills: Table listing skills possessed by each candidate.
Job_Requirements: Table listing skills required for each job posting.

Tests your understanding of

Joins, Aggregation, Filtering and Division

Input Tables

Candidates

candidate_id(INTEGER)	name(VARCHAR)
1	Alice
2	Bob
3	Charlie
4	David
5	Eve
6	Frank

Candidate_Skills

candidate_id(INTEGER)	skill_name(VARCHAR)
1	SQL
1	Python
1	Tableau
1	Java
2	SQL
2	Python
3	SQL
4	Java
4	Python

Job_Requirements

job_id(INTEGER)	skill_name(VARCHAR)
101	SQL
101	Python
101	Java
101	Tableau
101	Excel
102	SQL
102	Python

Expected Output

candidate_id(INTEGER)	job_id(INTEGER)
1	101
1	102
2	102