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 c_sal AS ( SELECT id, position, salary, Count(salary) OVER(partition BY position ORDER BY salary rows UNBOUNDED PRECEDING) AS 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 cnt AS senior, junior_cnt AS junior FROM ( SELECT Max(c_sal."cumulative salary") OVER(partition BY c_sal.position) AS se_cum_sum, c_sal.cnt FROM c_sal WHERE c_sal.position = 'senior' AND c_sal."cumulative salary" <= 150000 ORDER BY cnt DESC limit 1) AS x, ( SELECT c_sal."cumulative salary", c_sal.cnt AS junior_cnt FROM c_sal WHERE c_sal.position = 'junior' AND c_sal."cumulative salary" <= 150000) AS y WHERE ( x.se_cum_sum + y."cumulative salary") <= 150000 ORDER BY junior_cnt DESC limit 1