create table candidates (
id int primary key,
position varchar not null,
salary int not null
);
insert into candidates values (1, 'junior', 10500);
insert into candidates values (2, 'senior', 15000);
insert into candidates values (3, 'senior', 35000);
insert into candidates values (4, 'junior', 8000); insert
into candidates values (5, 'senior', 30000); insert into
candidates values (6, 'senior', 25000); insert into
candidates values (7, 'junior', 30000); insert into
candidates values (8, 'senior', 50000); insert into
candidates values (9, 'senior', 30000); insert into
candidates values (10, 'junior', 7000); insert into
candidates values (11, 'junior', 8000); insert into
candidates values (12, 'senior', 33000); insert into
candidates values (13, 'junior', 5000); insert into
candidates values (14, 'senior', 47000); insert into
candidates values (15, 'junior', 12000);
WITH cum_sal_stm AS
(
SELECT id,
position,
salary,
Count(salary) OVER(partition BY position ORDER BY salary rows UNBOUNDED PRECEDING) AS cu_cnt,
Sum(salary) OVER(partition BY position ORDER BY salary rows UNBOUNDED PRECEDING) AS cumulative_salary
FROM candidates
ORDER BY position DESC,
cumulative_salary ASC
)
SELECT senior_cnt AS senior,
junior_cnt AS junior
FROM (
SELECT Max(cum_sal_stm.cumulative_salary) OVER(partition BY cum_sal_stm.position) AS total_senior_salary,
cum_sal_stm.cu_cnt as senior_cnt
FROM cum_sal_stm
WHERE cum_sal_stm.position = 'senior'
AND cum_sal_stm.cumulative_salary <= 150000
ORDER BY cu_cnt DESC limit 1) AS x,
(
SELECT cum_sal_stm.cumulative_salary,
cum_sal_stm.cu_cnt AS junior_cnt
FROM cum_sal_stm
WHERE cum_sal_stm.position = 'junior') AS y
WHERE (
x.total_senior_salary + y.cumulative_salary) <= 150000
ORDER BY junior_cnt DESC limit 1
Telegram: DB developers channel