Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (
  id int,
  position varchar(20),
  value int
);

insert into mytable values
(5,    'senior',      10000),
(6,    'senior',      20000),
(8,    'senior',      30000),
(9,    'junior',      5000),
(4,   'junior' ,     7000),
(3,    'junior',      10000);


CREATE TABLE



INSERT 0 6


select *
from mytable;


id
position
value


5
senior
10000

6
senior
20000

8
senior
30000

9
junior
5000

4
junior
7000

3
junior
10000



SELECT 6


with cte as (
  SELECT *, SUM(value) OVER(PARTITION BY position ORDER BY id) AS cumulative_sum
  FROM mytable
)
select position, count(1)
from cte
where cumulative_sum < 50000
group by position


position
count


junior
3

senior
2



SELECT 2


 

id	position	value
5	senior	10000
6	senior	20000
8	senior	30000
9	junior	5000
4	junior	7000
3	junior	10000