HR Analysis and Data Validation – SQL

USE HR_Analytics_DB
GO
 
-- DATA CLEANUP & TRANSFORMATION
 
-- Count the dataset to ensure all rows were imported
SELECT COUNT(*) FROM employees;
 
-- inspect the dataset
SELECT * FROM employees;
 
-- Dropped all columns where the employee number is null
DELETE FROM employees
WHERE Employee_Number IS NULL;
 
-- Changed employee_number from varchar to integer with a not null constraint
ALTER TABLE employees
ALTER COLUMN Employee_Number  INT NOT NULL; 
 
-- Added a primary key constraint
ALTER TABLE employees
ADD CONSTRAINT PK_Employees_Employee_Number PRIMARY KEY (Employee_Number);
 
-- Primary key constraint returned error due to duplicate employee number
-- Retrieve duplicate employee numbers
SELECT * FROM 
(
SELECT Employee_Number,
ROW_NUMBER() OVER(PARTITION BY Employee_Number ORDER BY Employee_Number) AS rn
FROM employees
)X
WHERE X.rn > 1;
 
-- To cross-check the duplicate rows
SELECT *
FROM employees
WHERE Employee_Number = 1204033041;
 
-- Duplicate employee numbers have different employee names, this was spotted in the HR Analysis & Report - Power BI version
-- In a real scenario we would have to forward this back to HR for clarification, 
-- but for now, we would delete the duplicate record.
 
-- USING CTE
WITH cte AS 
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Employee_Number ORDER BY Employee_Number) AS rn
FROM employees
)
DELETE FROM cte
WHERE rn > 1; 
 
-- We can now add a primary key constraint
ALTER TABLE employees
ADD CONSTRAINT PK_Employees_Employee_Number PRIMARY KEY (Employee_Number);

Business Questions
1. Is there gender equality in the company’s employment?
2. Is there diversity in the workplace?
3. What demographics have the highest impact on low pay rates?
4. Identify the trend and pattern in the hiring process of the organization.

— The total number of employees is 300 compared to 301 in the Power BI report. We dropped the duplicate employee number because we needed to assign the primary key constraint.

1. Is there gender equality in the company’s employment?
The question relates to all employees recruited and not just current working employees.

SELECT
*,
(cast(no_of_employees as float)/cast(total_employees as float))* 100 as pct
FROM
(
SELECT
sex,
count(1) no_of_employees,
(select count(1) from employees) as total_employees
FROM employees
GROUP BY sex
)x;
-- We could achieve this with a window function to get better query performance
SELECT
sex,
count(1) as no_of_employees,
(cast(count(1) as float)/cast(sum(count(1)) over () as float))*100 as pct_of_total
FROM employees
GROUP BY sex;

2. Is there diversity in the workplace?
The question refers to current employees.

SELECT
racedesc as race,
count(1) as no_of_employees,
round(cast(count(1) as float)/cast(sum(count(1)) over () as float)*100, 2) as pct_of_total
FROM employees
WHERE Employment_Status not in ('Terminated for Cause','Voluntarily Terminated')
GROUP BY racedesc
ORDER BY no_of_employees DESC;

3. What demographics are more impacted by low pay rates?
We would find out which age groups, race and gender have the lowest pay rate

SELECT
racedesc as race,
sex,
age_group,
COUNT(1) as no_of_employees,
ROUND(AVG(pay_rate),2) as avg_payrate
FROM
(
SELECT
racedesc,
sex,
age,
pay_rate,
CASE WHEN age >= 18 AND age <= 25 THEN '18-25' WHEN age >= 26 AND age <= 35 THEN '26-35' WHEN age >= 36 AND age <= 45 THEN '36-45' WHEN age >= 46 AND age <= 55 THEN '46-55' WHEN age >= 56 THEN '56+'
END as age_group
FROM employees
)x
GROUP BY racedesc,
sex,
age_group
ORDER BY avg_payrate ASC;

4. Identify the trend and pattern in the hiring process of the organization

WITH all_hiring
AS (
SELECT
FORMAT(date_of_hire,'yyyy-MM') as hire_date,
COUNT(1) as no_of_employees
FROM employees
GROUP BY FORMAT(date_of_hire,'yyyy-MM')
),
female_hiring AS
(
SELECT
FORMAT(date_of_hire,'yyyy-MM') as hire_date,
COUNT(1) as female_hires
FROM employees
WHERE lower(sex) = 'female'
GROUP BY FORMAT(date_of_hire,'yyyy-MM')
),
male_hiring AS
(
SELECT
FORMAT(date_of_hire,'yyyy-MM') as hire_date,
COUNT(1) as male_hires
FROM employees
WHERE lower(sex) = 'male'
GROUP BY FORMAT(date_of_hire,'yyyy-MM')
)
SELECT
ah.hire_date,
no_of_employees,
coalesce(female_hires,0) as female_hires,
coalesce(male_hires,0) as male_hires
FROM all_hiring ah
LEFT JOIN female_hiring fh ON ah.hire_date = fh.hire_date
LEFT JOIN male_hiring mh ON ah.hire_date = mh.hire_date
ORDER BY ah.hire_date;

This shows that there is a higher preference for female employees than male employees. There are only very few occurrences where male candidates hired exceeded females.