End-to-end HR analytics case study — Python · SQL · Power BI · Excel
A realistic business analytics case study: a company's management team wants to understand its workforce and stop the bleeding. This project takes raw employee data, cleans and validates it, answers ten concrete management questions with Python and SQL, packages the results as a four-page Power BI dashboard specification, and closes with an evidence-based recommendation report.
Every number in this repository is computed from the actual dataset — cross-validated between the Python notebooks, the SQL scripts and the dashboard mockups. Nothing is hand-typed.
Acme Corporation (a fictional case-study company; the data is the public IBM HR Analytics Employee Attrition & Performance dataset) is losing employees at 16.1% — well above the 10% benchmark management would target. Replacing an employee costs hiring fees, onboarding time and institutional knowledge. Management commissioned HR Analytics to answer:
Why are employees leaving? Which departments and which employee groups are most likely to leave? Does salary relate to attrition? Does job satisfaction relate to attrition? Which areas require management attention?
- Quantify the workforce: size, structure, demographics, compensation, tenure.
- Measure attrition overall and in every segment management cares about.
- Identify the segments, patterns and risk areas worth management time.
- Deliver the numbers as a reusable, auditable pipeline: cleaned data → notebooks → SQL → Power BI specification → business report.
| Source | IBM HR Analytics Employee Attrition & Performance (fictional data created by IBM data scientists, publicly redistributed on Kaggle: pavansubhasht/ibm-hr-analytics-attrition-dataset) |
| File | data/raw/WA_Fn-UseC_-HR-Employee-Attrition.csv |
| Size | 1,470 rows × 35 columns (1,470 employees, one row per person) |
| Key variables | EmployeeNumber (ID), demographics (Age, Gender, MaritalStatus, Education, EducationField), organization (Department, JobRole, JobLevel), compensation (MonthlyIncome, PercentSalaryHike, StockOptionLevel), career (YearsAtCompany, YearsInCurrentRole, YearsSinceLastPromotion, YearsWithCurrManager, NumCompaniesWorked), engagement (JobSatisfaction, EnvironmentSatisfaction, WorkLifeBalance, RelationshipSatisfaction, JobInvolvement), workload (OverTime, BusinessTravel, DistanceFromHome, TrainingTimesLastYear) and the outcome Attrition (Yes/No) |
Dataset limitations (also documented in the cleaning notebook and in the
report): no hire/leave dates (no time axis → no trend analysis),
PerformanceRating contains only values 3 and 4, no market salary benchmarks,
and the data is synthetic. The analysis adapts to each limitation instead of
papering over it.
| Tool | Used for |
|---|---|
| Python (pandas, NumPy, Matplotlib, Seaborn) | data cleaning, EDA, all quantitative findings |
| SQL (SQLite, engine-agnostic patterns) | 7 script files, 86 validated statements answering the same business questions in a queryable form |
| Power BI | 4-page dashboard: build specification, data model and DAX measures in powerbi/; high-fidelity mockups rendered from the actual data in images/ |
| Excel | data/processed/hr_analytics_workbook.xlsx — KPI workbook with live formulas, scorecards and the data dictionary |
| GitHub | versioned, reproducible project structure |
notebooks/01_data_cleaning.ipynb performs a full quality pass. Summary:
- Missing values: none (0 across 35 columns) — verified, nothing to impute.
- Duplicates: 0 full-row duplicates;
EmployeeNumberis a unique primary key. - Type validation: all 32 typed columns match expected types.
- Category consistency: every categorical value inside its documented domain.
- Numerical validation: all values within plausible business ranges.
- Outliers: high incomes verified as legitimate seniority (level 4–5 staff); none removed.
- Cross-field consistency: 4 tenure-ordering rules, 0 violations.
- Attrition validation: exactly {Yes, No}, complete, 16.12% rate — business-plausible.
- Encoding artifact fixed and documented: the raw file carries a UTF-8 BOM that would silently corrupt the first column name.
- Constant columns removed with documentation:
EmployeeCount,Over18,StandardHours(single unique value each). - Derived columns added:
AgeGroup,SalaryBand(terciles),TenureBand,EmploymentStatus.
Result: 1,470 of 1,470 records kept (100%). No record was ever silently
deleted; every transformation is logged in the notebook's final table and in
data/processed/data_dictionary.csv.
notebooks/02_exploratory_analysis.ipynb answers each management question.
Every chart states the business question it answers. Highlights:
| Business question | Answer (from the data) |
|---|---|
| Which departments have the highest attrition? | Sales 20.6% · HR 19.0% (n=63) · R&D 13.8% — but R&D produces 56% of leavers in absolute terms (133 of 237) |
| Which job roles? | Sales Representative 39.8%, Laboratory Technician 23.9%, HR staff 23.1%, Sales Executive 17.5%; manager/director roles 2.5–6.9% |
| Which employee groups? | Level 1 (26.3%), ages 18–25 (35.8%) and 26–35 (19.1%), first 0–2 years (29.8%), low salary tercile (26.4%), single (25.5%), overtime workers (30.5%) |
| Does salary relate to attrition? | Leavers average $4,787/mo vs stayers $6,833 (−29.9%); 79.7% of leavers earned below their department's stayer average; low quartile leaves at 29.3% |
| Does job satisfaction relate? | Yes, strongly: 22.8% at score 1 → 11.3% at score 4; 47.3% of leavers vs 37.1% of stayers report low satisfaction |
| Any surprises? | No performance gap (rating 4 leaves at 16.4% vs 16.1% for 3); no promotion-stagnation effect (13.7% vs 17.0%); salary-hike size shows no retention gradient |
Key EDA figures:
Attrition rate (%) by department × job role — the two hot cells are Sales Representative and Laboratory Technician.
Leavers are paid ~30% less on average — an association, not a proven cause (see Limitations).
Every 1–4 engagement score declines in attrition as the score rises.
First two years: 29.8% attrition; after ten years: 10.4%.
Seven scripts in sql/ (86 statements, all executed and validated against
a live SQLite build of the cleaned data — scripts/validate_sql.py asserts 35
key results match the pandas ground truth). They demonstrate CTEs, window
functions (RANK, DENSE_RANK, ROW_NUMBER, PERCENT_RANK, NTILE, LAG,
running totals), correlated subqueries, CASE, GROUP BY/HAVING, JOINs and
percentage/conditional aggregation.
| File | Business questions it answers |
|---|---|
01_data_quality.sql |
schema DDL, missing/duplicate/domain/consistency/outlier checks |
02_hr_kpis.sql |
the executive KPI strip; KPIs by department vs the 10% benchmark |
03_attrition_analysis.sql |
attrition by department, role, age, gender, salary, level, overtime, travel, tenure, satisfaction, performance, distance; composite risk segments; leaver concentration |
04_salary_analysis.sql |
leaver vs stayer salary, pay by dept/role/level, quartiles, top earners, income–tenure correlation (computed by hand), hike analysis |
05_department_analysis.sql |
workforce share, department scorecards vs benchmark, role composition, churn payroll cost, rankings |
06_employee_analysis.sql |
longest-tenured staff, stagnation candidates, at-risk high performers, most expensive departures, training/education/equity profiles |
07_advanced_analysis.sql |
transparent retention-risk score, running attrition by tenure band, within-department income deciles, "underpaid leavers" correlated subquery, LAG momentum analysis |
powerbi/ contains the complete build kit: hr_dashboard_specification.md
(page-by-page visual spec, design system and build checklist),
data_model.md (star schema: employees fact + dim_departments,
relationships, derived columns) and measures.md (DAX measures with
validated expected values). The images/ folder holds high-fidelity mockups of
all four pages, rendered with the actual data and the report's exact palette
(rebuild with scripts/render_dashboard_mockups.py).
| Page | Purpose | Contents |
|---|---|---|
| 1 · HR Executive Overview | the company in 10 seconds | 6 KPI cards (1,470 total · 1,233 active · 237 left · 16.1% rate · $6,503 avg income · 7.0 yrs tenure); employees by department; attrition by department, gender, age group, job level; overall donut |
| 2 · Attrition Analysis | who leaves, by every dimension | 5 KPI cards; attrition by job role, overtime, travel, salary level, job level, tenure, satisfaction; 6-slicer rail (Department, Job Role, Gender, Age Group, Attrition, Job Level) |
| 3 · Compensation & Performance | does pay relate to staying? | KPI cards incl. leaver-income gap (−29.9%); income by department/level/role, income distribution with mean-vs-median annotation, income quartiles, income-vs-tenure scatter colored by outcome, performance and satisfaction rates, "unusual patterns" strip |
| 4 · Workforce Analysis | structure and engagement | KPI cards; gender, marital, education, department, tenure, job role, promotion history (yrs since last promotion by level), work-life balance, training |
Design: minimal corporate layout, one navy/blue/red/amber palette, red used only for values above the 16.1% company average, no 3D, no chartjunk, KPI cards with consistent hierarchy, source line on every page.
(all figures computed from the dataset — see reports/business-insights.md)
- Attrition is 16.12% (237/1,470) — above the 10% benchmark.
- Churn concentrates in entry-level, young-tenure, low-paid employees: level 1 = 26.3%; ages 18–25 = 35.8%; first 0–2 years = 29.8% (68.4% of all leavers came from the 0–5 year bands).
- Sales Representative is the worst segment at 39.8% (33/83); Laboratory Technician 23.9% is the biggest by volume (62 leavers). Manager/director roles are stable (2.5–6.9%).
- Overtime is the strongest single association: 30.5% vs 10.4% (≈3×); 53.6% of leavers worked overtime vs 23.4% of stayers.
- Leavers are paid ~30% less ($4,787 vs $6,833 mean); 79.7% of leavers earned below their department's stayer average; low salary quartile attrition is 29.3% vs 10.3% at the top; income grows with tenure (r = 0.51).
- Engagement scores are early-warning signals: job satisfaction 1 → 22.8% attrition (score 4 → 11.3%); work-life balance 1 → 31.2%; job involvement 1 → 33.7%.
- No top-talent hemorrhage: rating-4 employees leave at 16.4%, rating 3 at 16.1%.
- Checked and absent: promotion-stagnation effect (13.7% vs 17.0%), salary-hike retention gradient (17.5/14.7/15.4%), large gender gap (17.0% vs 14.8%).
- Direct cost: leaver payroll ≈ $1.13M/month (≈$13.6M/yr) — before replacement costs, which the dataset cannot estimate.
The full evidence-linked list (15 recommendations, sequenced 90-day plan) is in
reports/business-insights.md. The four with
the strongest support:
- First-two-years retention program — structured 90/180/365-day check-ins (29.8% attrition in years 0–2 vs 10.4% after 10).
- Sales-Representative deep-dive — exit interviews + pay-position review vs market (worst segment, 39.8%, lowest-paid role at $2,626/mo).
- Early-warning score list — flag active employees with any satisfaction score of 1 for manager outreach (score-1 groups leave at 22.8–33.7%).
- Overtime workload review in the two worst roles (strongest association in the dataset, 30.5% vs 10.4%).
Deliberately not recommended: blanket raises (no gradient found by hike size) and promotion-velocity urgency (no stagnation effect found).
- Cross-sectional data with no time axis (no hire/leave dates): no trend, survival or before/after analysis is possible.
- Association, not causation. The dataset cannot distinguish "overtime causes attrition" from "people planning to leave are assigned more overtime"; pay position has no market benchmark; manager quality, business context and competitor activity are absent.
- PerformanceRating only contains 3 and 4 — underperformers are invisible.
- Small cells (HR n=63; age 56+ n=47; several job-level cells n<110) are directional only.
- Synthetic source data: realistic and internally consistent, but a case study, not a live company.
hr-analytics/
├── data/
│ ├── raw/ # original IBM HR attrition CSV (public dataset)
│ └── processed/ # cleaned_employees.csv, data_dictionary.csv,
│ # kpis.json, segment_risk_table.csv,
│ # hr_analytics_workbook.xlsx (Excel), figs
├── notebooks/
│ ├── 01_data_cleaning.ipynb # quality pass + documented transformations
│ └── 02_exploratory_analysis.ipynb # all business questions + KPIs
├── sql/ # 7 files, 86 statements, all validated
│ ├── 01_data_quality.sql ├── 02_hr_kpis.sql ├── 03_attrition_analysis.sql
│ ├── 04_salary_analysis.sql ├── 05_department_analysis.sql
│ ├── 06_employee_analysis.sql └── 07_advanced_analysis.sql
├── powerbi/
│ ├── hr_dashboard_specification.md # 4-page build spec + design system
│ ├── data_model.md # star schema, relationships, derived columns
│ └── measures.md # DAX measures with validated values
├── images/ # dashboard mockups (4) + EDA charts
├── reports/
│ └── business-insights.md # findings, risk areas, 15 recommendations
├── scripts/ # notebook builder, SQL validator, Excel + mockup renderers
├── README.md
├── requirements.txt
└── .gitignore
# 1. Install
pip install -r requirements.txt
# 2. (Re)build + execute both notebooks (outputs are committed)
python scripts/build_notebooks.py run
# 3. Validate every SQL statement + 35 key results against the cleaned data
python scripts/validate_sql.py # -> RESULT: ALL 35 CHECKS PASSED
# 4. (Re)generate the Excel workbook and the 4 dashboard mockups
python scripts/make_excel.py
python scripts/render_dashboard_mockups.pyData Cleaning · Exploratory Data Analysis · SQL (CTEs, window functions, correlated subqueries) · Python · Pandas · NumPy · Data Visualization (Matplotlib/Seaborn) · Power BI (data model, DAX, dashboard design) · HR Analytics · Business Analysis · Data Storytelling
The dataset is the IBM HR Analytics Employee Attrition & Performance
dataset — fictional employee data created by IBM data scientists and
publicly redistributed (Kaggle: pavansubhasht/ibm-hr-analytics-attrition-dataset,
CC0). It contains no real individuals. "Acme Corporation" is a fictional
company name used for the case study.