LeetCode 2004. The Number of Seniors and Juniors to Join the Company SQL Solution

Problem

LeetCode SQL Problem

2004. The Number of Seniors and Juniors to Join the Company

Solution

• Partition employees by experience into 'Senior' and 'Junior' window frames. Within each frame, sort employees by salary from lowest to highest and calculate running total so we can hire the largest number of employees possible.
• Note that it's important to also sort by employee_id within each frame so we can get correct amount of running total when there are duplicate values of salary.
• Hire as many senior employees as possible and calculate the remaining budget amount.
• Now use the remaining amount of budget to hire as many juniors as possible.
• UNION ALL senior and junior hiring results

MySQL

SET @hiring_budget := 70000;

WITH salary_run_total
AS (
	SELECT c.*,
		sum(salary) OVER (
			PARTITION BY c.experience ORDER BY c.salary,
				c.employee_id
			) AS rolling_sum
	FROM Candidates c
	),
senior_hiring
AS (
	SELECT 'Senior' AS experience,
		count(*) AS accepted_candidates,
		CASE
			WHEN count(*) = 0
				THEN @hiring_budget
			ELSE @hiring_budget - max(srt.rolling_sum)
			END AS remaining_budget
	FROM salary_run_total srt
	WHERE srt.experience = 'Senior'
		AND srt.rolling_sum <= @hiring_budget
	),
junior_hiring
AS (
	SELECT 'Junior' AS experience,
		count(*) AS accepted_candidates
	FROM salary_run_total srt
	WHERE srt.experience = 'Junior'
		AND srt.rolling_sum <= (
			SELECT remaining_budget
			FROM senior_hiring
			)
	)
SELECT sh.experience,
	sh.accepted_candidates
FROM senior_hiring sh

UNION ALL

SELECT *
FROM junior_hiring

Tests

We'll conduct some tests to verify our MySQL script assuming our hiring budget is $70,000.

Test 1

The hiring budget is enough to hire some seniors and some juniors.

Candidates

employee_id	experience	salary
1	Junior	10000
9	Junior	10000
2	Senior	20000
11	Senior	20000
13	Senior	50000
4	Junior	40000

Hiring Results

experience	accepted_candidates
Senior	2
Junior	2

Test 2

The hiring budget is enough to only hire juniors.

Candidates

employee_id	experience	salary
1	Junior	10000
9	Junior	10000
2	Senior	80000
11	Senior	80000
13	Senior	80000
4	Junior	40000

Hiring Results

experience	accepted_candidates
Senior	0
Junior	3

Test 3

The hiring budget is only enough to hire a senior.

employee_id	experience	salary
1	Junior	10000
9	Junior	10000
2	Senior	69000
11	Senior	80000
13	Senior	80000
4	Junior	40000

Hiring Results

experience	accepted_candidates
Senior	1
Junior	0

Test 4

The hiring budget is not enough to hire any senior or junior!

employee_id	experience	salary
1	Junior	71000
9	Junior	72000
2	Senior	89000
11	Senior	80000
13	Senior	80000
4	Junior	73000

Hiring Results

experience	accepted_candidates
Senior	0
Junior	0