High Performers Due for a Promotion Review

Medium
236.

High Performers Due for a Promotion Review

MediumSubqueriesDates

MySQL

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

ColumnType
emp_idint
full_namevarchar
joined_ondate
exit_datedate

Primary key: emp_id.

exit_date is NULL for a current employee.

Table: Promotion

ColumnType
emp_idint
promoted_ondate
new_gradevarchar

Primary key (together): emp_id, promoted_on.

One row per promotion an employee received.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

Primary key (together): emp_id, cycle.

rating is 1–5, or NULL until the manager submits it.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namejoined_onexit_date
1Aarav Sharma2018-03-12NULL
2Diya Iyer2021-06-01NULL
3Ishaan Gupta2019-08-19NULL
4Kavya Nair2020-01-062025-03-31
5Rohan Das2022-07-01NULL
6Meera Rao2017-11-20NULL

Promotion

emp_idpromoted_onnew_grade
12021-04-01L3
32021-04-01L3
32023-04-01L4
62020-04-01L4

Appraisal

emp_idcyclerating
1FY2024-254
2FY2024-255
3FY2024-255
4FY2024-255
5FY2024-254
6FY2024-25NULL
6FY2023-245

Output

emp_idfull_namein_role_since
1Aarav Sharma2021-04-01
2Diya Iyer2021-06-01
5Rohan Das2022-07-01
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.

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

ColumnType
emp_idint
full_namevarchar
joined_ondate
exit_datedate

Primary key: emp_id.

exit_date is NULL for a current employee.

Table: Promotion

ColumnType
emp_idint
promoted_ondate
new_gradevarchar

Primary key: emp_id, promoted_on.

One row per promotion an employee received.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

Primary key: emp_id, cycle.

rating is 1–5, or NULL until the manager submits it.

Examples

Example 1

Employee

emp_idfull_namejoined_onexit_date
1Aarav Sharma2018-03-12NULL
2Diya Iyer2021-06-01NULL
3Ishaan Gupta2019-08-19NULL
4Kavya Nair2020-01-062025-03-31
5Rohan Das2022-07-01NULL
6Meera Rao2017-11-20NULL

Promotion

emp_idpromoted_onnew_grade
12021-04-01L3
32021-04-01L3
32023-04-01L4
62020-04-01L4

Appraisal

emp_idcyclerating
1FY2024-254
2FY2024-255
3FY2024-255
4FY2024-255
5FY2024-254
6FY2024-25NULL
6FY2023-245

Output

emp_idfull_namein_role_since
1Aarav Sharma2021-04-01
2Diya Iyer2021-06-01
5Rohan Das2022-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_id

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