🚀 SQL Project Series #35

HR & Employee Analytics 👥

Analyze employees, departments, salaries, attendance, promotions, and attrition using SQL to understand workforce trends and improve HR decision-making.

🎯 Business Objectives

✅ Analyze employee headcount

✅ Track employee attrition

✅ Analyze salary distribution

✅ Measure department performance

✅ Identify high-performing employees

✅ Analyze promotions and tenure

✅ Track attendance

✅ Build HR analytics dashboards

📂 Step 1: Create Database

CREATE DATABASE hr_analytics_db;
USE hr_analytics_db;

📂 Step 2: Create Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(50)
);

📂 Step 3: Create Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    job_title VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(12,2),
    employment_status VARCHAR(20),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

📂 Step 4: Create Attendance Table

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY,
    employee_id INT,
    attendance_date DATE,
    attendance_status VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

📂 Step 5: Create Promotions Table

CREATE TABLE promotions (
    promotion_id INT PRIMARY KEY,
    employee_id INT,
    promotion_date DATE,
    old_job_title VARCHAR(100),
    new_job_title VARCHAR(100),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

📂 Step 6: Insert Sample Departments

INSERT INTO departments VALUES
(101,'Data Analytics','Mumbai'),
(102,'Finance','Delhi'),
(103,'Human Resources','Bangalore'),
(104,'Technology','Pune'),
(105,'Operations','Hyderabad');

📂 Step 7: Insert Sample Employees

INSERT INTO employees VALUES
(1,'Rahul Sharma',101,'Data Analyst','2022-01-10',850000,'Active'),
(2,'Priya Verma',102,'Financial Analyst','2021-05-15',920000,'Active'),
(3,'Amit Patel',104,'Software Engineer','2020-08-20',1200000,'Active'),
(4,'Sneha Joshi',103,'HR Specialist','2023-02-10',650000,'Active'),
(5,'Rohan Gupta',105,'Operations Analyst','2022-11-05',750000,'Resigned');

📂 Step 8: Insert Sample Attendance

INSERT INTO attendance VALUES
(1,1,'2025-01-02','Present'),
(2,1,'2025-01-03','Present'),
(3,2,'2025-01-02','Present'),
(4,2,'2025-01-03','Absent'),
(5,3,'2025-01-02','Present'),
(6,4,'2025-01-02','Present'),
(7,5,'2025-01-02','Absent');

📂 Step 9: Insert Sample Promotions

INSERT INTO promotions VALUES
(1,1,'2024-06-01','Junior Data Analyst','Data Analyst'),
(2,3,'2024-01-15','Software Engineer','Senior Software Engineer');

🧠 SQL Concepts You'll Practice

✔ INNER JOIN

✔ LEFT JOIN

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ Aggregate Functions

✔ CTEs

✔ Subqueries

✔ Window Functions

✔ RANK()

✔ DENSE_RANK()

✔ LAG()

✔ Date Functions

✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Total Employees

📈 Active Employees

📈 Employee Attrition Rate

📈 Monthly Hiring Rate

📈 Monthly Attrition Rate

📈 Employee Retention Rate

📈 Average Salary

📈 Median Salary

📈 Salary by Department

📈 Salary by Job Title

📈 Gender Distribution

📈 Employee Tenure

📈 Average Tenure

📈 Promotion Rate

📈 Employees Promoted

📈 Attendance Rate

📈 Absenteeism Rate

📈 Department Headcount

📈 Department Attrition Rate

📈 Highest Paid Employees

📈 Salary Distribution

📈 Hiring Trend

📈 Employee Growth

📈 Executive HR Dashboard

💡 Example 1: Department-wise Employee Count