High Performers Due for a Promotion Review — SQL Problem
On 2025-07-01 HR runs its promotion review. A current employee (exit_date NULL) is due when they have spent at least three years in their current role —…
- Difficulty: Medium
- Topics: Subqueries, Dates
- Dialect: MySQL
- Problem: #236
Problem statement
On 2025-07-01 HR runs its promotion review. A current employee (exit_date NULL) is due when they have spent at least three years in their current role — their latest promotion, or their joining date if they were never promoted, is on or before 2022-07-01 — and their FY2024-25 rating is 4 or 5. A missing or NULL rating is not 4 or 5.
Return emp_id, full_name and in_role_since (the latest promotion date, or the joining date) for every employee who is due. Order by in_role_since, then emp_id.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| joined_on | date |
| exit_date | date |
Primary key: emp_id.
exit_date is NULL for a current employee.
Table: Promotion
| Column | Type |
|---|---|
| emp_id | int |
| promoted_on | date |
| new_grade | varchar |
Primary key: emp_id, promoted_on.
One row per promotion an employee received.
Table: Appraisal
| Column | Type |
|---|---|
| emp_id | int |
| cycle | varchar |
| rating | int |
Primary key: emp_id, cycle.
rating is 1–5, or NULL until the manager submits it.
Examples
Example 1
Employee
| emp_id | full_name | joined_on | exit_date |
|---|---|---|---|
| 1 | Aarav Sharma | 2018-03-12 | NULL |
| 2 | Diya Iyer | 2021-06-01 | NULL |
| 3 | Ishaan Gupta | 2019-08-19 | NULL |
| 4 | Kavya Nair | 2020-01-06 | 2025-03-31 |
| 5 | Rohan Das | 2022-07-01 | NULL |
| 6 | Meera Rao | 2017-11-20 | NULL |
Promotion
| emp_id | promoted_on | new_grade |
|---|---|---|
| 1 | 2021-04-01 | L3 |
| 3 | 2021-04-01 | L3 |
| 3 | 2023-04-01 | L4 |
| 6 | 2020-04-01 | L4 |
Appraisal
| emp_id | cycle | rating |
|---|---|---|
| 1 | FY2024-25 | 4 |
| 2 | FY2024-25 | 5 |
| 3 | FY2024-25 | 5 |
| 4 | FY2024-25 | 5 |
| 5 | FY2024-25 | 4 |
| 6 | FY2024-25 | NULL |
| 6 | FY2023-24 | 5 |
Output
| emp_id | full_name | in_role_since |
|---|---|---|
| 1 | Aarav Sharma | 2021-04-01 |
| 2 | Diya Iyer | 2021-06-01 |
| 5 | Rohan Das | 2022-07-01 |
How to solve High Performers Due for a Promotion Review
Each employee's time in role starts at their latest promotion, or at joining when there is none. A correlated MAX(promoted_on) returns NULL for someone never promoted, and COALESCE(…, joined_on) falls back to the joining date. Computing this in a derived table lets the outer query filter on it by name.
The cut-off is three years before the review: DATE_SUB('2025-07-01', INTERVAL 3 YEAR) = 2022-07-01, and "at least three years" means <= — someone who joined or was promoted on exactly 2022-07-01 qualifies. The rating check is an EXISTS on this cycle's row with rating >= 4; a NULL rating fails the comparison and a missing row fails the EXISTS, so neither counts. A strong rating from an earlier cycle is irrelevant.
The alternative does the same with joins: an inner join to the qualifying appraisal, a LEFT JOIN to promotions, GROUP BY per employee and the date test in HAVING. The appraisal key (emp_id, cycle) guarantees the inner join cannot multiply rows. Both are a couple of indexed lookups per employee.
Reference solution (MySQL)
SELECT emp_id, full_name, in_role_since
FROM (
SELECT e.emp_id, e.full_name,
COALESCE((SELECT MAX(p.promoted_on) FROM Promotion p WHERE p.emp_id = e.emp_id), e.joined_on) AS in_role_since
FROM Employee e
WHERE e.exit_date IS NULL
AND EXISTS (SELECT 1 FROM Appraisal a WHERE a.emp_id = e.emp_id AND a.cycle = 'FY2024-25' AND a.rating >= 4)
) t
WHERE in_role_since <= '2022-07-01'
ORDER BY in_role_since, emp_idAnother way
SELECT e.emp_id, e.full_name, COALESCE(MAX(p.promoted_on), e.joined_on) AS in_role_since
FROM Employee e
JOIN Appraisal a ON a.emp_id = e.emp_id AND a.cycle = 'FY2024-25' AND a.rating IN (4, 5)
LEFT JOIN Promotion p ON p.emp_id = e.emp_id
WHERE e.exit_date IS NULL
GROUP BY e.emp_id, e.full_name, e.joined_on
HAVING COALESCE(MAX(p.promoted_on), e.joined_on) <= DATE_SUB('2025-07-01', INTERVAL 3 YEAR)
ORDER BY in_role_since, e.emp_id← Average Days to Fill a Requisition by Department · Performance Bonus Payout for FY2024-25 →