Skip to content
Dataset walkthrough

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
  1. Target outcome
  2. Grain contract
  3. Definitions
  4. Build sequence
  5. Expected result
  6. Limitations
  7. Dataset and exercise
Target outcome

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.

Grain contract

Know what one row means.

employment_records
Key · 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_headcount
Key · month_start + department_id
One row per department for the first day of each month of 2025.
department_attrition
Key · department_name
One row per department for the year 2025.
Definitions

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.
Build sequence

Work from source grain to tested output.

  1. 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.sql
    Headcount by department and month

    Generate the twelve months of 2025 with a recursive CTE and count the employment records active on each first day, by department.

    18 lines
    CREATE 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.sql
    Company headcount by month

    Check the shape of the year before dividing by it.

    6 lines
    SELECT
      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.
  2. 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.sql
    Attrition by department, 2025

    Create the department table with total and voluntary attrition rates on the same denominator.

    32 lines
    CREATE 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.sql
    Read the result

    Rank departments by attrition rate.

    9 lines
    SELECT
      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.
  3. 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.sql
    Company attrition, 2025

    Sum the numerators and the denominators before dividing.

    7 lines
    SELECT
      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.
  4. 04

    Test the grain and reconcile to the source

    Each query returns zero rows when the model is right.

    sql
    One row per department per month

    Return zero rows when the headcount view has the promised grain.

    7 lines
    SELECT
      month_start,
      department_id,
      COUNT(*) AS row_count
    FROM monthly_headcount
    GROUP BY month_start, department_id
    HAVING COUNT(*) > 1;
    sql
    Separations reconcile to employment records

    Return zero rows when the departments add up to every record that ended in 2025.

    18 lines
    WITH 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;
    sql
    Headcount rolls forward

    Return zero rows when each month's headcount equals the previous month's, plus hires in that month, minus exits.

    41 lines
    WITH 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.
Expected result

Release-backed output, not demo numbers.

v1.1.0 · verified output
walkthroughs.employee-attrition-analysis-sql.department-attrition

Twelve departments with 2025 average headcount, separations, and total and voluntary attrition rates.

Columns
department_nameavg_headcountseparationsvoluntaryattrition_pctvoluntary_attrition_pct

Checked 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.

Limitations

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.
Continue with the data

Open the matching dataset and exercise.