HIRE JUNIOR & SENIOR DEVELOPER WITHIN BUDGET SQL_INTREVIEW

 --#15day SQL CHALLENGE
--DAY 4
--We are having a budget of 70000 ,have to hire senior & junior developers within 70000.
--But first we have to hire senior & then to hire junior with the remaining budget(Amount is remain after hiring senior developer )

--So write a sql query to find the employee (junior & senior developer ) those got hired within the above condition.

SELECT REPLICATE('*',400) as 'Sample_Table_Created'

create table candidates (

emp_id int,

experience varchar(20),

salary int

);

delete from candidates;

insert into candidates values

(1,'Junior',10000),(2,'Junior',15000),(3,'Junior',40000),(4,'Senior',16000),(5,'Senior',20000),(6,'Senior',50000);

SELECT * FROM candidates

---------------------------------------START SOLUTION------------------------

SELECT REPLICATE('*',400) Solution

WITH TOTAL_SALARY AS
(
SELECT *,SUM (salary) OVER (PARTITION BY experience order by salary asc  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 'Running_Sum_Salary'
FROM candidates
),
SENIORS AS 
( SELECT * FROM TOTAL_SALARY  WHERE TOTAL_SALARY.Running_Sum_Salary <70000 AND TOTAL_SALARY.experience='Senior'
)
SELECT * FROM TOTAL_SALARY  WHERE   TOTAL_SALARY.experience='Junior' AND TOTAL_SALARY.Running_Sum_Salary < 70000 -  (SELECT SUM (SENIORS.salary) FROM SENIORS)
UNION ALL
SELECT * FROM SENIORS

SELECT REPLICATE('*',400) END_OF_Solution

------------------------------------------------END OF SOLUTION-------------------------------