Find Candidates for Data Scientist Position II - Problem

You are given two tables: Candidates and Projects. Write a SQL query to find the best candidate for each project based on specific scoring criteria.

Requirements:

  • Candidates must have all required skills for a project
  • Calculate a score starting with 100 points
  • Add 10 points for each skill where proficiency > importance
  • Subtract 5 points for each skill where proficiency < importance
  • No change when proficiency equals importance
  • Return only the highest scoring candidate per project
  • Break ties by choosing the candidate with the lower candidate_id
  • Exclude projects with no suitable candidates

Table Schema

Candidates
Column Name Type Description
candidate_id PK int Unique identifier for candidate
skill PK varchar Skill name (e.g., Python, SQL)
proficiency int Skill proficiency level (1-5)
Primary Key: (candidate_id, skill)
Projects
Column Name Type Description
project_id PK int Unique identifier for project
skill PK varchar Required skill name
importance int Skill importance level (1-5)
Primary Key: (project_id, skill)

Input & Output

Example 1 — Multiple Projects and Candidates
Input Tables:
Candidates
candidate_id skill proficiency
101 Python 5
101 Tableau 3
101 PostgreSQL 4
101 TensorFlow 2
102 Python 4
102 Tableau 5
102 PostgreSQL 4
102 R 4
103 Python 3
103 Tableau 5
103 PostgreSQL 5
103 Spark 4
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 candidate_id score
501 101 105
502 102 130
💡 Note:

For Project 501: Candidate 101 scores 100 + 10 (Python 5>4) + 0 (Tableau 3=3) + (-5) (PostgreSQL 4<5) = 105. For Project 502: Candidate 102 scores 100 + 10 (Python 4>3) + 10 (Tableau 5>4) + 10 (R 4>2) = 130. Candidate 103 doesn't have R skill, so is disqualified from Project 502.
Example 2 — No Qualified Candidates
Input Tables:
Candidates
candidate_id skill proficiency
201 Python 3
201 SQL 4
Projects
project_id skill importance
601 Python 4
601 R 3
601 Machine Learning 5
Output:
project_id candidate_id score
💡 Note:

Candidate 201 only has Python and SQL skills, but Project 601 requires Python, R, and Machine Learning. Since the candidate doesn't have all required skills, they are disqualified and no result is returned for this project.

Constraints

  • 1 ≤ candidate_id ≤ 1000
  • 1 ≤ project_id ≤ 1000
  • 1 ≤ proficiency ≤ 5
  • 1 ≤ importance ≤ 5
  • skill names are case-sensitive varchar strings

Visualization

Tap to expand
Find Candidates for Data Scientist Position II INPUT Projects Table id req_skills level 1 Python 3 1 SQL 2 Candidates Table id skill proficiency 101 Python 4 101 SQL 2 102 Python 3 102 SQL 3 Scoring Rules: Base: 100 points Bonus: +10 per level above Penalty: -5 per level below Must have ALL required skills ALGORITHM STEPS 1 Filter Candidates Keep only those with ALL required project skills 2 Calculate Scores Compare proficiency vs required level per skill 3 Apply Bonuses/Penalties diff = proficiency - required score += diff*10 or diff*5 4 Select Best Candidate Highest score wins Tie: lower candidate_id Score Example (Candidate 101) Python: 4-3 = +1 --> +10 SQL: 2-2 = 0 --> 0 Total: 100 + 10 = 110 Cand 102: 100+0+5 = 105 FINAL RESULT Best Candidates Output project_id candidate_id 1 101 WINNER Score: 110 Score Comparison Cand 101: 110 pts Cand 102: 105 pts Candidate 101 selected Highest score for Project 1 Key Insight: The optimal solution uses JOIN operations to match candidates with project requirements, then filters candidates who possess ALL required skills using GROUP BY and HAVING COUNT matching. Scoring is calculated with CASE statements: +10 bonus per level above, -5 penalty per level below requirement. TutorialsPoint - Find Candidates for Data Scientist Position II | Optimal Solution

Related Problems

Asked in
Google 28 Meta 22 Amazon 19
28.0K Views
Medium Frequency
~25 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen