0/304

00:00:00

Description

Editorial

Project Candidate Skill Fit Ranking

HARD30 pts

A talent intelligence platform maintains candidate skill profiles and project skill requirements. You need to recommend one best candidate for each project using strict qualification and deterministic ranking rules.

Table: Candidates

candidate_id: candidate identifier
skill: skill name
proficiency: candidate's skill level from 1 to 5
(candidate_id, skill) is unique

Table: Projects

project_id: project identifier
skill: required skill name
importance: required level from 1 to 5
(project_id, skill) is unique

Qualification rule:

A candidate is eligible for a project only if the candidate has every required skill listed for that project.

Scoring rule for each eligible (project_id, candidate_id) pair:

Start score at 100
For each required skill:
- add 10 when proficiency > importance
- subtract 5 when proficiency < importance
- add 0 when proficiency = importance

Selection rule:

For each project, return only one winner:
- highest score wins
- if scores tie, choose smaller candidate_id
If a project has no eligible candidate, exclude it from output.

Output requirements:

Return columns in this exact order: project_id, candidate_id, score
Sort final output by project_id ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Candidates:
| candidate_id | skill      | proficiency |
|--------------|------------|-------------|
| 101          | Python     | 5           |
| 101          | Tableau    | 3           |
| 101          | PostgreSQL | 4           |
| 102          | Python     | 4           |
| 102          | Tableau    | 5           |
| 102          | PostgreSQL | 4           |
| 102          | R          | 4           |
| 103          | Python     | 3           |
| 103          | Tableau    | 5           |
| 103          | PostgreSQL | 5           |

Projects:
| project_id | skill      | importance |
|------------|------------|------------|
| 501        | Python     | 4          |
| 501        | Tableau    | 3          |
| 501        | PostgreSQL | 5          |
| 502        | Python     | 3          |
| 502        | Tableau    | 4          |
| 502        | R          | 2          |

Output

[
  {"project_id":501,"candidate_id":101,"score":105},
  {"project_id":502,"candidate_id":102,"score":130}
]

Explanation

Candidate 101 wins project 501 by score. Candidate 102 is the only eligible winner for project 502.

Example

Input

Candidates:
| candidate_id | skill      | proficiency |
|--------------|------------|-------------|
| 201          | SQL        | 4           |
| 201          | Statistics | 4           |
| 205          | SQL        | 4           |
| 205          | Statistics | 4           |

Projects:
| project_id | skill      | importance |
|------------|------------|------------|
| 610        | SQL        | 4          |
| 610        | Statistics | 4          |

Output

[
  {"project_id":610,"candidate_id":201,"score":100}
]

Explanation

Both candidates tie at score 100, so lower candidate_id (201) is selected.

Example

Input

Candidates:
| candidate_id | skill  | proficiency |
|--------------|--------|-------------|
| 301          | Python | 3           |
| 301          | SQL    | 4           |
| 302          | Python | 5           |

Projects:
| project_id | skill      | importance |
|------------|------------|------------|
| 701        | Python     | 3          |
| 701        | SQL        | 5          |
| 702        | TensorFlow | 4          |

Output

[
  {"project_id":701,"candidate_id":301,"score":95}
]

Explanation

Project 702 is excluded because no candidate has TensorFlow. For project 701, candidate 301 is fully qualified and wins.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Candidates <= 400000
1 <= number of rows in Projects <= 300000
1 <= candidate_id <= 1000000000
1 <= project_id <= 1000000000
1 <= proficiency <= 5
1 <= importance <= 5
(candidate_id, skill) is unique in Candidates
(project_id, skill) is unique in Projects
Candidates can have extra skills that should not affect scoring for unrelated projects
Only fully-qualified candidates (all required project skills present) are eligible
Winner per project: highest score, tie broken by lower candidate_id
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [project_id, candidate_id, score]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Projects =

[{"project_id":502,"skill":"R","importance":2},{"project_id":501,"skill":"Tableau","importance":3},{"project_id":501,"skill":"Python","importance":4},{"project_id":501,"skill":"PostgreSQL","importance":5},{"project_id":502,"skill":"Tableau","importance":4},{"project_id":502,"skill":"Python","importance":3}]

Candidates =

[{"candidate_id":102,"skill":"PostgreSQL","proficiency":4},{"candidate_id":103,"skill":"PostgreSQL","proficiency":5},{"candidate_id":102,"skill":"Tableau","proficiency":5},{"candidate_id":101,"skill":"Python","proficiency":5},{"candidate_id":103,"skill":"Python","proficiency":3},{"candidate_id":102,"skill":"R","proficiency":4},{"candidate_id":103,"skill":"Tableau","proficiency":5},{"candidate_id":102,"skill":"Python","proficiency":4},{"candidate_id":101,"skill":"PostgreSQL","proficiency":4},{"candidate_id":103,"skill":"Spark","proficiency":4},{"candidate_id":101,"skill":"Tableau","proficiency":3},{"candidate_id":101,"skill":"TensorFlow","proficiency":2}]

Project Candidate Skill Fit Ranking

Hints

Project Candidate Skill Fit Ranking

Hints