90- and 180-Day Retention of 2024 Hires by Quarter

Hard
246.

90- and 180-Day Retention of 2024 Hires by Quarter

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

ColumnTypeValues
emp_idint
joined_ondate
exit_datedate
sourceenumcampus, 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.

Example

The rows must come back in the order shown.

Employee

emp_idjoined_onexit_datesource
12024-01-15NULLcampus
22024-02-052024-04-30job_board
32024-03-112024-06-09agency
42024-07-012024-11-15referral
52024-08-19NULLcampus
62024-10-022025-03-31job_board
72024-11-042025-01-10agency
82023-12-182024-02-01agency

Output

cohorthiresretained_90_pctretained_180_pct
2024-Q1366.6733.33
2024-Q20NULLNULL
2024-Q3210050
2024-Q4250100
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.

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

ColumnType
emp_idint
joined_ondate
exit_datedate
sourceenum(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_idjoined_onexit_datesource
12024-01-15NULLcampus
22024-02-052024-04-30job_board
32024-03-112024-06-09agency
42024-07-012024-11-15referral
52024-08-19NULLcampus
62024-10-022025-03-31job_board
72024-11-042025-01-10agency
82023-12-182024-02-01agency

Output

cohorthiresretained_90_pctretained_180_pct
2024-Q1366.6733.33
2024-Q20NULLNULL
2024-Q3210050
2024-Q4250100

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 cohort

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