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: JOIN → GROUP BY → HAVINGCandidatesidexpinterview113101121102Roundsinterviewroundscore1011810129JOINAfter JOIN + WHEREcandidate_idscore118119GROUP BYAfter GROUP BYcandidate_idtotal_score1117HAVING > 15Final Resultcandidate_id11
Understanding the Visualization
1
Input Tables
Candidates and Rounds tables
2
JOIN + Filter
Combine tables and filter by experience
3
GROUP + HAVING
Sum scores and filter by total
4
Output
Qualified candidate IDs
Key Takeaway
🎯 Key Insight: Use JOIN to combine tables, WHERE to pre-filter, GROUP BY to aggregate, and HAVING to filter aggregated results

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.5K 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