Monthly Attrition Rate in 2024

Medium
239.

Monthly Attrition Rate in 2024

MediumDatesSubqueries

MySQL

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

ColumnType
emp_idint
joined_ondate
exit_datedate

Primary key: emp_id.

exit_date is the last working day of someone who has left, or NULL for a current employee.

Example

The rows must come back in the order shown.

Employee

emp_idjoined_onexit_date
12022-04-11NULL
22023-01-092024-02-29
32023-06-012024-03-01
42024-01-01NULL
52024-02-122024-02-28
62021-09-202024-06-14
72023-11-062023-12-29
82024-03-04NULL

Output

monthexitsopening_headcountattrition_pct
2024-022540
2024-031425
2024-061425
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
joined_ondate
exit_datedate

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_idjoined_onexit_date
12022-04-11NULL
22023-01-092024-02-29
32023-06-012024-03-01
42024-01-01NULL
52024-02-122024-02-28
62021-09-202024-06-14
72023-11-062023-12-29
82024-03-04NULL

Output

monthexitsopening_headcountattrition_pct
2024-022540
2024-031425
2024-061425

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

Another 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 →