90- and 180-Day Retention of 2024 Hires by Quarter
The leadership review on 2025-03-31 asks how well 2024's hires stayed.
- Difficulty: Hard
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #246
Problem statement
The leadership review on 2025-03-31 asks how well 2024's hires stayed. A hire is retained at N days when they have not left, or their exit_date is at least N days after joined_on. Every 2024 hire is old enough for the 90-day check; for the 180-day check, count only hires who joined at least 180 days before 2025-03-31 (on or before 2024-10-02).
Return all four quarters of 2024 — even a quarter with no hires — with cohort (2024-Q1 … 2024-Q4), hires, retained_90_pct (retained at 90 ÷ hires × 100) and retained_180_pct (retained at 180 ÷ hires old enough × 100), each rounded to 2 decimals and NULL when its denominator is 0. Order by cohort.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| joined_on | date |
| exit_date | date |
| source | enum(campus, referral, job_board, agency) |
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 | source |
|---|---|---|---|
| 1 | 2024-01-15 | NULL | campus |
| 2 | 2024-02-05 | 2024-04-30 | job_board |
| 3 | 2024-03-11 | 2024-06-09 | agency |
| 4 | 2024-07-01 | 2024-11-15 | referral |
| 5 | 2024-08-19 | NULL | campus |
| 6 | 2024-10-02 | 2025-03-31 | job_board |
| 7 | 2024-11-04 | 2025-01-10 | agency |
| 8 | 2023-12-18 | 2024-02-01 | agency |
Output
| cohort | hires | retained_90_pct | retained_180_pct |
|---|---|---|---|
| 2024-Q1 | 3 | 66.67 | 33.33 |
| 2024-Q2 | 0 | NULL | NULL |
| 2024-Q3 | 2 | 100 | 50 |
| 2024-Q4 | 2 | 50 | 100 |
How to solve 90- and 180-Day Retention of 2024 Hires by Quarter
A cohort report has to show every cohort, including an empty one, so the quarters come from a table we make ourselves — four literal rows joined with UNION ALL, or a recursive CTE that steps three months at a time — and the hires are LEFT JOINed onto it by YEAR(joined_on) = 2024 AND QUARTER(joined_on) = q. A quarter with no hires keeps one NULL row; COUNT(e.emp_id) gives it 0.
Retention at N days is a condition per hire: no exit, or an exit at least N days after joining — DATEDIFF(exit_date, joined_on) >= N, which is the same as exit_date >= DATE_ADD(joined_on, INTERVAL N DAY). Someone whose last day is exactly day 90 counts as retained, as the statement says.
The 180-day rate is the trap: hires from the last months of 2024 have not had 180 days yet, and counting them as "not retained" would make the newest cohort look terrible. So its denominator is only the hires who joined on or before 2024-10-02 (180 days before the review), and its numerator is the retained among those. Both percentages divide by NULLIF(…, 0) so an empty denominator gives NULL, and are rounded to 2 decimals. One pass over the employees.
Reference solution (MySQL)
WITH quarters AS (
SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
)
SELECT CONCAT('2024-Q', q.q) AS cohort,
COUNT(e.emp_id) AS hires,
ROUND(SUM(CASE WHEN e.exit_date IS NULL OR DATEDIFF(e.exit_date, e.joined_on) >= 90 THEN 1 ELSE 0 END) * 100
/ NULLIF(COUNT(e.emp_id), 0), 2) AS retained_90_pct,
ROUND(SUM(CASE WHEN e.joined_on <= '2024-10-02' AND (e.exit_date IS NULL OR DATEDIFF(e.exit_date, e.joined_on) >= 180) THEN 1 ELSE 0 END) * 100
/ NULLIF(SUM(CASE WHEN e.joined_on <= '2024-10-02' THEN 1 ELSE 0 END), 0), 2) AS retained_180_pct
FROM quarters q
LEFT JOIN Employee e ON YEAR(e.joined_on) = 2024 AND QUARTER(e.joined_on) = q.q
GROUP BY q.q
ORDER BY cohortAnother way
WITH RECURSIVE quarters AS (
SELECT 1 AS q, CAST('2024-01-01' AS DATE) AS q_start
UNION ALL
SELECT q + 1, DATE_ADD(q_start, INTERVAL 3 MONTH) FROM quarters WHERE q < 4
), hires AS (
SELECT q.q,
CASE WHEN e.exit_date IS NULL OR e.exit_date >= DATE_ADD(e.joined_on, INTERVAL 90 DAY) THEN 1 ELSE 0 END AS kept90,
CASE WHEN e.joined_on <= DATE_SUB('2025-03-31', INTERVAL 180 DAY) THEN 1 ELSE 0 END AS eligible180,
CASE WHEN e.joined_on <= DATE_SUB('2025-03-31', INTERVAL 180 DAY)
AND (e.exit_date IS NULL OR e.exit_date >= DATE_ADD(e.joined_on, INTERVAL 180 DAY)) THEN 1 ELSE 0 END AS kept180
FROM quarters q
JOIN Employee e ON e.joined_on >= q.q_start AND e.joined_on < DATE_ADD(q.q_start, INTERVAL 3 MONTH)
)
SELECT CONCAT('2024-Q', q.q) AS cohort, COUNT(h.q) AS hires,
CASE WHEN COUNT(h.q) = 0 THEN NULL ELSE ROUND(SUM(h.kept90) * 100 / COUNT(h.q), 2) END AS retained_90_pct,
CASE WHEN IFNULL(SUM(h.eligible180), 0) = 0 THEN NULL ELSE ROUND(SUM(h.kept180) * 100 / SUM(h.eligible180), 2) END AS retained_180_pct
FROM quarters q
LEFT JOIN hires h ON h.q = q.q
GROUP BY q.q
ORDER BY cohort← Median CTC for Each Job Level · Leave Ledger Entries That Took the Balance Negative →