Table: Candidates
+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int |
| experience | enum |
| salary | int |
+-------------+------+
employee_id is the column with unique values for this table.
experience is an ENUM (category) type of values ('Senior', 'Junior').
Each row of this table indicates the id of a candidate, their monthly salary, and their experience.
A company wants to hire new employees. The budget of the company for the salaries is $70000. The company's criteria for hiring are:
Write a solution to find the number of seniors and juniors hired under the mentioned criteria.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Candidates table: +-------------+------------+--------+ | employee_id | experience | salary | +-------------+------------+--------+ | 1 | Junior | 10000 | | 9 | Junior | 10000 | | 2 | Senior | 20000 | | 11 | Senior | 20000 | | 13 | Senior | 50000 | | 4 | Junior | 40000 | +-------------+------------+--------+ Output: +------------+---------------------+ | experience | accepted_candidates | +------------+---------------------+ | Senior | 2 | | Junior | 2 | +------------+---------------------+ Explanation: We can hire 2 seniors with IDs (2, 11). Since the budget is $70000 and the sum of their salaries is $40000, we still have $30000 but they are not enough to hire the senior candidate with ID 13. We can hire 2 juniors with IDs (1, 9). Since the remaining budget is $30000 and the sum of their salaries is $20000, we still have $10000 but they are not enough to hire the junior candidate with ID 4.
Example 2:
Input: Candidates table: +-------------+------------+--------+ | employee_id | experience | salary | +-------------+------------+--------+ | 1 | Junior | 10000 | | 9 | Junior | 10000 | | 2 | Senior | 80000 | | 11 | Senior | 80000 | | 13 | Senior | 80000 | | 4 | Junior | 40000 | +-------------+------------+--------+ Output: +------------+---------------------+ | experience | accepted_candidates | +------------+---------------------+ | Senior | 0 | | Junior | 3 | +------------+---------------------+ Explanation: We cannot hire any seniors with the current budget as we need at least $80000 to hire one senior. We can hire all three juniors with the remaining budget.
Problem Overview: You are given a table of candidates with their experience level (Senior or Junior) and salary. The company has a fixed hiring budget. Seniors must be hired first, starting from the lowest salary, and then juniors can be hired with whatever budget remains. The task is to compute how many seniors and juniors can be hired without exceeding the budget.
Approach 1: Window Function with Running Salary Sum (O(n log n) time, O(n) space)
This approach uses SQL window functions to compute a running total of salaries after sorting candidates by salary. First filter the senior candidates and calculate a cumulative salary using SUM(salary) OVER (ORDER BY salary). Only seniors whose cumulative salary stays within the total budget are selected. After determining how much budget the selected seniors consume, compute the remaining budget and repeat the same process for junior candidates. Window functions allow you to efficiently compute prefix sums without manual iteration, making this approach clean and expressive in SQL.
The key insight: hiring is greedy. Since the goal is to maximize the number of hires under a fixed budget, always pick the lowest salary first within each experience group. Sorting combined with cumulative sums naturally models this greedy strategy. The database engine handles ordering and prefix aggregation efficiently.
Approach 2: Subqueries with Aggregation (O(n log n) time, O(n) space)
If window functions are unavailable, you can simulate the same logic using nested subqueries. First compute the set of seniors that fit within the budget by repeatedly aggregating salaries in sorted order. Then subtract the total salary of selected seniors from the budget to determine the remaining amount available for juniors. A second query applies the same logic to junior candidates.
This method relies heavily on ordered subqueries and aggregation functions such as SUM(). While it works in most SQL engines, the queries become harder to read and maintain compared with a window-based solution. Still, it demonstrates the same greedy hiring logic using basic database operations.
Recommended for interviews: The window function solution is what interviewers typically expect for modern SQL problems. It shows strong understanding of ordering, cumulative aggregation, and efficient filtering using window functions. The subquery approach proves you understand the greedy logic, but the window-based query is cleaner and more scalable.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Window Function with Running Sum | O(n log n) | O(n) | Preferred solution when window functions are available in the SQL engine |
| Subqueries with Aggregation | O(n log n) | O(n) | Useful when window functions are unsupported or restricted |
LeetCode Hard 2004 "Number of Seniors and Juniors" Wayfair Interview SQL Question With Explanation • Everyday Data Science • 3,106 views views
Watch 1 more video solutions →Practice The Number of Seniors and Juniors to Join the Company with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor