스터디 노트 40일차

과제 6 실습

-- Q1
-- 1. Department , JobLevel , JobRole 별 재직자 대상
-- 2. 평균 월급 , 평균 Salary 인상률 ( 이 인상률을 연 인상률로 가정)
-- 3. 인당 월급과 연 인상률을 활용해서 내년 추정 월급 산출
-- 4. 내년 연봉 추정 (12개월)

SELECT Department ,JobRole , hn.JobLevel
,avg(hn.MonthlyIncome) avg_MI
,avg(hn.PercentSalaryHike) avg_PSH
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) next_year_MI
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) * 12 next_year_Income
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
WHERE 1=1
AND Attrition = 'No'
GROUP BY 1,2,3
ORDER BY 1,2,3
;

-- Q2
-- Q1의 결과값을 기준으로 , 내년 추정 연봉이 Top 10인 사람들을 필터링

SELECT Department ,JobRole , hn.JobLevel
,avg(hn.MonthlyIncome) avg_MI
,avg(hn.PercentSalaryHike) avg_PSH
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) next_year_MI
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) * 12 next_year_Income
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
WHERE 1=1
AND Attrition = 'No'
GROUP BY 1,2,3
ORDER BY next_year_Income DESC
LIMIT 10
;

-- Q3
-- Q1의 결과값을 기준으로 , 내년 추정 연봉이 20만 이상 30만 이하인 그룹을 필터링
-- 추정 연봉을 기준으로 내림차순

SELECT Department ,JobRole , hn.JobLevel
,avg(hn.MonthlyIncome) avg_MI
,avg(hn.PercentSalaryHike) avg_PSH
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) next_year_MI
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) * 12 next_year_Income
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
WHERE 1=1
AND Attrition = 'No'
GROUP BY 1,2,3
HAVING next_year_Income BETWEEN 200000 AND 300000
ORDER BY next_year_Income DESC
;

-- Q4
-- 당해년도 승진자를 대상으로 내년 추정 연봉이 20만 이상인 그룹을 필터링하여 / 추정 연봉을 기준으로 내림차순

SELECT Department ,JobRole , hn.JobLevel
,avg(hn.MonthlyIncome) avg_MI
,avg(hn.PercentSalaryHike) avg_PSH
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) next_year_MI
,avg(hn.MonthlyIncome (1 + hn.PercentSalaryHike/100)) * 12 next_year_Income
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
WHERE 1=1
AND Attrition = 'No'
AND YearsSinceLastPromotion = 0
GROUP BY 1,2,3
HAVING next_year_Income >= 200000
ORDER BY next_year_Income DESC
;