Accepted Candidates From the Interviews - Problem

You are given two tables: Candidates and Rounds.

The Candidates table contains information about job candidates including their ID, name, years of experience, and interview ID.

The Rounds table contains the scores for each round of interviews, with each row representing one interview round score.

Write a SQL query to find the IDs of candidates who meet both criteria:

  • Have at least 2 years of experience
  • Their total interview score is strictly greater than 15

Return the result in any order.

Table Schema

Candidates
Column Name Type Description
candidate_id PK int Primary key, unique candidate identifier
name varchar Candidate's name
years_of_exp int Years of work experience
interview_id int Interview identifier
Primary Key: candidate_id
Rounds
Column Name Type Description
interview_id PK int Interview identifier, foreign key to Candidates
round_id PK int Round number within interview
score int Score achieved in this round
Primary Key: (interview_id, round_id)

Input & Output

Example 1 — Basic Case
Input Tables:
Candidates
candidate_id name years_of_exp interview_id
11 Alyce 3 101
12 Bob 1 102
13 Charlie 2 103
Rounds
interview_id round_id score
101 1 8
101 2 9
102 1 12
103 1 10
103 2 6
Output:
candidate_id
11
13
💡 Note:

Alyce (ID 11): 3 years experience ≥ 2 ✓, total score = 8 + 9 = 17 > 15 ✓

Bob (ID 12): 1 year experience < 2 ✗ (filtered out by WHERE clause)

Charlie (ID 13): 2 years experience ≥ 2 ✓, total score = 10 + 6 = 16 > 15 ✓
Example 2 — Edge Case with Low Scores
Input Tables:
Candidates
candidate_id name years_of_exp interview_id
21 David 4 201
22 Eve 2 202
Rounds
interview_id round_id score
201 1 7
201 2 8
202 1 20
Output:
candidate_id
22
💡 Note:

David (ID 21): 4 years experience ≥ 2 ✓, but total score = 7 + 8 = 15, which is NOT > 15 ✗

Eve (ID 22): 2 years experience ≥ 2 ✓, total score = 20 > 15 ✓
Example 3 — No Qualified Candidates
Input Tables:
Candidates
candidate_id name years_of_exp interview_id
31 Frank 1 301
Rounds
interview_id round_id score
301 1 20
Output:
candidate_id
💡 Note:

Frank (ID 31): Only 1 year experience < 2 ✗ (filtered out despite high score of 20)

Constraints

  • 1 ≤ candidate_id ≤ 1000
  • 1 ≤ years_of_exp ≤ 10
  • 1 ≤ interview_id ≤ 500
  • 1 ≤ round_id ≤ 10
  • 0 ≤ score ≤ 20

Visualization

Tap to expand
Accepted Candidates From Interviews INPUT TABLES Candidates Table id name exp_yrs 1 Alice 3 2 Bob 1 3 Carol 2 Rounds Table cand_id round score 1 1 8 1 2 10 2 1 12 3 1 9 3 2 8 ALGORITHM STEPS 1 JOIN Tables Candidates JOIN Rounds ON id = candidate_id 2 WHERE Filter experience_years >= 2 (Bob excluded: exp=1) 3 GROUP BY candidate SUM(score) per candidate Alice (id=1): 8+10 = 18 Carol (id=3): 9+8 = 17 Both have exp >= 2 4 HAVING Filter SUM(score) > 15 18 > 15 OK, 17 > 15 OK FINAL RESULT Candidates who meet BOTH: - Experience >= 2 years - Total Score > 15 candidate_id 1 3 Accepted Candidates: ID 1 (Alice): exp=3, score=18 -- OK ID 3 (Carol): exp=2, score=17 -- OK ID 2 excluded (exp=1) Key Insight: Use JOIN to combine Candidates and Rounds tables, then apply WHERE for experience filter, GROUP BY to aggregate scores per candidate, and HAVING to filter by total score threshold. The HAVING clause filters AFTER aggregation (SUM), while WHERE filters BEFORE. TutorialsPoint - Accepted Candidates From the Interviews | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.7K Views
Medium Frequency
~12 min Avg. Time
892 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