Pewlett-Hackard-Analysis

Overview

Helping the company management to prepare for the upcoming "silver tsunami". Find out the retiring employees and retreive their infomation for the manager. Also, retrieve information of employees who are eligible to participate in a mentorship program.

Results

After retrieved the number of employees by their most recent job title who are about to retire, we got the Retiring Titles table as below:

• There are 90398 employees are eligible to retire.
• Most of the eligible retirees are Senior Engineer and Senior Staff, which are 29414 and 28254 of them respectively. Total number of potential retirees in these 2 titles accounts for more than half of the whole retiring employee group. However, there are only 2 employees titled as Manager are going to be retire, which stands in sharp contrast to all other titles.
• But, these potential retirees includes employees had left. Excluding the left employees we got: total 72458 current employees are potential to be retired. Script and table are as following:

SELECT COUNT(emp_no),
title
INTO current_retire_titles
FROM retirement_titles
WHERE (to_date = '9999-01-01')
GROUP BY title
ORDER BY COUNT(emp_no) DESC;

After identified the mentorship eligibility, we got a table that holds the employees who are eligible to participate in a mentorship program. Grouping them by job titles we can get:

• There are total 1549 current employees are eligible to participate mentorship program.

Summary

How many roles will need to be filled as the "silver tsunami" begins to make an impact?

Running additional queries below:

SELECT COUNT(emp_no),
title
INTO current_retire_titles
FROM retirement_titles
WHERE (to_date = '9999-01-01')
GROUP BY title
ORDER BY COUNT(emp_no) DESC;

SELECT SUM(COUNT) FROM current_retire_titles;

We get the updated Retiring Titles Table for only current employees, current_retire_titles.

And, sum of the count:

Therefore, there are 72458 roles will need to be filled as the "silver tsunami" begins to make an impact.

Are there enough qualified, retirement-ready employees in the departments to mentor the next generation of Pewlett Hackard employees?

Running additional queries below:

-- mentorship eligibility by department
SELECT COUNT(e.emp_no),
d.dept_name
INTO mentorship_by_dept
FROM dept_emp AS de
	INNER JOIN employees AS e
		ON (de.emp_no = e.emp_no)
	INNER JOIN departments AS d
		ON (de.dept_no = d.dept_no)
WHERE (de.to_date = '9999-01-01')
AND (e.birth_date BETWEEN '1965-01-01' AND '1965-12-31')
GROUP BY dept_name
ORDER BY COUNT DESC;

-- potential retirees by department
SELECT COUNT(de.emp_no),
d.dept_name
INTO retiring_dept
FROM dept_emp AS de
	INNER JOIN employees AS e
		ON (de.emp_no = e.emp_no)
	INNER JOIN departments AS d
		ON (de.dept_no = d.dept_no)
WHERE (de.to_date = '9999-01-01')
AND (e.birth_date BETWEEN '1952-01-01' AND '1955-12-31')
GROUP BY dept_name
ORDER BY COUNT DESC;

We get the number of eligible mentorship employees by each departments versus retiring employees count per departments:

Then, perform the aggregate and get the Roles per Mentor ratio as following:

SELECT CAST(CAST(rd.COUNT AS FLOAT)/CAST(md.COUNT AS FLOAT) AS DECIMAL(4,2)) AS "Roles per Mentor",
md.dept_name AS "Department"
FROM mentorship_by_dept AS md
INNER JOIN retiring_dept AS rd
ON (md.dept_name = rd.dept_name);

Base on the ratio we get from above, each eligible retirement_ready employee will mentor about 40 to 60 next generation of Pewlett Hackard employees per department.