Employee Attrition Analysis in SQL: Annual Rate by Department
Calculate an annual employee attrition rate in SQL using average headcount, split out voluntary exits, and compare departments. Runs on a free HR dataset.
On this page · 7 sections
- Release
- v1.1.0
- Tool
- SQLite SQL
- Time
- 30 minutes
- Deliverables
- 4 build steps, 3 modeled assets
What this build produces.
A 12-row table of 2025 attrition by department with average headcount, separations, and total and voluntary attrition rates, plus the company-wide rate.
Know what one row means.
- employment_recordsKey · employment_record_id
- One row per employment period. In v1.1.0 every employee has exactly one, with a start date and an end date that is empty while the person is employed.
- monthly_headcountKey · month_start + department_id
- One row per department for the first day of each month of 2025.
- department_attritionKey · department_name
- One row per department for the year 2025.
Fix the meaning before the code.
- Headcount on a date
- Employment records that started before the date and had not ended before it.
- Average headcount
- The mean of the twelve first-of-month headcounts in the year. An average is used because headcount changes during the year, and people who join in June were not there to leave in March.
- Separation
- An employment record whose ended_at date falls inside the year, whatever the reason.
- Annual attrition rate
- Separations in the year divided by average headcount for the year, as a percentage.
- Voluntary attrition rate
- Separations with termination_type 'voluntary' divided by the same average headcount. Resignations are the part an employer can usually influence, so they are reported on their own.
- Lifetime attrition share
- Terminated records divided by all records ever created. It has no time period, so it is not a rate and cannot be compared between a new department and an old one.
Work from source grain to tested output.
- 01
Count headcount on the first day of each month
The denominator has to describe who was employed during the year. Build it from twelve dated headcounts, not from the number of people in the table today.
sql · attrition.sqlHeadcount by department and monthGenerate the twelve months of 2025 with a recursive CTE and count the employment records active on each first day, by department.
18 linesCREATE TEMP VIEW monthly_headcount AS WITH RECURSIVE months(month_start) AS ( SELECT '2025-01-01' UNION ALL SELECT date(month_start, '+1 month') FROM months WHERE month_start < '2025-12-01' ) SELECT m.month_start, er.department_id, COUNT(*) AS headcount FROM months AS m INNER JOIN employment_records AS er ON er.started_at < m.month_start AND (er.ended_at = '' OR er.ended_at >= m.month_start) GROUP BY m.month_start, er.department_id;sql · attrition.sqlCompany headcount by monthCheck the shape of the year before dividing by it.
6 linesSELECT month_start, SUM(headcount) AS headcount FROM monthly_headcount GROUP BY month_start ORDER BY month_start;Verification- On v1.1.0 the view has 144 rows: 12 departments for 12 months.
- Company headcount is 4,118 on 1 January 2025, peaks at 5,271 on 1 October, and is 5,081 on 1 December.
- A current employee has an empty string in ended_at, not NULL, so the join tests for '' explicitly.
- 02
Divide separations by average headcount
Average the monthly headcounts and count the year's separations separately, each at one row per department, then join the two. Joining the raw tables first would repeat every separation twelve times.
sql · attrition.sqlAttrition by department, 2025Create the department table with total and voluntary attrition rates on the same denominator.
32 linesCREATE TEMP VIEW department_attrition AS WITH average_headcount AS ( SELECT department_id, AVG(headcount) AS avg_headcount FROM monthly_headcount GROUP BY department_id ), separations AS ( SELECT department_id, COUNT(*) AS separations, SUM(CASE WHEN termination_type = 'voluntary' THEN 1 ELSE 0 END) AS voluntary FROM employment_records WHERE ended_at >= '2025-01-01' AND ended_at < '2026-01-01' GROUP BY department_id ) SELECT d.department_name, h.avg_headcount, COALESCE(s.separations, 0) AS separations, COALESCE(s.voluntary, 0) AS voluntary, ROUND(100.0 * COALESCE(s.separations, 0) / h.avg_headcount, 1) AS attrition_pct, ROUND(100.0 * COALESCE(s.voluntary, 0) / h.avg_headcount, 1) AS voluntary_attrition_pct FROM average_headcount AS h INNER JOIN departments AS d ON h.department_id = d.department_id LEFT JOIN separations AS s ON h.department_id = s.department_id;sql · attrition.sqlRead the resultRank departments by attrition rate.
9 linesSELECT department_name, ROUND(avg_headcount, 1) AS avg_headcount, separations, voluntary, attrition_pct, voluntary_attrition_pct FROM department_attrition ORDER BY attrition_pct DESC, department_name;Verification- The result has 12 rows, one per department.
- Finance is highest at 17.9% (71 separations on an average headcount of 396.4) and Legal & Compliance is lowest at 13.0% (52 on 399.3).
- The LEFT JOIN keeps a department that had no separations, with a rate of 0. Every department has some in this release, but an inner join would silently drop one that did not.
- 03
Calculate the company-wide rate
The company rate is total separations over total average headcount. It is not the average of the twelve department percentages, which would weight a small department the same as a large one.
sql · attrition.sqlCompany attrition, 2025Sum the numerators and the denominators before dividing.
7 linesSELECT ROUND(SUM(avg_headcount), 1) AS avg_headcount, SUM(separations) AS separations, SUM(voluntary) AS voluntary, ROUND(100.0 * SUM(separations) / SUM(avg_headcount), 1) AS attrition_pct, ROUND(100.0 * SUM(voluntary) / SUM(avg_headcount), 1) AS voluntary_attrition_pct FROM department_attrition;Verification- Company-wide, 750 separations on an average headcount of 4,788.4 give a 2025 attrition rate of 15.7%.
- 503 of those were voluntary, a voluntary attrition rate of 10.5%.
- The lifetime attrition share for the same data is 17.1% (1,025 of 6,000 records). It mixes three years of exits with everyone ever hired and should not be reported as an annual rate.
- The choice of denominator moves the answer. Using the average of headcount on 1 January 2025 and 1 January 2026 (4,118 and 4,975) gives 16.5%. State which convention a report uses.
- 04
Test the grain and reconcile to the source
Each query returns zero rows when the model is right.
sqlOne row per department per monthReturn zero rows when the headcount view has the promised grain.
7 linesSELECT month_start, department_id, COUNT(*) AS row_count FROM monthly_headcount GROUP BY month_start, department_id HAVING COUNT(*) > 1;sqlSeparations reconcile to employment recordsReturn zero rows when the departments add up to every record that ended in 2025.
18 linesWITH model AS ( SELECT SUM(separations) AS separations FROM department_attrition ), source AS ( SELECT COUNT(*) AS separations FROM employment_records WHERE ended_at >= '2025-01-01' AND ended_at < '2026-01-01' ) SELECT model.separations AS model_separations, source.separations AS source_separations FROM model CROSS JOIN source WHERE model.separations <> source.separations;sqlHeadcount rolls forwardReturn zero rows when each month's headcount equals the previous month's, plus hires in that month, minus exits.
41 linesWITH company AS ( SELECT month_start, SUM(headcount) AS headcount FROM monthly_headcount GROUP BY month_start ), hires AS ( SELECT substr(started_at, 1, 7) AS month, COUNT(*) AS hires FROM employment_records GROUP BY substr(started_at, 1, 7) ), exits AS ( SELECT substr(ended_at, 1, 7) AS month, COUNT(*) AS exits FROM employment_records WHERE ended_at <> '' GROUP BY substr(ended_at, 1, 7) ), roll_forward AS ( SELECT c.month_start, c.headcount + COALESCE(h.hires, 0) - COALESCE(x.exits, 0) AS expected_next, LEAD(c.headcount) OVER (ORDER BY c.month_start) AS actual_next FROM company AS c LEFT JOIN hires AS h ON substr(c.month_start, 1, 7) = h.month LEFT JOIN exits AS x ON substr(c.month_start, 1, 7) = x.month ) SELECT * FROM roll_forward WHERE actual_next IS NOT NULL AND expected_next <> actual_next;Verification- All three queries return zero rows on v1.1.0.
- The reconciliation compares 750 separations in the model with 750 in employment_records.
- The roll-forward test fails if the headcount rule and the separation rule disagree about the first or last day of a month, which is where off-by-one errors in headcount usually hide.
Release-backed output, not demo numbers.
walkthroughs.employee-attrition-analysis-sql.department-attritionTwelve departments with 2025 average headcount, separations, and total and voluntary attrition rates.
department_nameavg_headcountseparationsvoluntaryattrition_pctvoluntary_attrition_pctChecked on 2026-11-21 against workforce-employee-attrition-v1.1.0.sqlite as published. Company total: average headcount 4,788.4, 750 separations, 503 voluntary, 15.7% attrition, 10.5% voluntary attrition.
What this result does not claim.
- The data is synthetic and is not a workforce benchmark.
- The generator gives every employee the same chance of leaving, whatever their department, role, or commute. The gap between 13.0% and 17.9% across departments is chance on 52 to 71 exits each, so it should not be presented as a department problem.
- termination_type is assigned independently of age band, so retirements appear in every age band. Use the type to practice the split, not to study retirement.
- Each employee has one employment period in v1.1.0. There are no transfers, promotions, or rehires, so a person belongs to one department for their whole tenure.
- Nobody leaves in their first 90 days, and hiring stops at the end of September 2025, which is why headcount falls in the last quarter.
- Associations between employee attributes and exits in this dataset are descriptive and do not establish causation.
- The temporary views need a SQLite connection that allows CREATE, such as the sqlite3 shell or DB Browser for SQLite on the downloaded file. The browser sandbox on this site runs SELECT statements only.
