Qualified Interview Candidates by Experience and Round Score

Medium

A recruiting analytics platform stores one interview packet per candidate and records multiple scored rounds for each interview. Table: Candidates candidate_id: unique ID for a candidate name: candidate display name years_of_exp: full years of prior experience interview_id: identifier for the candidate's interview packet Table: Rounds interview_id: interview packet ID round_id: unique round number inside an interview packet score: score assigned in that round Acceptance policy: Candidate must have at least 2 years of experience. The total score across all rounds in their interview packet must be strictly greater than 15. Task: Return candidate_id values for all candidates who satisfy both acceptance rules. Output requirements: Return exactly one column: candidate_id Return each qualifying candidate only once. Output order can be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Candidates:
| candidate_id | name    | years_of_exp | interview_id |
|--------------|---------|--------------|--------------|
| 11           | Atticus | 1            | 101          |
| 9            | Ruben   | 6            | 104          |
| 6            | Aliza   | 10           | 109          |
| 8            | Alfredo | 0            | 107          |

Rounds:
| interview_id | round_id | score |
|--------------|----------|-------|
| 109          | 3        | 4     |
| 101          | 2        | 8     |
| 109          | 4        | 1     |
| 107          | 1        | 3     |
| 104          | 3        | 6     |
| 109          | 1        | 4     |
| 104          | 4        | 7     |
| 104          | 1        | 2     |
| 109          | 2        | 1     |
| 104          | 2        | 7     |
| 107          | 2        | 3     |
| 101          | 1        | 8     |

Output

[
  {"candidate_id":9}
]

Explanation

Candidate 9 has 6 years of experience and interview score total 22, so they pass. Candidate 11 has enough score but not enough experience, candidate 6 has enough experience but score total 10, and candidate 8 fails both criteria.

Example 2

Input

Candidates:
| candidate_id | name  | years_of_exp | interview_id |
|--------------|-------|--------------|--------------|
| 1            | Mia   | 2            | 201          |
| 2            | Kai   | 2            | 202          |
| 3            | Noor  | 1            | 203          |
| 4            | Ivan  | 5            | 204          |

Rounds:
| interview_id | round_id | score |
|--------------|----------|-------|
| 201          | 1        | 7     |
| 201          | 2        | 8     |
| 202          | 1        | 8     |
| 202          | 2        | 8     |
| 203          | 1        | 10    |
| 203          | 2        | 10    |
| 204          | 1        | 4     |
| 204          | 2        | 4     |
| 204          | 3        | 6     |

Output

[
  {"candidate_id":2}
]

Explanation

Candidate 1 is rejected because total score is exactly 15 (strictly greater than 15 is required). Candidate 2 passes with total 16 and experience 2. Candidate 3 fails on experience. Candidate 4 fails on score total.

Example 3

Input

Candidates:
| candidate_id | name  | years_of_exp | interview_id |
|--------------|-------|--------------|--------------|
| 21           | Anya  | 3            | 301          |
| 22           | Theo  | 4            | 302          |
| 23           | Omar  | 2            | 303          |
| 24           | Zuri  | 12           | 304          |

Rounds:
| interview_id | round_id | score |
|--------------|----------|-------|
| 301          | 1        | 9     |
| 301          | 2        | 9     |
| 302          | 1        | 5     |
| 302          | 2        | 4     |
| 302          | 3        | 6     |
| 303          | 1        | 16    |

Output

[
  {"candidate_id":21},
  {"candidate_id":23}
]

Explanation

Candidate 24 has no rounds and therefore does not meet the score requirement. Candidates 21 and 23 both pass all rules.

Constraints

0 <= number of rows in Candidates <= 250000
0 <= number of rows in Rounds <= 500000
Candidates.candidate_id values are unique
Each candidate has one interview_id in Candidates
(Rounds.interview_id, Rounds.round_id) pairs are unique
0 <= candidate_id <= 1000000000
0 <= interview_id <= 1000000000
0 <= years_of_exp <= 100
0 <= score <= 100
Use strict comparison for score total: SUM(score) > 15
Use non-strict comparison for experience: years_of_exp >= 2
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column: candidate_id

Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|---------|--------------|--------------| | 11 | Atticus | 1 | 101 | | 9 | Ruben | 6 | 104 | | 6 | Aliza | 10 | 109 | | 8 | Alfredo | 0 | 107 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 109 | 3 | 4 | | 101 | 2 | 8 | | 109 | 4 | 1 | | 107 | 1 | 3 | | 104 | 3 | 6 | | 109 | 1 | 4 | | 104 | 4 | 7 | | 104 | 1 | 2 | | 109 | 2 | 1 | | 104 | 2 | 7 | | 107 | 2 | 3 | | 101 | 1 | 8 |

Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|-------|--------------|--------------| | 1 | Mia | 2 | 201 | | 2 | Kai | 2 | 202 | | 3 | Noor | 1 | 203 | | 4 | Ivan | 5 | 204 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 201 | 1 | 7 | | 201 | 2 | 8 | | 202 | 1 | 8 | | 202 | 2 | 8 | | 203 | 1 | 10 | | 203 | 2 | 10 | | 204 | 1 | 4 | | 204 | 2 | 4 | | 204 | 3 | 6 |

Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|-------|--------------|--------------| | 21 | Anya | 3 | 301 | | 22 | Theo | 4 | 302 | | 23 | Omar | 2 | 303 | | 24 | Zuri | 12 | 304 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 301 | 1 | 9 | | 301 | 2 | 9 | | 302 | 1 | 5 | | 302 | 2 | 4 | | 302 | 3 | 6 | | 303 | 1 | 16 |