https://leetcode-cn.com/problems/the-number-of-seniors-and-juniors-to-join-the-company/
2004.png
with tmp as(
select a.employee_id,id,sum(salary) over(order by id) s1 from(
select employee_id,salary,row_number() over(order by salary)id from Candidates where experience='Senior')a
),
tmp1 as(
select a.employee_id,id,sum(salary) over(order by id) s1 from(
select employee_id,salary,row_number() over(order by salary)id from Candidates where experience='Junior')a
)
select 'Senior'experience,a.id accepted_candidates from tmp a left join(
select max(case when s1<=70000 then s1 else 0 end)m1 from tmp
)b on a.s1=b.m1 where b.m1 is not null
union
select 'Senior'experience,0 from(
select max(case when s1<=70000 then s1 else 0 end)m1 from tmp
)a where a.m1=0
union
select 'Junior'experience, case when max(a.id) is null then 0 else max(a.id) end id from tmp1 a ,(
select max(case when s1<=70000 then s1 else 0 end)s1 from tmp
)b where b.s1 is not null and 70000>=b.s1+a.s1
