skip to Main Content

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.

2

Answers


  1. 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
    

    to

    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.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search