candidates2hire.sql

With amounts as (
    select t1.id, t1.position, t1.salary, sum(t2.salary) as running_salary
    from candidates t1 join candidates t2 on t1.id >= t2.id
    and t1.position = t2.position
    where t1.salary <= 50000
    group by t1.id, t1.position, t1.salary
    order by t1.id
),
snrs as (
    select id, position, salary, running_salary from amounts
    where position = 'senior' and running_salary <= 50000
),
jnrs as (
    select id, position, salary, running_salary from amounts
    where position = 'junior' 
    and running_salary <= 50000 - (case when (select count(*) from snrs = 0 then 0 
                                      else select max(running_salary) from snrs) end)
)
select (
    select count(*) from jnrs
) as juniors,
count(*) as seniors from snrs