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

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. Hiring the largest number of seniors.
2. After hiring the maximum number of seniors, use the remaining budget to hire the largest number of juniors.

Write an SQL query to find the number 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,employee_id ASC) AS RN 
    FROM Candidates
)
      
SELECT 
    'Senior' AS experience, 
    COUNT(employee_id) AS accepted_candidates 
FROM CTE 
WHERE experience = 'Senior' AND RN < 70000
UNION
SELECT 
    'Junior' AS experience, 
    COUNT(employee_id) AS accepted_candidates 
FROM CTE 
WHERE experience = 'Junior' AND RN < (SELECT 70000 - IFNULL(MAX(RN),0) FROM CTE WHERE experience = 'Senior' AND RN < 70000)