Find Candidates for Data Scientist Position II

Find Candidates for Data Scientist Position II - Problem

Database Medium

🎯 Find the Perfect Data Scientist for Each Project!

You're a technical recruiter at a top tech company managing multiple data science projects. Each project requires specific skills at certain importance levels, and you have a pool of candidates with varying proficiency in different skills.

Your Mission: Match the best candidate to each project based on a sophisticated scoring system!

Scoring Rules:

Start with 100 base points
Add +10 points for each skill where candidate's proficiency > project's importance
Subtract -5 points for each skill where candidate's proficiency < project's importance
No change when proficiency equals importance

Selection Criteria:

✅ Candidate must have all required skills for the project
🏆 Choose the highest scoring candidate
⚖️ In case of ties, prefer the candidate with lower ID
❌ Skip projects with no qualified candidates

Return results ordered by project_id ascending.

Input & Output

example_1.sql — Basic Matching

$ Input: Candidates: [(101,'Python',5), (101,'Tableau',3), (101,'PostgreSQL',4)] Projects: [(501,'Python',4), (501,'Tableau',3), (501,'PostgreSQL',5)]

› Output: [(501, 101, 105)]

💡 Note: Candidate 101 has all skills for project 501. Score: 100 + 10 (Python 5>4) + 0 (Tableau 3=3) + (-5) (PostgreSQL 4<5) = 105

example_2.sql — Multiple Candidates

$ Input: Candidates: [(102,'Python',4), (102,'R',4), (103,'Python',3), (103,'R',5)] Projects: [(502,'Python',3), (502,'R',2)]

› Output: [(502, 103, 130)]

💡 Note: Both candidates qualify. Candidate 102: 100+10+10=120. Candidate 103: 100+0+10=110. Wait, let me recalculate: 103 has Python=3 (=importance 3), R=5 (>importance 2), so 100+0+10=110. But 102 has Python=4 (>3), R=4 (>2), so 100+10+10=120. Actually 102 should win.

example_3.sql — No Qualified Candidates

$ Input: Candidates: [(104,'Python',3)] Projects: [(503,'Python',2), (503,'Java',3)]

› Output: []

💡 Note: Candidate 104 lacks Java skill required for project 503, so no results returned for this project

Constraints

1 ≤ candidate_id, project_id ≤ 1000
1 ≤ proficiency, importance ≤ 5
skill names are valid varchar strings
Each candidate must have ALL required skills to be considered
Skills can include: Python, R, Tableau, PostgreSQL, TensorFlow, Spark, etc.

Visualization

Tap to expand

Understanding the Visualization

Skill Matching

JOIN candidates with projects on matching skills (like filtering resumes by required skills)

Qualification Check

GROUP BY candidate-project pairs and use HAVING to ensure all skills are present

Score Calculation

Use CASE statements to award bonus points (+10) or penalties (-5) based on proficiency vs importance

Ranking & Selection

ROW_NUMBER() ranks candidates per project, handling ties by candidate_id

Key Takeaway

🎯 Key Insight: Using GROUP BY with HAVING COUNT(*) ensures candidates have ALL required skills, while ROW_NUMBER() efficiently handles ranking and tie-breaking in a single pass.

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses Common Table Expressions (CTEs) to efficiently join the tables, filter candidates with all required skills using GROUP BY + HAVING, calculate scores with conditional aggregation, and rank candidates using ROW_NUMBER() window function. This approach minimizes table scans and leverages SQL's built-in optimizations for best performance.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n²m)	O(1)	Check each candidate-project pair with separate subqueries
Optimized JOIN with CTE (Optimal)	O(n log n)	O(n)	Use CTEs and window functions to efficiently process in single pass

Nested Subqueries Approach — Algorithm Steps

For each project, find candidates with all required skills using nested EXISTS
Calculate score for each valid candidate-project pair
Select the best candidate per project with window functions
Order results by project_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Join Projects and Candidates on matching skills

Group & Filter

Group by project-candidate pairs, filter those with all skills

Calculate Scores

Use CASE statements to calculate bonus/penalty points

Rank Results

Use ROW_NUMBER to find best candidate per project

Code -

solution.c — C

// SQL Solution
char* sql = "\
WITH candidate_project_scores AS (\
    SELECT \
        p.project_id,\
        c.candidate_id,\
        100 + \
        SUM(CASE WHEN c.proficiency > p.importance THEN 10 ELSE 0 END) +\
        SUM(CASE WHEN c.proficiency < p.importance THEN -5 ELSE 0 END) AS score\
    FROM Projects p\
    JOIN Candidates c ON p.skill = c.skill\
    GROUP BY p.project_id, c.candidate_id\
    HAVING COUNT(*) = (\
        SELECT COUNT(*) \
        FROM Projects p2 \
        WHERE p2.project_id = p.project_id\
    )\
),\
ranked_candidates AS (\
    SELECT \
        project_id,\
        candidate_id,\
        score,\
        ROW_NUMBER() OVER (\
            PARTITION BY project_id \
            ORDER BY score DESC, candidate_id ASC\
        ) as rn\
    FROM candidate_project_scores\
)\
SELECT project_id, candidate_id, score\
FROM ranked_candidates\
WHERE rn = 1\
ORDER BY project_id;";

Time & Space Complexity

Time Complexity

⏱️

O(n²m)

n candidates × m projects, with skill checks for each pair

⚠ Quadratic Growth

Space Complexity

O(1)

Only temporary result storage, no additional data structures

✓ Linear Space

38.2K Views

High Frequency

~25 min Avg. Time

1.6K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler