240726_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타

이번 주 내내 프로젝트 때문에 바쁘다는 핑계로
easy랑 medium만 골라 풀다 보니,
밥에 들어간 콩 골라놓은 것마냥 hard 문제만 드글드글 남았다.
더는 미룰 수 없겠다 싶어서 덤벼든 문제.

문제 링크
팀플 끝나고 풀려니 진짜 머리가 안 돌아간다...

1. 먼저 주어진 예산 70,000 중 시니어 채용부터 해야 하므로
   where 절 조건을 시니어로 준 다음
   employee_id와 salary, 그리고 salary의 누적합을 구한다.
   이 결과는 h_senior라는 CTE에 저장한다.
   실행 결과는 아래와 같다.

WITH h_senior
AS
  (
           SELECT   employee_id,
                    salary,
                    sum(salary) over ( ORDER BY salary) AS "senior_total"
           FROM     candidates
           WHERE    experience = 'Senior')

2. 시니어를 고용하고 남은 돈을 주니어 고용에 써야 하는데,
   h_senior를 만들 때와 똑같은 방식으로 CTE를 만들고
   h_junior라는 이름으로 저장한다.

h_junior
AS
  (
           SELECT   employee_id,
                    salary,
                    sum(salary) over ( ORDER BY salary) AS "junior_total"
           FROM     candidates
           WHERE    experience = 'Junior')

3. 이제 h_senior를 기준으로 최대로 고용할 수 있는 시니어의 숫자를 구한다.
   이 결과를 total_senior에 저장한다.
   이 때 주의해야 할 것이, 쓸 수 있는 예산인 senior_total이
   70,000보다 작다는 조건을 준 상태에서 count를 해야 70000 이하에서 뽑을 수 있는 최대 시니어의 수와 그 시점에서의 시니어 급여 총합을 알 수 있다는 점.

total_seniors
AS
  (
         SELECT count(employee_id) AS "num_seniors",
                max(senior_total) AS "total_senior_salary"
         FROM   h_senior
         WHERE  senior_total <= 70000 )

4. 마찬가지로 시니어를 뽑고 남은 예산을 기준으로 최대로 고용할 수 있는 주니어의 숫자를 구한다.
   이번에는 where 조건 안에 70,000에서 total_senior_salary를 빼 준 금액을 넣어야 한다는 점. 그래야 남은 예산 하에서 뽑을 수 있는 최대 주니어의 수를 알 수 있다.

total_juniors
AS
  (
         SELECT count(employee_id) AS "num_juniors"
         FROM   h_junior
         WHERE  senior_total <= 70000 -
                (
                       SELECT ifnull(total_senior_salary,0)
                       FROM   total_seniors )

5. 3과 4의 결과를 union하면 정답. 완성된 정답 쿼리는 아래와 같다.

WITH h_senior
     AS (SELECT employee_id,
                salary,
                Sum(salary)
                  OVER (
                    ORDER BY salary) AS "senior_total"
         FROM   candidates
         WHERE  experience = 'Senior'),
     h_junior
     AS (SELECT employee_id,
                salary,
                Sum(salary)
                  OVER (
                    ORDER BY salary) AS "junior_total"
         FROM   candidates
         WHERE  experience = 'Junior'),
     total_seniors
     AS (SELECT Count(employee_id) AS "num_seniors",
                Max(senior_total)  AS "total_senior_salary"
         FROM   h_senior
         WHERE  senior_total <= 70000),
     total_juniors
     AS (SELECT Count(employee_id) AS "num_juniors"
         FROM   h_junior
         WHERE  junior_total <= 70000 - (SELECT Ifnull(total_senior_salary, 0)
                                         FROM   total_seniors))

SELECT "Senior"               AS "experience",
       (SELECT num_seniors
        FROM   total_seniors) AS "accepted_candidates"
UNION ALL
SELECT "Junior"               AS "experience",
       (SELECT num_juniors
        FROM   total_juniors) AS "accepted_candidates";