Monthly Attrition Rate in 2024 — SQL Dates Problem
Attrition for a month is the number of employees whose last working day (exit_date) fell in that month, divided by the opening headcount — the employees on…
- Difficulty: Medium
- Topics: Dates, Subqueries
- Dialect: MySQL
- Problem: #239
Problem statement
Attrition for a month is the number of employees whose last working day (exit_date) fell in that month, divided by the opening headcount — the employees on the rolls on the 1st of the month (joined on or before the 1st, and either still employed or with an exit_date on or after the 1st).
For every month of 2024 with at least one exit, return month (as YYYY-MM), exits, opening_headcount and attrition_pct = exits × 100 / opening headcount, rounded to 2 decimals (NULL if the opening headcount is 0). Order by month.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| joined_on | date |
| exit_date | date |
Primary key: emp_id.
exit_date is the last working day of someone who has left, or NULL for a current employee.
Examples
Example 1
Employee
| emp_id | joined_on | exit_date |
|---|---|---|
| 1 | 2022-04-11 | NULL |
| 2 | 2023-01-09 | 2024-02-29 |
| 3 | 2023-06-01 | 2024-03-01 |
| 4 | 2024-01-01 | NULL |
| 5 | 2024-02-12 | 2024-02-28 |
| 6 | 2021-09-20 | 2024-06-14 |
| 7 | 2023-11-06 | 2023-12-29 |
| 8 | 2024-03-04 | NULL |
Output
| month | exits | opening_headcount | attrition_pct |
|---|---|---|---|
| 2024-02 | 2 | 5 | 40 |
| 2024-03 | 1 | 4 | 25 |
| 2024-06 | 1 | 4 | 25 |
How to solve Monthly Attrition Rate in 2024
Two different populations meet in each row: the exits of a month (grouped by the month of exit_date) and the opening headcount on its 1st (a count over all employees). They do not come from the same rows, so compute them separately.
The derived table m groups 2024's exits by month, which also yields only the months the statement wants — those with at least one exit — and carries the month's first day as text (YYYY-MM-01). For each of those rows, a correlated scalar subquery counts employees who had joined by that day and had not yet left: exit_date IS NULL OR exit_date >= first_day. The >= matters: a person whose last working day is the 1st was still on the rolls that morning, and an employee who exits during the month is counted in both the exits and the opening headcount.
The rate is exits × 100 / headcount rounded to 2 decimals, with NULLIF so a month whose leavers all joined during it gives NULL instead of failing. The alternative cross-joins the exit months with all employees and counts both things with conditional sums. Both are months × employees, tiny here.
Reference solution (MySQL)
SELECT m.month, m.exits,
(SELECT COUNT(*) FROM Employee e
WHERE e.joined_on <= m.first_day AND (e.exit_date IS NULL OR e.exit_date >= m.first_day)) AS opening_headcount,
ROUND(m.exits * 100 / NULLIF((SELECT COUNT(*) FROM Employee e
WHERE e.joined_on <= m.first_day AND (e.exit_date IS NULL OR e.exit_date >= m.first_day)), 0), 2) AS attrition_pct
FROM (
SELECT month, CONCAT(month, '-01') AS first_day, COUNT(*) AS exits
FROM (SELECT DATE_FORMAT(exit_date, '%Y-%m') AS month FROM Employee
WHERE exit_date >= '2024-01-01' AND exit_date < '2025-01-01') x
GROUP BY month
) m
ORDER BY m.monthAnother way
WITH months AS (
SELECT DISTINCT DATE_FORMAT(exit_date, '%Y-%m') AS month FROM Employee WHERE YEAR(exit_date) = 2024
), stats AS (
SELECT m.month,
SUM(CASE WHEN DATE_FORMAT(e.exit_date, '%Y-%m') = m.month THEN 1 ELSE 0 END) AS exits,
SUM(CASE WHEN e.joined_on <= CONCAT(m.month, '-01') AND (e.exit_date IS NULL OR e.exit_date >= CONCAT(m.month, '-01')) THEN 1 ELSE 0 END) AS opening_headcount
FROM months m CROSS JOIN Employee e
GROUP BY m.month
)
SELECT month, exits, opening_headcount,
CASE WHEN opening_headcount = 0 THEN NULL ELSE ROUND(exits * 100 / opening_headcount, 2) END AS attrition_pct
FROM stats
ORDER BY month← Appraisal Ratings Falling Two Cycles Running · Staffing Pool of Multi-Skill Experts →