SQL Analysis — HR Analytics¶

Setup

Load hr_clean.csv into a table employees. Target column: Attrition_flag (1 = left, 0 = stayed).

Q1 — Overall Attrition Rate and Cost¶

SELECT
    COUNT(*)                            AS total_employees,
    SUM(Attrition_flag)                 AS left_count,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct,
    -- Estimated replacement cost: assume 75% of annual salary
    ROUND(SUM(CASE WHEN Attrition_flag = 1
                   THEN MonthlyIncome * 12 * 0.75 ELSE 0 END), 0) AS est_attrition_cost
FROM employees;

Sample output:

total_employees	left_count	attrition_rate_pct	est_attrition_cost
1470	237	16.1	8,294,500

Q2 — Attrition by Department¶

SELECT
    Department,
    COUNT(*)                            AS headcount,
    SUM(Attrition_flag)                 AS left_count,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct
FROM employees
GROUP BY Department
ORDER BY attrition_rate_pct DESC;

Sample output:

Department	headcount	left_count	attrition_rate_pct
Sales	446	92	20.6
Human Resources	63	12	19.0
Research & Development	961	133	13.8

Q3 — Attrition by Job Role¶

SELECT
    JobRole,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct,
    ROUND(AVG(MonthlyIncome), 0)        AS avg_monthly_income
FROM employees
GROUP BY JobRole
HAVING COUNT(*) >= 30
ORDER BY attrition_rate_pct DESC;

Sample output (top of list):

JobRole	headcount	attrition_rate_pct	avg_monthly_income
Sales Representative	83	39.8	2626
Laboratory Technician	259	23.9	3237
Human Resources	52	23.1	4236

Sales Representatives are leaving in droves

Nearly 40% of Sales Reps leave — the highest of any role. They're also the lowest paid (£2,626/month). The combination of low pay and likely high pressure is toxic for retention.

Q4 — The Overtime Effect¶

SELECT
    OverTime,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct
FROM employees
GROUP BY OverTime;

Sample output:

OverTime	headcount	attrition_rate_pct
Yes	416	30.5
No	1054	10.4

Q5 — Attrition by Tenure Bucket¶

SELECT
    tenure_bucket,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct
FROM employees
GROUP BY tenure_bucket
ORDER BY
    CASE tenure_bucket
        WHEN '0-2 yrs' THEN 1 WHEN '3-5 yrs' THEN 2
        WHEN '6-10 yrs' THEN 3 ELSE 4
    END;

Sample output:

tenure_bucket	headcount	attrition_rate_pct
0-2 yrs	412	27.7
3-5 yrs	388	14.4
6-10 yrs	392	11.7
10+ yrs	278	8.6

The first two years are the danger zone — over a quarter of new hires leave.

Q6 — Income Band and Attrition¶

SELECT
    income_band,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct,
    ROUND(AVG(MonthlyIncome), 0)        AS avg_income
FROM employees
GROUP BY income_band
ORDER BY avg_income;

Q7 — Work-Life Balance and Attrition¶

SELECT
    WorkLifeBalance_label,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct
FROM employees
GROUP BY WorkLifeBalance_label, WorkLifeBalance
ORDER BY WorkLifeBalance;

Q8 — Combined Risk Factors (The High-Risk Profile)¶

-- Employees matching the high-risk profile
SELECT
    Department,
    JobRole,
    COUNT(*)                            AS at_risk_count,
    ROUND(AVG(Attrition_flag) * 100, 1) AS actual_attrition_pct
FROM employees
WHERE OverTime = 'Yes'
  AND YearsAtCompany <= 3
  AND income_band IN ('Q1 (Lowest)', 'Q2')
GROUP BY Department, JobRole
ORDER BY at_risk_count DESC;

This identifies the intersection of risk factors — overtime + new + low pay — and shows the actual attrition rate for that profile (typically 50%+).

Q9 — Promotion Stagnation¶

SELECT
    overdue_promotion,
    COUNT(*)                            AS headcount,
    ROUND(AVG(Attrition_flag) * 100, 1) AS attrition_rate_pct,
    ROUND(AVG(YearsSinceLastPromotion), 1) AS avg_years_since_promo
FROM employees
GROUP BY overdue_promotion;

← Data Cleaning · Next: Dashboard Design →