LeetCode 2004. The Number of Seniors and Juniors to Join the Company SQL Solution | Explanation + Code

Description

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:

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 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 is40000, 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 is20000, 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.

Solutions

Solution 1

sql

# Write your MySQL query statement below WITH s AS ( SELECT employee_id, SUM(salary) OVER (ORDER BY salary) AS cur FROM Candidates WHERE experience = 'Senior' ), j AS ( SELECT employee_id, IFNULL( SELECT MAX(cur) FROM s WHERE cur <= 70000, 0 ) + SUM(salary) OVER (ORDER BY salary) AS cur FROM Candidates WHERE experience = 'Junior' ) SELECT 'Senior' AS experience, COUNT(employee_id) AS accepted_candidates FROM s WHERE cur <= 70000 UNION ALL SELECT 'Junior' AS experience, COUNT(employee_id) AS accepted_candidates FROM j WHERE cur <= 70000;(code-box)