[StrataScratch - SQL] (Medium) Risky Projects

문제

다음은 주어진 프로젝트 및 직원 데이터에 대한 설명입니다. 각 프로젝트는 이름, 예산, 특정 기간을 가지고 있으며, 각 직원은 연봉을 가지고 있고 특정 기간 동안 하나 이상의 프로젝트에 배정될 수 있습니다. 작업은 어떤 프로젝트가 예산을 초과했는지 식별하는 것입니다. 프로젝트가 예산을 초과했다고 간주되는 경우는, 해당 프로젝트에 배정된 모든 직원의 비례 비용 합이 프로젝트 예산을 초과하는 경우입니다.

이를 해결하려면, 각 직원의 연봉을 해당 프로젝트에서 실제로 작업한 기간에 맞게 비례 배분해야 합니다. 예를 들어, 직원이 6개월 동안 작업한 경우, 연봉의 절반만 해당 프로젝트에 할당되어야 합니다. 각 프로젝트에 배정된 모든 직원의 비례 급여 금액을 합산하고, 그 총액을 프로젝트 예산과 비교합니다.

출력에는 예산을 초과한 프로젝트 목록이 포함되어야 하며, 각 항목에는 프로젝트 이름, 예산, 해당 프로젝트의 총 비례 직원 비용이 포함됩니다. 총 비용은 가장 가까운 달러로 반올림하여 출력해야 합니다. 모든 연도는 365일로 가정하고, 윤년은 무시합니다.

 

테이블설명

 

예상결과

 

✏️ 풀이

SELECT title, budget, CEILING(SUM(salary / 365 * TIMESTAMPDIFF(DAY, start_date, end_date))) AS prorated_employee_expense
FROM linkedin_emp_projects AS ep
JOIN linkedin_projects AS p ON ep.project_id = p.id
JOIN linkedin_employees AS e ON ep.emp_id = e.id
GROUP BY 1, 2
HAVING budget < prorated_employee_expense;

1. 예산, start/end 기간, 연봉 정보를 가지고 데이터 추출을 하기 위해 3개의 테이블 조인

2. 프로젝트 진행 기간만큼만 연봉에서 계산하기 위해 365일로 나누고 start, end 기간의 일 수를 구해 곱한다

3. 예산을 초과한 데이터를 찾기 위해 HAVING을 써서 GROUP BY에 대한 필터 조건을 건다

 

예상결과에 Project1 다음이 바로 11이어서 2는 안나오는 줄 알고 채점을 안하고 계속 문제를 다시 풀었었는데

Project2도 나오는게 맞았다...