Suppose I have the following applicant data for jobs in a company:
id position salary —————————————————————— 0 senior 20000 1 senior 15000 2 associate 10000
The budget is 40000 and the preference is to hire senior managers. What PostgreSQL constructs do I use to get the following result as far as the number of hires are concerned.
seniors associates ——————————————————— 2 0
Any directions would be appreciated.
Here is a starting sqlfiddle: http://sqlfiddle.com/#!17/2cef4/1
Advertisement
Answer
Using PostgreSQL filters and window functions, I was able to come up with a query that produced the result.
select count(*) filter(where s.position = 'senior') as seniors, count(*) filter(where s.position = 'associate') as associates from ( select position, sum(salary) over(order by position desc rows between unbounded preceding and current row) as salary from candidates ) as s where s.salary <= 40000;
Example: http://sqlfiddle.com/#!17/2cef4/10