HR Attrition Analysis:

Project Overview

This project leverages SQL and Tableau to analyze employee attrition. The data is structured into two tables: Employees
and Attrition. SQL queries facilitate data retrieval, while Tableau is used for visualization. The project and SQL queries
were developed with the assistance of ChatGPT, an AI-powered tool, ensuring ethical AI utilization for data analysis and
insights..

1. Database Schema

To analyze attrition in the company, we modify the existing Employees table and introduce an Attrition table.

Employees Table (Existing)

This table stores employee details.

CREATE TABLE Employees (

employee_id INT PRIMARY KEY AUTO_INCREMENT,

first_name VARCHAR(50),

last_name VARCHAR(50),

department VARCHAR(50),

hire_date DATE,

salary DECIMAL(10,2)

);

Attrition Table (New)

This table stores employees who have left the company.

CREATE TABLE Attrition (

attrition_id INT PRIMARY KEY AUTO_INCREMENT,

employee_id INT,

attrition_date DATE,

reason VARCHAR(100),

FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);
2. Sample Data

Employees Data

INSERT INTO Employees (first_name, last_name, department, hire_date, salary)

VALUES

('John', 'Doe', 'HR', '2024-03-01', 50000.00),

('Jane', 'Smith', 'Finance', '2023-11-15', 70000.00),

('Sara', 'Lee', 'HR', '2021-09-10', 60000.00),

('David', 'Clark', 'Finance', '2020-05-30', 75000.00);

Attrition Data

INSERT INTO Attrition (employee_id, attrition_date, reason)

VALUES

(1, '2025-01-15', 'Resigned for a new job'),

(4, '2024-12-20', 'Retired');

3.1 Find Employees Who Left the Company

SELECT e.employee_id, e.first_name, e.last_name, e.department, a.attrition_date, a.reason

FROM Employees e

JOIN Attrition a ON e.employee_id = a.employee_id;

3.2 Attrition Rate by Department

SELECT e.department,

COUNT(a.attrition_id) AS total_attrition,

COUNT(e.employee_id) AS total_employees,

ROUND((COUNT(a.attrition_id) / COUNT(e.employee_id)) * 100, 2) AS attrition_rate

FROM Employees e

LEFT JOIN Attrition a ON e.employee_id = a.employee_id

GROUP BY e.department;

3.3 Employees With Highest Salary Who Left

SELECT e.first_name, e.last_name, e.department, e.salary, a.attrition_date, a.reason

FROM Employees e

JOIN Attrition a ON e.employee_id = a.employee_id

ORDER BY e.salary DESC

LIMIT 3;
4. SQL Testing

The SQL queries were tested and verified using DB Fiddle to ensure accuracy.
HR Attrition Analysis in Tableau
The data was imported into Tableau Public, and the following visualization was created using a Highlight Table.

Visualization Details

 Columns: Year of Hire Date

 Rows: Attrition (Yes/No), Department, Employee ID

 Color: Based on Salary

 Filters: Department

The Highlight Table effectively represents employee attrition trends along with their salary data over the years.

1. Project Overview

Employee attrition is a crucial factor that impacts an organization’s workforce planning and performance. This project
aims to analyze attrition patterns using Tableau to derive insights on employee turnover, salary impact, and department-
wise attrition trends. The project and SQL queries used for data preparation were referred from ChatGPT.

2. Data Preparation

The dataset used for this analysis includes the following key attributes:

 Employee ID – Unique identifier for each employee

 First Name & Last Name – Employee details (not used in visualization)

 Department – Department of the employee

 Hire Date – Year the employee was hired

 Salary – Employee’s salary

 Attrition (Y/N) – Indicates if an employee has left the company

 Attrition Date – Date when an employee left

 Reason – Reason for attrition (not visualized but useful for deep-dive analysis)

Data was cleaned and formatted in Tableau, ensuring correct data types and calculated fields where necessary.

Adding a Yes/No Column in Tableau

To create a calculated field that checks whether an employee exists in another sheet, follow these steps:

1. Go to your Worksheet (Sheet 1): Click on Sheet 1 at the bottom of Tableau.

2. Create a New Calculated Field:

o In the Data Pane (left side), click the small ▼ dropdown arrow next to Sheet1.

o Select "Create Calculated Field".

3. Define Your Formula:

o In the new window, give it a name like "Yes/No Column".

IF ISNULL([Employee Id (Sheet2)]) THEN "No" ELSE "Yes" END

o This checks whether an employee exists in Sheet2. If not, it returns "No"; otherwise, "Yes".

4. Click OK to save the new column.

3. Key Visualizations

3.1 Attrition Rate by Department (Bar Chart)

 Columns: Department

 Rows: Count of Employee IDs

 Color: Attrition (Y/N) – Differentiating employees who stayed vs. left

 Insight: The bar chart visually represents the attrition distribution across departments.

3.2 Attrition Trend Over Time (Line Chart)

 Columns: Hire Date (Year or Month)

 Rows: Count of Employee IDs (Only those with Attrition = "Yes")

 Visualization Type: Line Chart

 Insight: Shows attrition patterns over time, identifying peak turnover periods.

3.3 Salary Impact on Attrition (Box Plot)

 Columns: Salary

 Rows: Attrition (Y/N)

 Visualization Type: Box Plot

 Insight: Helps determine if lower salaries contribute to higher attrition rates.

3.4 Highlight Table (Current Visualization)

 Columns: Year of Hire Date

 Rows: Attrition (Y/N), Department, Employee ID

 Marks: Square

 Color Encoding: Salary (SUM)

 Insight: Shows a heatmap-style representation of salary distribution across different attrition categories and hire
years.

4. Insights & Interpretation

 High Attrition in Certain Departments: Some departments exhibit higher attrition rates than others.

 Salary Influence on Attrition: Employees with lower salaries appear more likely to leave.

Year-wise Trends: Certain years show higher turnover, possibly due to external factors like company policies, market
trends, or economic conditions.
click here to view: HR Attrition Analysis in Tableau

5. Conclusion

This HR Attrition Analysis using Tableau provides valuable insights into employee retention and salary impact. The
visualizations help HR teams identify patterns, strategize retention policies, and address key attrition factors. Further
analysis can include reasons for attrition, job roles, and performance ratings to refine insights