SQLize Online / PHPize Online  /  SQLtest Online

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