Pewlett Hackard is a fictional company that is facing the problem of a large percentage of their workforce retiring at the same time. In this analysis, I queried the number of retiring employees per title using Postgres and identified employees who were eligible to participate in the mentorship program.
- There are a total of 90398 employees who were born between January 1, 1952 and December 31, 1955. They are eligible for retirement.
- Here is a table of all the retiring employees
- These employees held a total of 133776 positions over the course of their career.
- Here is a table of all the retiring employees along with their past positions
- Here is the breakdown of retirement-ready individuals by title
- There are a total of 1549 mentorship eligible workers. The criteria that makes them mentorship eligible is that they were born between January 1, 1965 and December 31, 1965.
- As workers start to retire, a total of 90398 roles will need to be filled
- There are only 1549 mentorship eligible workers to mentor the newer employees. Assuming that every new worker needs one mentor, there are not nearly enough mentors available.
Here is a summary of the number of mentorship eligible workers by department. (Comparing it to the previous breakdown of number of retiring employees by title , we can see that there are not enough mentors for each position)
However, taking a closer look at the positions, we can make a reasonable assumption that a Senior Engineer and Senior Staff member will be able to mentor newer Engineers and Staff. Here is the count of senior engineers and senior staff who were born after December 31, 1955 (not yet eligible for retirement).
From this it is clear that there are a lot more mentors available for future staff and engineers.
Here is the code I used to calculate the number of senior engineers and staff that were not yet available for retirement.
SELECT DISTINCT ON (e.emp_no) e.emp_no,
e.first_name,
e.last_name,
t.title
INTO senior_staff
FROM employees as e
inner JOIN titles as t
ON (e.emp_no = t.emp_no)
WHERE e.birth_date> '1955-12-31'
AND title = 'Senior Engineer' OR title = 'Senior Staff'
ORDER BY e.emp_no ASC, t.to_date DESC
SELECT COUNT(title), title
FROM senior_staff
GROUP BY title
ORDER BY Count(title) DESC