This is an independent project that I have been working on.
If interested in more of my work, please check out my portfolio at https://www.datacamp.com/portfolio/kwheeler5250.
HR Independent Project contains 2 datasets: employees and performance_schema. Since we are looking more within the realm of HR, we will be reviewing the employees dataset.
The data will be reviewed to see if there is a discrpency in gender bias within the company. This will included determining if there is gender bias within the number of employees within the company and each department. Also, we will be determining if there are pay discrepencies amongst the different genders and departments. (The data will only be containing current employees at this time.)
The employees dataset contains 6 tables.
- departments
- dept_emp
- dept_manager
- employees
- salaries
- titles
To begin let's take a look at the different types of departments that are within the company.
SELECT *
FROM departments
ORDER BY dept_no;As shown, there are 9 departments within the company.
Now let's take a look at the employees within each department.
First we will want ot know how many employees are currently working for the company. Then we will want to now how many employees work for each department. We will be wanting to see current employees which have been labeled in the to_date column as '9999-01-01T00:00:00.000Z'.
SELECT COUNT(DISTINCT emp_no) AS 'employee count'
FROM dept_emp
WHERE to_date LIKE '9999%';SELECT departments.dept_name AS 'department name',
COUNT(*) AS 'employee count'
FROM dept_emp
JOIN departments ON
dept_emp.dept_no = departments.dept_no
WHERE to_date LIKE '9999%'
GROUP BY dept_name
ORDER BY `employee count` DESC;As you can see, the top three employed departments are: Development, Production, and Sales.
Let's take a closer look at how many males and females are within each department. This is important to see the gender culture within the company.
SELECT departments.dept_name AS department_name,
COUNT(CASE WHEN employees.gender = 'M' THEN 1 END) AS 'Male employees',
COUNT(CASE WHEN employees.gender = 'F' THEN 1 END) AS 'Female employeest',
COUNT(*) AS 'Total employees in department'
FROM dept_emp
JOIN departments ON
dept_emp.dept_no = departments.dept_no
JOIN employees ON
dept_emp.emp_no = employees.emp_no
WHERE to_date LIKE '9999%'
GROUP BY department_name
ORDER BY `Total employees in department` DESC;The data indicates that there are more males working within each department. However, let's see how the numbers compare to percentages.
WITH s AS (
SELECT
departments.dept_name AS department_name,
COUNT(CASE WHEN employees.gender = 'M' THEN 1 END) AS `Male employees`,
COUNT(CASE WHEN employees.gender = 'F' THEN 1 END) AS `Female employees`,
COUNT(*) AS `Total employees in department`
FROM employees.dept_emp
JOIN employees.departments ON
dept_emp.dept_no = departments.dept_no
JOIN employees.employees ON
dept_emp.emp_no = employees.emp_no
WHERE dept_emp.to_date LIKE '9999%'
GROUP BY departments.dept_name
)
SELECT
department_name,
(`Male employees` / `Total employees in department`) * 100 AS `Percent Male Employees`,
(`Female employees` / `Total employees in department`) * 100 AS `Percent Female Employees`,
(`Male employees` / `Total employees in department`) * 100 -
(`Female employees` / `Total employees in department`) * 100 AS `Difference in Percentages`,
`Total employees in department`
FROM s
ORDER BY `Total employees in department` DESC;The data shows that each department has about 20% difference between male versus female employees with each department being male dominant. The percentage of males versus females for each departmert can be rounded to 60% males and 40% females. Due to this, a visual of the percentage of male versus female employees per department is not warranted.
Based on these findings, let's start by determining what id the highest and lowest salaries offered within the company and what the average salary is.
SELECT MAX(salary),
MIN(salary),
ROUND(AVG(salary),0) AS 'avg_salary'
FROM salaries
WHERE to_date LIKE '9999%';Now that we can see what the maximum, minimum, and average salaries are. Let's see how the salaries differ by gender.
Due to there being a difference in the number of employees based on gender, it is important to see if there is a difference in pay for each gender as this could e considered gender discrimination with pay gaps.
SELECT e.gender,
MAX(s.salary) AS 'max_salary',
MIN(s.salary) AS 'min_salary',
ROUND(AVG(s.salary),0) AS 'avg_salary'
FROM salaries AS s
JOIN employees AS e
ON s.emp_no = e.emp_no
WHERE to_date LIKE '9999%'
GROUP BY e.gender;