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
I think what you are looking for is the following (after your existing CTEs):
You can see it working in a Fiddle here.
However, I do think your first CTE should also likely be changed from
to
It is equivalent in both of your examples, however unless you know for sure that
id
is always in the same order assalary
,salary
seems safer. You need to order that window function bysalary
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 treatnull
as 0.Try this :
see the test result in dbfiddle