Interview Question Bank · Point-in-time headcount · COUNT FILTER · average denominator
9. Annual attrition rate by department
HardHRQuestion #189
Concept: Point-in-time headcount · COUNT FILTER · average denominatorshow
Attrition is leavers in a period divided by the average headcount over it, usually (headcount at the start + headcount at the end) / 2. Headcount on a day d counts everyone hired by d whose end date is NULL or on or after d, and COUNT(*) FILTER computes several of these per group in one pass. Leavers are counted by termination date, even people who joined and left within the year and appear in neither snapshot. Divide by 2.0, not 2, or the average is truncated.
Example (different data):
SELECT store,
COUNT(*) FILTER (WHERE cancelled_on >= DATE '2024-01-01' AND cancelled_on < DATE '2025-01-01') AS cancels,
COUNT(*) FILTER (WHERE joined_on <= DATE '2024-01-01'
AND (cancelled_on IS NULL OR cancelled_on >= DATE '2024-01-01')) AS members_jan1
FROM memberships
GROUP BY store;Your task
Quillmoor's board wants 2024 attrition by department. termination_date is an employee's last day, so someone whose last day was 2024-12-31 left in 2024 and is off the books on 2025-01-01.
For each department, return department, leavers (termination_date in 2024), avg_headcount ((headcount on 2024-01-01 + headcount on 2025-01-01) / 2, rounded to 1 decimal) and attrition_pct (100 * leavers / avg_headcount, rounded to 1 decimal). Someone is in the headcount on day d if hire_date <= d and termination_date is NULL or on or after d. Sort by attrition_pct descending, then department.
Starting Postgres in your browser…
Checking your access…