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

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.

CodePudding user response：

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.

Here's an additional Fiddle using the new example data (from the comments) where no senior staff can be hired. It uses COALESCE to treat null as 0.

CodePudding user response：

Try this :

WITH senior_list AS (
SELECT count(*) OVER (ORDER BY id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_senior
     , sum(salary) OVER (ORDER BY id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_senior
  FROM people
 WHERE seniority_level = 'senior'
), junior_list AS (
SELECT count(*) OVER (ORDER BY id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS count_junior
     , sum(salary) OVER (ORDER BY id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_junior
  FROM people
 WHERE seniority_level = 'junior'
)
SELECT COALESCE(max(count_senior), 0) AS senior_hires
     , COALESCE(max(count_junior), 0) AS junior_hires
  FROM senior_list AS sl
  LEFT JOIN junior_list AS jl
    ON sum_junior <= 40000 - sum_senior
 WHERE sum_senior <= 40000

see the test result in dbfiddle