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 *,
       sum(value) over w as cum_sum
from test_table
window w as (
  order by position desc, 
           value asc );


id
position
value
cum_sum


5
senior
10000
10000

6
senior
20000
30000

8
senior
30000
60000

9
junior
5000
65000

4
junior
7000
79000

1
junior
7000
79000

3
junior
10000
89000



SELECT 7


with cumulative_sums as (
select *,
       sum(value) over w as cum_sum
from test_table
window w as (
  order by position desc, 
           value asc )
  )
select *, case when cum_sum>50000 then value else 0 end as strike
from cumulative_sums;


id
position
value
cum_sum
strike


5
senior
10000
10000
0

6
senior
20000
30000
0

8
senior
30000
60000
30000

9
junior
5000
65000
5000

4
junior
7000
79000
7000

1
junior
7000
79000
7000

3
junior
10000
89000
10000



SELECT 7


select *,
       row_number() over () 
from test_table 
order by position desc, 
         value asc;


id
position
value
row_number


5
senior
10000
1

6
senior
20000
2

8
senior
30000
3

9
junior
5000
4

4
junior
7000
5

1
junior
7000
7

3
junior
10000
6



SELECT 7


--to show what it does
with recursive 
 ordered as --this will be fed to the actual recursive cte
(   select *,
           row_number() over (order by position desc,value asc) 
    from test_table)
,recursive_cte as 
( select id,
         position,
         value, 
         value*(value<50000)::int as cum_sum,
         value<50000 as is_hired,
         2 as next_i
  from ordered
  where row_number=1
  union
  select o.id,
         o.position,
         o.value, 
         case when o.value+r.cum_sum<50000 then o.value+r.cum_sum else r.cum_sum end,
         (o.value+r.cum_sum)<50000 as is_hired,
         r.next_i+1 as next_i
  from recursive_cte r, 
       ordered o
  where o.row_number=next_i
)
select * from recursive_cte;


id
position
value
cum_sum
is_hired
next_i


5
senior
10000
10000
t
2

6
senior
20000
30000
t
3

8
senior
30000
30000
f
4

9
junior
5000
35000
t
5

4
junior
7000
42000
t
6

1
junior
7000
49000
t
7

3
junior
10000
49000
f
8



SELECT 7


--actual result
with recursive 
 ordered as --this will be fed to the actual recursive cte
(   select *,
           row_number() over (order by position desc,value asc) 
    from test_table)
,recursive_cte as 
( select id,
         position,
         value, 
         value*(value<50000)::int as cum_sum,
         value<50000 as is_hired,
         2 as next_i
  from ordered
  where row_number=1
  union
  select o.id,
         o.position,
         o.value, 
         case when o.value+r.cum_sum<50000 then o.value+r.cum_sum else r.cum_sum end,
         (o.value+r.cum_sum)<50000 as is_hired,
         r.next_i+1 as next_i
  from recursive_cte r, 
       ordered o
  where o.row_number=next_i
)
select count(*) filter (where position='junior') as juniors,
       count(*) filter (where position='senior') as seniors
from recursive_cte 
where is_hired;


juniors
seniors


3
2



SELECT 1


--more tests

insert into test_table (id,position,value) values
(10,'senior',1111),
(11,'senior',1),
(12,'senior',40000),
(13,'junior',500),
(14,'junior',100),
(15,'junior',50001),
(16,'junior',7000),
(17,'senior',60000);

with recursive 
 ordered as --this will be fed to the actual recursive cte
(   select *,
           row_number() over (order by position desc,value asc) 
    from test_table)
,recursive_cte as 
( select id,
         position,
         value, 
         value*(value<50000)::int as cum_sum,
         value<50000 as is_hired,
         2 as next_i
  from ordered
  where row_number=1
  union
  select o.id,
         o.position,
         o.value, 
         case when o.value+r.cum_sum<50000 then o.value+r.cum_sum else r.cum_sum end,
         (o.value+r.cum_sum)<50000 as is_hired,
         r.next_i+1 as next_i
  from recursive_cte r, 
       ordered o
  where o.row_number=next_i
)
select * from recursive_cte;

with recursive 
 ordered as --this will be fed to the actual recursive cte
(   select *,
           row_number() over (order by position desc,value asc) 
    from test_table)
,recursive_cte as 
( select id,
         position,
         value, 
         value*(value<50000)::int as cum_sum,
         value<50000 as is_hired,
         2 as next_i
  from ordered
  where row_number=1
  union
  select o.id,
         o.position,
         o.value, 
         case when o.value+r.cum_sum<50000 then o.value+r.cum_sum else r.cum_sum end,
         (o.value+r.cum_sum)<50000 as is_hired,
         r.next_i+1 as next_i
  from recursive_cte r, 
       ordered o
  where o.row_number=next_i
)
select count(*) filter (where position='junior') as juniors,
       count(*) filter (where position='senior') as seniors
from recursive_cte 
where is_hired;

INSERT 0 8




id
position
value
cum_sum
is_hired
next_i


11
senior
1
1
t
2

10
senior
1111
1112
t
3

5
senior
10000
11112
t
4

6
senior
20000
31112
t
5

8
senior
30000
31112
f
6

12
senior
40000
31112
f
7

17
senior
60000
31112
f
8

14
junior
100
31212
t
9

13
junior
500
31712
t
10

9
junior
5000
36712
t
11

4
junior
7000
43712
t
12

16
junior
7000
43712
f
13

1
junior
7000
43712
f
14

3
junior
10000
43712
f
15

15
junior
50001
43712
f
16



SELECT 15




juniors
seniors


4
4



SELECT 1


--ither solutions start breaking on more tests
with cte as (
  SELECT *, SUM(value) OVER(PARTITION BY position ORDER BY id) AS cumulative_sum
  FROM test_table
)
select position, count(1)
from cte
where cumulative_sum < 50000
group by position;

select 
  count(case when position = 'junior' then position else null end) chek_jun,
  count(case when position = 'senior' then   position else null end) chek_sen
from
(select position, 
sum(value) over (partition by position order by value asc rows between unbounded preceding and current row ) chek_sum
from test_table
)x 
where x.chek_sum <=50000;


position
count


junior
6

senior
2



SELECT 2




chek_jun
chek_sen


7
4



SELECT 1
id	position	value
5	senior	10000
6	senior	20000
8	senior	30000
9	junior	5000
4	junior	7000
3	junior	10000
1	junior	7000
id	position	value	cum_sum	is_hired	next_i
11	senior	1	1	t	2
10	senior	1111	1112	t	3
5	senior	10000	11112	t	4
6	senior	20000	31112	t	5
8	senior	30000	31112	f	6
12	senior	40000	31112	f	7
17	senior	60000	31112	f	8
14	junior	100	31212	t	9
13	junior	500	31712	t	10
9	junior	5000	36712	t	11
4	junior	7000	43712	t	12
16	junior	7000	43712	f	13
1	junior	7000	43712	f	14
3	junior	10000	43712	f	15
15	junior	50001	43712	f	16