SQL Interview Questions – The Number of Seniors and Juniors to Join the Company II

Problem Description –

A company wants to hire new employees. The budget of the company for the salaries is $70000. The company’s criteria for hiring are:

1. Keep hiring the senior with the smallest salary until you cannot hire any more seniors.
2. Use the remaining budget to hire the junior with the smallest salary.
3. Keep hiring the junior with the smallest salary until you cannot hire any more juniors.

Write an SQL query to find the ids of seniors and juniors hired under the mentioned criteria.

Return the result table in any order.

The query result format is in the following example.

Problem Link – Seniors and Juniors

Difficulty Level – Hard

Solution –

WITH CTE AS (
    SELECT employee_id, 
    experience, 
    SUM(salary) OVER(PARTITION BY experience ORDER BY salary ASC) AS RN 
    FROM Candidates
)
      
SELECT employee_id FROM CTE WHERE experience = 'Senior' AND RN < 70000
UNION
SELECT employee_id FROM CTE WHERE experience = 'Junior' AND RN < (SELECT 70000 - IFNULL(MAX(RN),0) FROM CTE WHERE experience = 'Senior' AND RN < 70000)