Home SQL to answer how many people of varying seniority you can hire under a budget

Questions

SQL to answer how many people of varying seniority you can hire under a budget

December 21, 2022

I have a table with ID, seniority level, and salary (Postgres)

CREATE TABLE people (
  id int,
  seniority_level varchar(255),
  salary int
 );

The goal is to hire as many senior people under a budget of 40,000 and with the remaining money hire as many junior people. I have the logic to do this successfully but not in the proper format or handling certain conditions.

with total_cost AS
(
  SELECT 
    id,
    seniority_level,
    salary,
    SUM(salary) over (partition by seniority_level order by id ASC) as cost
  FROM people
),
senior_can_hire AS
(
  SELECT 
    id,
    seniority_level,
    salary
  FROM total_cost 
  WHERE seniority_level = 'senior' 
  AND cost <=40000
),
junior_can_hire AS
(
  SELECT 
    id, 
    seniority_level,
    salary
  FROM total_cost
  WHERE seniority_level = 'junior'
  AND cost <= 40000 - (SELECT SUM(salary) FROM senior_can_hire)
)
SELECT 
  seniority_level,
  COUNT(id) AS NUM_HIRES
FROM senior_can_hire
GROUP BY seniority_level
UNION
SELECT 
  seniority_level,
  COUNT(id) AS NUM_HIRES
FROM junior_can_hire
GROUP BY seniority_level

The idea is to display 1 row as a result with 2 columns containing the number of seniors you can hire and the number of juniors you can hire.

CASE 1:

INSERT INTO people values(20, 'junior', 10000);
INSERT INTO people values(30, 'senior', 15000);
INSERT INTO people values(40, 'senior', 30000);

My results are two columns, two rows: seniority_level and num_hires and a row for senior, row for junior each containing 1 in num_hires.

The desired results is 1 row, two columns: senior_hires, junior_hires and each value containing 1.

CASE 2:

 INSERT INTO people values(20, 'senior', 10000);
 INSERT INTO people values(30, 'senior', 15000);
 INSERT INTO people values(40, 'senior', 30000);

My results only give me 1 row saying you can hire 2 seniors. Desired results is 1 row saying you can hire 2 seniors but also indicating 0 for juniors.

>Solution :

I think what you are looking for is the following (after your existing CTEs):

SELECT 
  (SELECT count(*) FROM senior_can_hire) senior,
  (SELECT count(*) FROM junior_can_hire) junior

You can see it working in a Fiddle here.

However, I do think your first CTE should also likely be changed from

SUM(salary) over (partition by seniority_level order by id ASC) as cost

SUM(salary) over (partition by seniority_level order by salary ASC) as cost

It is equivalent in both of your examples, however unless you know for sure that id is always in the same order as salary, salary seems safer. You need to order that window function by salary to ensure you are maximizing the numbers of people who can be hired.