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:
- Hiring the largest number of seniors.
- 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
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)