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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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

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

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