HR-Analytics-SQL-Project.sql

-- ===========================================================
--                  HR ANALYTICS SQL PROJECT
-- ===========================================================
-- Project: HR Employee Data Analysis
-- Tool Used: MySQL Workbench
-- Objective:
-- Analyze HR employee data to answer business questions
-- related to workforce distribution, performance,
-- hiring trends, employee demographics, and turnover.
-- ===========================================================



-- ===========================================================
-- STEP 1 : VIEW RAW DATA
-- ===========================================================

SELECT *
FROM employee_data;



-- ===========================================================
-- STEP 2 : CREATE A CLEAN COPY OF THE DATASET
-- ===========================================================

CREATE TABLE employee_data_clean AS
SELECT *
FROM employee_data;



-- ===========================================================
-- STEP 3 : DATA CLEANING
-- ===========================================================

-- Check total records

SELECT COUNT(*) AS Total_Employees
FROM employee_data_clean;


-- Check duplicate Employee IDs

SELECT EmpID,
COUNT(*) AS Duplicate_Count
FROM employee_data_clean
GROUP BY EmpID
HAVING COUNT(*) > 1;


-- Check Employee Status values

SELECT DISTINCT EmployeeStatus
FROM employee_data_clean;


-- ===========================================================
-- Convert DOB from VARCHAR to DATE
-- ===========================================================

ALTER TABLE employee_data_clean
ADD COLUMN DOB_New DATE;

UPDATE employee_data_clean
SET DOB_New = STR_TO_DATE(DOB,'%d-%m-%Y');

ALTER TABLE employee_data_clean
DROP COLUMN DOB;

ALTER TABLE employee_data_clean
RENAME COLUMN DOB_New TO DOB;



-- ===========================================================
-- Convert StartDate from VARCHAR to DATE
-- ===========================================================

ALTER TABLE employee_data_clean
ADD COLUMN StartDate_New DATE;

UPDATE employee_data_clean
SET StartDate_New = STR_TO_DATE(StartDate,'%d-%b-%y');

ALTER TABLE employee_data_clean
DROP COLUMN StartDate;

ALTER TABLE employee_data_clean
RENAME COLUMN StartDate_New TO StartDate;



-- ===========================================================
-- Convert ExitDate from VARCHAR to DATE
-- ===========================================================

ALTER TABLE employee_data_clean
ADD COLUMN ExitDate_New DATE;

UPDATE employee_data_clean
SET ExitDate_New =
CASE
WHEN ExitDate IS NULL OR TRIM(ExitDate)='' THEN NULL
ELSE STR_TO_DATE(ExitDate,'%d-%b-%y')
END;

ALTER TABLE employee_data_clean
DROP COLUMN ExitDate;

ALTER TABLE employee_data_clean
RENAME COLUMN ExitDate_New TO ExitDate;



-- ===========================================================
-- Verify Data Types
-- ===========================================================

DESCRIBE employee_data_clean;



-- ===========================================================
-- STEP 4 : EXPLORATORY DATA ANALYSIS (EDA)
-- ===========================================================

-- Employee count by Department

SELECT DepartmentType,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY DepartmentType
ORDER BY Employees DESC;



-- Employee count by Business Unit

SELECT BusinessUnit,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY BusinessUnit
ORDER BY Employees DESC;



-- Gender Distribution

SELECT GenderCode,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY GenderCode;



-- Employee Distribution by State

SELECT State,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY State
ORDER BY Employees DESC;



-- Active vs Terminated Employees

SELECT EmployeeStatus,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY EmployeeStatus;



-- Marital Status Distribution

SELECT MaritalDesc,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY MaritalDesc;



-- ===========================================================
-- STEP 5 : BUSINESS ANALYSIS
-- ===========================================================

-- 1. Average Employee Rating

SELECT AVG(`Current Employee Rating`) AS Average_Rating
FROM employee_data_clean;



-- 2. Top 10 Highest Rated Employees

SELECT FirstName,
LastName,
Title,
`Current Employee Rating`
FROM employee_data_clean
ORDER BY `Current Employee Rating` DESC
LIMIT 10;



-- 3. Average Rating by Department

SELECT DepartmentType,
AVG(`Current Employee Rating`) AS Avg_Rating
FROM employee_data_clean
GROUP BY DepartmentType
ORDER BY Avg_Rating DESC;



-- 4. Performance Score Distribution

SELECT `Performance Score`,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY `Performance Score`;



-- 5. Employee Classification

SELECT EmployeeClassificationType,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY EmployeeClassificationType;



-- 6. Employees by Job Title

SELECT Title,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY Title
ORDER BY Employees DESC;



-- 7. Employees by Division

SELECT Division,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY Division
ORDER BY Employees DESC;



-- 8. Employees by Employment Type

SELECT EmployeeType,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY EmployeeType;



-- 9. Top 10 Supervisors Managing Largest Teams

SELECT Supervisor,
COUNT(*) AS Team_Size
FROM employee_data_clean
GROUP BY Supervisor
ORDER BY Team_Size DESC
LIMIT 10;



-- 10. Employees Exceeding Performance Expectations

SELECT FirstName,
LastName,
DepartmentType,
`Performance Score`
FROM employee_data_clean
WHERE `Performance Score`='Exceeds';



-- 11. Employees Needing Improvement

SELECT FirstName,
LastName,
DepartmentType,
`Performance Score`
FROM employee_data_clean
WHERE `Performance Score`='Needs Improvement';



-- 12. Average Rating by Gender

SELECT GenderCode,
AVG(`Current Employee Rating`) AS Avg_Rating
FROM employee_data_clean
GROUP BY GenderCode;



-- 13. Business Units with Highest Ratings

SELECT BusinessUnit,
AVG(`Current Employee Rating`) AS Avg_Rating
FROM employee_data_clean
GROUP BY BusinessUnit
ORDER BY Avg_Rating DESC;



-- 14. Terminated Employees by Department

SELECT DepartmentType,
COUNT(*) AS Terminated_Employees
FROM employee_data_clean
WHERE EmployeeStatus='Terminated'
GROUP BY DepartmentType;



-- 15. Employees by Pay Zone

SELECT PayZone,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY PayZone
ORDER BY Employees DESC;



-- 16. Top 5 States by Employee Count

SELECT State,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY State
ORDER BY Employees DESC
LIMIT 5;



-- 17. Highest Rated Department

SELECT DepartmentType,
AVG(`Current Employee Rating`) AS Avg_Rating
FROM employee_data_clean
GROUP BY DepartmentType
ORDER BY Avg_Rating DESC
LIMIT 1;



-- 18. Supervisor Performance Analysis

SELECT Supervisor,
AVG(`Current Employee Rating`) AS Avg_Rating,
COUNT(*) AS Team_Size
FROM employee_data_clean
GROUP BY Supervisor
ORDER BY Avg_Rating DESC;



-- 19. Employee Distribution by Age Group

SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR,DOB,CURDATE()) BETWEEN 20 AND 29 THEN '20-29'
WHEN TIMESTAMPDIFF(YEAR,DOB,CURDATE()) BETWEEN 30 AND 39 THEN '30-39'
WHEN TIMESTAMPDIFF(YEAR,DOB,CURDATE()) BETWEEN 40 AND 49 THEN '40-49'
WHEN TIMESTAMPDIFF(YEAR,DOB,CURDATE()) BETWEEN 50 AND 59 THEN '50-59'
ELSE '60+'
END AS Age_Group,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY Age_Group
ORDER BY Age_Group;



-- 20. Monthly Hiring Trend

SELECT
YEAR(StartDate) AS Year,
MONTH(StartDate) AS Month,
COUNT(*) AS Hires
FROM employee_data_clean
GROUP BY YEAR(StartDate),MONTH(StartDate)
ORDER BY Year,Month;



-- 21. Employees Below Average Rating

SELECT
FirstName,
LastName,
`Current Employee Rating`
FROM employee_data_clean
WHERE `Current Employee Rating`
<
(
SELECT AVG(`Current Employee Rating`)
FROM employee_data_clean
);



-- 22. Department Performance Summary

SELECT
DepartmentType,
COUNT(*) AS Employees,
AVG(`Current Employee Rating`) AS Avg_Rating,
SUM(EmployeeStatus='Active') AS Active_Employees,
SUM(EmployeeStatus='Terminated') AS Terminated_Employees
FROM employee_data_clean
GROUP BY DepartmentType;



-- 23. Performance Distribution by Gender

SELECT
GenderCode,
`Performance Score`,
COUNT(*) AS Employees
FROM employee_data_clean
GROUP BY GenderCode,`Performance Score`
ORDER BY GenderCode;



-- 24. Average Employee Tenure (Years)

SELECT
ROUND(AVG(DATEDIFF(ExitDate,StartDate))/365,2)
AS Avg_Tenure_Years
FROM employee_data_clean
WHERE ExitDate IS NOT NULL;



-- ===========================================================
--                  END OF PROJECT
-- ===========================================================