Project Candidate Skill Fit Ranking

Hard

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 1

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 2

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 3

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.

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]

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 |

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 |

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 |