Problem

Table: Candidates

1
2
3
4
5
6
7
8
9

+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| candidate_id | int      |
| name         | varchar  |
| years_of_exp | int      |
| interview_id | int      |
+--------------+----------+
candidate_id is the primary key column for this table.

Each row of this table indicates the name of a candidate, their number of years of experience, and their interview ID.

Table: Rounds

1
2
3
4
5
6
7
8
9

+--------------+------+
| Column Name  | Type |
+--------------+------+
| interview_id | int  |
| round_id     | int  |
| score        | int  |
+--------------+------+

(interview_id, round_id) is the primary key column for this table.

Each row of this table indicates the score of one round of an interview.

Write an SQL query to report the IDs of the candidates who have at least two years of experience and the sum of the score of their interview rounds is strictly greater than 15.

Return the result table in any order.

The query result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

Input: 
Candidates table:
+--------------+---------+--------------+--------------+
| 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 table:
+--------------+----------+-------+
| 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 11: The total score is 16, and they have one year of experience. We do not include them in the result table because of their years of experience.
- Candidate 9: The total score is 22, and they have six years of experience. We include them in the result table.
- Candidate 6: The total score is 10, and they have ten years of experience. We do not include them in the result table because the score is not good enough.
- Candidate 8: The total score is 6, and they have zero years of experience. We do not include them in the result table because of their years of experience and the score.

Solution

Method 1 – Using SQL Joins and Aggregation

Intuition

The key idea is to join the Candidates and Rounds tables on interview_id, then group by each candidate to sum their interview scores. We filter candidates who have at least 2 years of experience and a total interview score greater than 15.

Approach

  1. Join Tables: Use an INNER JOIN to combine Candidates and Rounds on interview_id.
  2. Group and Aggregate: Group the results by candidate_id and sum the score for each candidate.
  3. Apply Filters:
    • Only include candidates with years_of_exp >= 2.
    • Only include candidates whose total score is strictly greater than 15.
  4. Select Output: Return only the candidate_id column.

Code

1
2
3
4
5
6

SELECT c.candidate_id
FROM Candidates c
JOIN Rounds r ON c.interview_id = r.interview_id
WHERE c.years_of_exp >= 2
GROUP BY c.candidate_id
HAVING SUM(r.score) > 15;

Complexity

  • ⏰ Time complexity: O(N + M), where N is the number of candidates and M is the number of rounds (due to the join and aggregation).
  • 🧺 Space complexity: O(K), where K is the number of unique candidates (for grouping).