365 Days of Daily Coding: Day 96

I was quite occupied with work today nontheless, I managed my productivity although I would have liked it to be much better. I am resuming my Korean classes from June which I am quite excited about. But first, I must recap everything I learned from the beginner and intermediate class I have already taken.

This is the second codility challenge I had taken for a job interview. I had to find the number of potential junior and senior that I could accomodate with a budget of EUR 50,000. Each junior and senior had their expected salary and the priority always need to be given to the seniors first and then what remaining budget check if any of the juniors could be hired.

When I was taking the test, I was using CASE instead of the running sum to determine the number of junior and senior I could take but I find that I lack the understanding of iteration and also a bit on the order of operation in SQL.

My solution:

WITH temp_1 AS (
Select 'junior' AS position, 10000 AS Salary FROM Dual Union All
Select 'junior' AS position, 10000 AS Salary FROM Dual Union All
Select 'senior' AS position, 30000 AS Salary FROM Dual Union All
Select 'senior' AS position, 25000 AS Salary FROM Dual Union All
Select 'junior' AS position, 15000 AS Salary FROM Dual  
), temp_2 AS
(SELECT position, Salary, SUM(Salary) OVER (PARTITION BY position ORDER BY Salary ASC) AS Running_Sum
    FROM temp_1)

SELECT *
FROM 
    (SELECT position
    FROM (
        SELECT position, Salary, SUM(Salary) OVER (ORDER BY position DESC, Salary ASC) AS run_2
        FROM (
            SELECT position, Salary, SUM(Salary) OVER (PARTITION BY position ORDER BY Salary ASC) AS Run_Sum
            FROM (
                SELECT position, Salary 
                    FROM temp_2
                    WHERE Running_Sum < 50000
                    ORDER BY position DESC)))
    WHERE run_2 <= 50000)
PIVOT (COUNT(*) FOR Position IN ('junior', 'senior'));