Accepted Candidates From the Interviews - Practice Coding Problems

Accepted Candidates From the Interviews - Problem

Database Medium

You're working for a tech company's HR department that needs to analyze interview results to identify qualified candidates. The company has a structured interview process where candidates go through multiple rounds, and you need to filter candidates based on two key criteria:

Experience requirement: At least 2 years of professional experience
Performance threshold: Total interview score must be strictly greater than 15

You have access to two tables:

Candidates - Contains candidate information including their experience and interview ID
Rounds - Contains individual round scores for each interview

Your task: Write a SQL query to find all candidate IDs who meet both criteria and should be accepted for the position.

The results can be returned in any order.

Input & Output

example_1.sql — Basic Example

$ Input: Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|-------|--------------|---------------| | 11 | Atticus | 1 | 101 | | 21 | Ruben | 6 | 102 | | 22 | Aliza | 10 | 103 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 101 | 1 | 8 | | 101 | 2 | 7 | | 102 | 1 | 9 | | 102 | 2 | 4 | | 102 | 3 | 6 | | 103 | 1 | 10 | | 103 | 2 | 1 |

› Output: | candidate_id | |--------------| | 21 |

💡 Note: Only candidate 21 (Ruben) meets both criteria: has 6 years experience (≥2) and total interview score of 9+4+6=19 (>15). Candidate 11 has insufficient experience (1 year), and candidate 22 has sufficient experience but low score (10+1=11).

example_2.sql — Edge Case: Exact Threshold

$ Input: Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|------|--------------|---------------| | 1 | John | 2 | 201 | | 2 | Jane | 3 | 202 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 201 | 1 | 15 | | 202 | 1 | 16 |

› Output: | candidate_id | |--------------| | 2 |

💡 Note: Candidate 1 has exactly 15 points, but we need STRICTLY greater than 15. Only candidate 2 with 16 points qualifies.

example_3.sql — Multiple Qualifying Candidates

$ Input: Candidates: | candidate_id | name | years_of_exp | interview_id | |--------------|------|--------------|---------------| | 10 | Alice | 5 | 301 | | 20 | Bob | 3 | 302 | | 30 | Carol | 8 | 303 | Rounds: | interview_id | round_id | score | |--------------|----------|-------| | 301 | 1 | 10 | | 301 | 2 | 8 | | 302 | 1 | 20 | | 303 | 1 | 7 | | 303 | 2 | 9 |

› Output: | candidate_id | |--------------| | 10 | | 20 |

💡 Note: Alice (total: 18) and Bob (total: 20) both qualify with sufficient experience and scores >15. Carol has enough experience but only 16 total points, which does qualify her as well, so all three should be included.

Visualization

Tap to expand

Understanding the Visualization

Collect Applications

Gather all candidate profiles with their experience levels

Aggregate Scores

Sum up scores from all interview rounds for each candidate

Apply Filters

Check both experience requirement (≥2 years) and performance threshold (>15 points)

Select Winners

Accept candidates who meet both criteria

Key Takeaway

🎯 Key Insight: Using SQL JOIN with GROUP BY allows us to combine and aggregate data in a single efficient pass, eliminating the need for expensive correlated subqueries.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through candidates (n) and rounds (m) tables with efficient join

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique candidates for grouping

✓ Linear Space

Constraints

1 ≤ candidate_id, interview_id ≤ 10⁶
0 ≤ years_of_exp ≤ 50
1 ≤ round_id ≤ 10
0 ≤ score ≤ 100
Each candidate has a unique interview_id
Each interview has at least one round

Asked in

⊞ Microsoft 35 a Amazon 28 G Google 22 f Meta 18

The optimal solution uses SQL JOIN with GROUP BY to efficiently combine candidate information with their aggregated interview scores. By joining the tables and using SUM() with HAVING clause, we can filter candidates in a single optimized query with O(n+m) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Optimal SQL JOIN with GROUP BY	O(n + m)	O(k)	Use JOIN and GROUP BY to efficiently combine and aggregate data in one pass
Brute Force (Subquery for Each Candidate)	O(n²)	O(1)	Use correlated subquery to calculate sum for each candidate separately

Optimal SQL JOIN with GROUP BY — Algorithm Steps

JOIN Candidates and Rounds tables on interview_id
GROUP BY candidate information to aggregate scores
Use SUM() to calculate total score for each candidate
Apply WHERE clause for experience >= 2 years
Apply HAVING clause for total score > 15

Visualization

Tap to expand

Step-by-Step Walkthrough

Build Score Map

Single pass through rounds table to aggregate scores by interview_id

Filter Candidates

Single pass through candidates, checking experience and looking up total scores

Quick Lookup

O(1) hash map lookup for each candidate's total score

Apply Filters

Check both conditions efficiently in one step

Code -

solution.c — C

// Optimal approach using hash table simulation
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// Simple hash table structure for interview scores
typedef struct HashNode {
    int interview_id;
    int total_score;
    struct HashNode* next;
} HashNode;

int* optimalSolution(Candidate* candidates, int candidatesSize, Round* rounds, int roundsSize, int* returnSize) {
    // Create hash table for scores
    HashNode* hash_table[1000] = {NULL}; // Simple hash table
    
    // Build score hash table
    for (int i = 0; i < roundsSize; i++) {
        int hash = rounds[i].interview_id % 1000;
        HashNode* node = hash_table[hash];
        
        // Find existing node or create new one
        while (node && node->interview_id != rounds[i].interview_id) {
            node = node->next;
        }
        
        if (node) {
            node->total_score += rounds[i].score;
        } else {
            HashNode* new_node = malloc(sizeof(HashNode));
            new_node->interview_id = rounds[i].interview_id;
            new_node->total_score = rounds[i].score;
            new_node->next = hash_table[hash];
            hash_table[hash] = new_node;
        }
    }
    
    // Find qualified candidates
    int* result = malloc(candidatesSize * sizeof(int));
    *returnSize = 0;
    
    for (int i = 0; i < candidatesSize; i++) {
        if (candidates[i].years_of_exp >= 2) {
            int hash = candidates[i].interview_id % 1000;
            HashNode* node = hash_table[hash];
            
            while (node && node->interview_id != candidates[i].interview_id) {
                node = node->next;
            }
            
            if (node && node->total_score > 15) {
                result[(*returnSize)++] = candidates[i].candidate_id;
            }
        }
    }
    
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through candidates (n) and rounds (m) tables with efficient join

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique candidates for grouping

✓ Linear Space

Constraints

1 ≤ candidate_id, interview_id ≤ 10⁶
0 ≤ years_of_exp ≤ 50
1 ≤ round_id ≤ 10
0 ≤ score ≤ 100
Each candidate has a unique interview_id
Each interview has at least one round

24.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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Optimal SQL JOIN with GROUP BY — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler