Pewlett-Packard (PH) is a large firm that currently employs 300,024 people. Many employees are approaching retirement age, which will result in a significant number of job openings in the organization. The management at PH wants to be prepared for this development, known as the "Silver Tsunami" by designating some of the retirees as mentors to train the future generation of workers who would run Pewlett Hackard's operations and day-to-day activities.
The goal of this study is to construct a list of employees approaching retirement age using Pewlett-Packard (PH) employee data. Employees born between January 1, 1952, and December 31, 1955 are included on the list. The company's management must be aware of the overall number of retirees, as well as the department in which they work and their title.
The database provided by PH consisted of 6 CSV files:
EmployeesDepartmentsDat_empDept_managerTitlesSalaries
These CSV files had to be imported into database tables and linked between them using SQL statements. The final schema showing the links between the tables is shown in the image below.
The SQL code used to create the tables can be found in this link.
The deliverables for this analysis are:
2️⃣.1️⃣ The Number of Retiring Employees by Title. retirement_titles.csv
The names of those born between 1952 and 1955 are presented in the table in Image #2 below. The number of entries in this table is calculated using SELECT count(emp_no) FROM retirement_titles;, which gives a total of 133,776 lines. Because the data comprises more than 130,000 lines, it requires additional filtration before it can be useful to PH management. Some entries are duplicated since some employees' titles have changed throughout their careers as they moved from one position to another.
Code used to create this table
SELECT e.emp_no,
e.first_name,
e.last_name,
ti.title,
ti.from_date,
ti.to_date
INTO retirement_titles
FROM employees as e
INNER JOIN titles as ti
ON (e.emp_no = ti.emp_no)
WHERE (e.birth_date BETWEEN '1952-01-01' AND '1955-12-31')
ORDER BY e.emp_no;
2️⃣.2️⃣ A table with Unique Titles. unique_titles.csv
Image 3 shows the table with the employees born between 1953 and 1955 but without the duplicates. The number of entries in the table is now 90,398. The query used to count them is:
SELECT Count(DISTINCT emp_no)
FROM retirement_titles;
Since 90,398 is still a large number to make any decision, we need to refine the data even further.
2️⃣.3️⃣ A table with Retiring Titles. retiring_titles.csv
The final filtering step is to present the data of the 90,398 employees grouped by title. Below is the code used to generate the table shown in Image 4
SELECT COUNT(emp_no), title
FROM unique_titles
GROUP BY title
ORDER BY COUNT(emp_no) DESC;
Looking at the numbers in the table we see that 90,398 / 300,024 = 30.1% of the employees of the company are about to retire. This a tremendous challenge for any company and in particular for Pewlett Hackard because the majority of the retirees are engineers with many years of experience. So, even if the company manages to hire substitute engineers in a short amount of time, the new hires will require a lot of training before they can contribute effectively to the operations of PH.
The total number of engineers in the Image 4 table is 45,397 or 50.2% of the retirees.
The Senior Staff group is also another important part of the labor force that is retiring. There is a total of 28,254 or 31.3% of the group.
2️⃣.4️⃣ The Employees Eligible for the Mentorship Program whose birth dates are between January 1, 1965 and December 31, 1965. mentorship_eligibility.csv
Given the vast number of employees departing and the skillsets they possess, the firm decides to select a group of employees born in 1965 (i.e., they are 10 years younger than the retirees) and turn them into trainers for new hires and coworkers who will be promoted to fill future openings.
Below is the image showing the list of employees who fall under the indicated criteria. The code used to generate the table is
SELECT DISTINCT ON (e.emp_no)
e.emp_no,
e.first_name,
e.last_name,
e.birth_date,
de.from_date,
de.to_date,
ti.title
INTO mentorship_eligibilty
FROM employees as e
INNER JOIN dept_emp as de
ON (e.emp_no = de.emp_no)
INNER JOIN titles as ti
ON (e.emp_no = ti.emp_no)
WHERE (e.birth_date BETWEEN '1965-01-01' AND '1965-12-31')
AND (de.to_date = '9999-01-01')
ORDER BY e.emp_no, ti.from_date DESC;
Using the code below, we obtain another table showing the number of potential trainers per title:
SELECT COUNT(emp_no), title
FROM public.mentorship_eligibilty
GROUP BY title
ORDER BY COUNT(emp_no) DESC;
The total number of personnel that have been pre-selected for the post of trainer is 1,549. When we split this amount by the total number of employees departing, we get a group of 90,398 total retirees / 1,549 potential trainers = 59 employees per trainer. This figure appears to be rather high, implying that if the training program is to be effective, the corporation will need to increase the number of potential trainers by a factor of at least ten.
.png)
30.1%, or 90,398 employees, will need to be replaced. The problem is made more difficult by the fact that there are 45,397 engineers in the organization, accounting for 50.2% of all retirees. Another key segment of the labor force that is retiring is the Senior Staff. There are a total of 28,254 departing workers, accounting for 31.3% of the total. A more detailed analysis is in Section 2.
Below is a table detailing the composition of the group
- Are there enough qualified, retirement-ready employees in the departments to mentor the next generation of Pewlett Hackard employees?
1,549 people. There are 90,398 people who are retiring. Dividing 90,398 / 1,549 people who want to be trainers = 59 people per trainer. In order for the training program to be effective, the company will need to increase at least tenfold the number of people who could become trainers, so that each trainer has only 6 trainees under supervision. More details in section 2.4.
Module 7: Employee Database with SQL, https://courses.bootcampspot.com/courses/1145/pages/7-dot-0-1-exploring-databases-with-sql, ©️ 202-2021 Trilogy Education Services, Web 01 Apr 2022.