[ 알고리즘 ] LeetCode 2004. The Number of Seniors and Juniors to Join the Company

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

본 글은 [ LeetCode ] 2004. The Number of Seniors and Juniors to Join the Company를 풀고 작성한 글입니다.

문제

테이블

Table: Candidates

+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int  |
| experience  | enum |
| salary      | int  |
+-------------+------+
employee_id is the primary key column for this table.
experience is an enum with one of the values ('Senior', 'Junior').
Each row of this table indicates the id of a candidate, their monthly salary, and their experience.

요구사항

A company wants to hire new employees. The budget of the company for the salaries is $70000 . The company's criteria for hiring are:

1. Hiring the largest number of seniors.
   After hiring the maximum number of seniors, use the remaining budget to hire the largest number of juniors.
2. Write an SQL query to find the number of seniors and juniors hired under the mentioned criteria.

Return the result table in any order.

The query result format is in the following example.

풀이

접근법

각각의 누적합을 구해서 Seniors 테이블을 만들고 Seniors 테이블에서 주어진 예산 70000 을 차감하여 남은 예산보다 작은 누적합을 가진 Juniors 테이블을 만들면 된다.

나의 풀이

접근법을 토대로 문제를 풀면 아래와 같다. 이때 Seniors 테이블에서 조건을 만족하는 값이 하나도 존재하지 않을 수 있어 MAX 함수의 반환값으로 NULL 을 받을 수도 있다. 따라서 IFNULL 함수를 사용하여 NULL 값을 처리해줘야 한다.

WITH Cumulatives (employee_id, experience, cumulative_salaries) AS (
    SELECT 
        employee_id,
        experience,
        SUM(salary) OVER(PARTITION BY experience ORDER BY salary ASC) AS cumulative_salaries
    FROM Candidates
), Seniors (experience, accepted_candidates, remained_budget) AS (
    SELECT
        'Senior' AS experience,
        COUNT(employee_id) AS accepted_candidates,
        70000 - IFNULL(MAX(cumulative_salaries), 0) AS remained_budget
    FROM Cumulatives
    WHERE (
        experience = 'Senior'
        AND
        cumulative_salaries <= 70000
    )
), Juniors (experience, accepted_candidates) AS (
    SELECT
        'Junior' AS experience,
        COUNT(employee_id) AS accepted_candidates
    FROM Cumulatives
    WHERE (
        experience = 'Junior'
        AND
        cumulative_salaries <= (SELECT remained_budget FROM Seniors)
    )
)

SELECT
    experience,
    accepted_candidates
FROM Seniors
UNION ALL
SELECT
    experience,
    accepted_candidates
FROM Juniors;