message: Update the-number-of-seniors-and-juniors-to-join-the-company.sql
diff: WITH senior_cte AS
  (
  - SELECT 'Senior' AS experience,
  - IFNULL(MAX(salary_accu), 0) AS salary,
  - IFNULL(MAX(rn), 0) AS rn
  + SELECT employee_id,
  + salary_accu
  FROM
  (
  - SELECT SUM(salary) OVER(ORDER BY salary, employee_id) AS salary_accu,
  - ROW_NUMBER() OVER(ORDER BY salary, employee_id) rn
  + SELECT employee_id,
  + SUM(salary) OVER(ORDER BY salary, employee_id) AS salary_accu
  FROM candidates
  WHERE experience = 'Senior'
  ) a
  WHERE salary_accu <= 70000
  ),
  +senior_max_cte AS
  +(
  + SELECT IFNULL(MAX(salary_accu), 0) AS salary
  + FROM senior_cte
  +),
  junior_cte AS
  (
  - SELECT 'Junior' AS experience,
  - IFNULL(MAX(salary_accu), 0) AS salary,
  - IFNULL(MAX(rn), 0) AS rn
  + SELECT employee_id
  FROM
  (
  - SELECT SUM(salary) OVER(ORDER BY salary, employee_id) AS salary_accu,
  - ROW_NUMBER() OVER(ORDER BY salary, employee_id) AS rn
  + SELECT employee_id,
  + SUM(salary) OVER(ORDER BY salary, employee_id) AS salary_accu
  FROM candidates
  WHERE experience = 'Junior'
  ) a
  - WHERE salary_accu + (SELECT salary FROM senior_cte) <= 70000
  + WHERE salary_accu + (SELECT salary FROM senior_max_cte) <= 70000
  )
  -SELECT experience, rn AS accepted_candidates
  -FROM
  -(
  - SELECT experience, rn FROM senior_cte
  +SELECT 'Senior' AS experience, COUNT(1) AS accepted_candidates FROM senior_cte
  UNION ALL
  - SELECT experience, rn FROM junior_cte
  -) a;
  +SELECT 'Junior' AS experience, COUNT(1) AS accepted_candidates FROM junior_cte;
  