SQLize | PHPize | SQLtest

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 c_sal."cumulative salary", Max(c_sal.cnt) AS junior_cnt FROM c_sal WHERE c_sal.position = 'junior' AND c_sal."cumulative salary" <= 150000 GROUP BY c_sal.position, c_sal."cumulative salary"