Postgres 15

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

create table test_table (id smallint, position text, value int);

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

CREATE TABLE




id
position
value


5
senior
10000

6
senior
20000

8
senior
30000

9
junior
5000

4
junior
7000

3
junior
10000

1
junior
7000



INSERT 0 7


select 
  count(case when chek_sum_jun >0 and position = 'junior'  then position else null end) chek_jun,
  count(case when  chek_sum_sen>0  and position = 'senior' then  position else null end) chek_sen
from (
select position, 
20000 - sum(case when position = 'junior' then value else 0 end) over (partition by position order by value asc rows between unbounded preceding and current row )  chek_sum_jun,
50000 - sum(case when position = 'senior' then value else 0 end) over (partition by position order by value asc rows between unbounded preceding and current row )  chek_sum_sen
from test_table) x


chek_jun
chek_sen


3
2



SELECT 1


select position, 
20000 - sum(case when position = 'junior' then value else 0 end) over (partition by position order by value asc rows between unbounded preceding and current row )  chek_sum_jun,
50000 - sum(case when position = 'senior' then value else 0 end) over (partition by position order by value asc rows between unbounded preceding and current row )  chek_sum_sen
from test_table


position
chek_sum_jun
chek_sum_sen


junior
15000
50000

junior
8000
50000

junior
1000
50000

junior
-9000
50000

senior
20000
40000

senior
20000
20000

senior
20000
-10000



SELECT 7

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

position	chek_sum_jun	chek_sum_sen
junior	15000	50000
junior	8000	50000
junior	1000	50000
junior	-9000	50000
senior	20000	40000
senior	20000	20000
senior	20000	-10000