# $2010. 职员招聘人数 II
# https://leetcode-cn.com/problems/the-number-of-seniors-and-juniors-to-join-the-company-ii/

# SQL架构
Create table If Not Exists Candidates (employee_id int, experience ENUM('Senior', 'Junior'), salary int);
Truncate table Candidates;
insert into Candidates (employee_id, experience, salary) values ('1', 'Junior', '10000');
insert into Candidates (employee_id, experience, salary) values ('9', 'Junior', '15000');
insert into Candidates (employee_id, experience, salary) values ('2', 'Senior', '20000');
insert into Candidates (employee_id, experience, salary) values ('11', 'Senior', '16000');
insert into Candidates (employee_id, experience, salary) values ('13', 'Senior', '50000');
insert into Candidates (employee_id, experience, salary) values ('4', 'Junior', '40000');

# Write your MySQL query statement below
with seniortotal as (
    select
        employee_id,
        sum(salary) over (
            order by
                salary
        ) as totalone
    from
        Candidates
    where
        experience = 'Senior'
),
seniornumber as (
    select
        max(totalone) totals
    from
        seniortotal
    where
        totalone <= 70000
),
juniortotal as (
    select
        employee_id,
        sum(salary) over (
            order by
                salary
        ) as totaltwo
    from
        Candidates
    where
        experience = 'Junior'
)
select
    employee_id
from
    seniortotal
where
    totalone <= 70000
union
all
select
    employee_id
from
    juniortotal,
    seniornumber
where
    totaltwo < 70000 - ifnull(totals, 0);

# clean-up
drop table Candidates;