Appraisal Ratings Falling Two Cycles Running — SQL Problem
HR business partners want to talk to anyone whose performance is sliding.
- Difficulty: Medium
- Topics: Window Functions, Strings
- Dialect: MySQL
- Problem: #238
Problem statement
HR business partners want to talk to anyone whose performance is sliding. Look at each employee's three most recent appraisal cycles (by cycle_year): the employee is flagged when the ratings strictly fall from the oldest of the three to the middle one and from the middle one to the latest. Employees with fewer than three appraisals are never flagged.
Return emp_id and trend, the three ratings oldest first joined by > (for example 5>4>2). Order by emp_id.
Tables
Table: Appraisal
| Column | Type |
|---|---|
| emp_id | int |
| cycle_year | int |
| rating | int |
Primary key: emp_id, cycle_year.
One row per employee per yearly cycle they were rated in; rating is 1–5 and never NULL. An employee may have skipped a cycle (on leave), so years can have gaps.
Examples
Example 1
Appraisal
| emp_id | cycle_year | rating |
|---|---|---|
| 1 | 2021 | 5 |
| 1 | 2022 | 5 |
| 1 | 2023 | 4 |
| 1 | 2024 | 3 |
| 2 | 2022 | 4 |
| 2 | 2023 | 3 |
| 2 | 2024 | 3 |
| 3 | 2020 | 5 |
| 3 | 2022 | 4 |
| 3 | 2024 | 2 |
| 4 | 2023 | 4 |
| 4 | 2024 | 2 |
Output
| emp_id | trend |
|---|---|
| 1 | 5>4>3 |
| 3 | 5>4>2 |
How to solve Appraisal Ratings Falling Two Cycles Running
Each employee's three most recent appraisals decide the flag, so order each employee's rows newest first. On the latest row (ROW_NUMBER() = 1), LEAD(rating, 1) is the previous appraisal and LEAD(rating, 2) the one before that — whatever their years, so a skipped cycle is simply stepped over.
The test is two strict comparisons: oldest > middle and middle > latest. An employee with fewer than three appraisals has NULL in a LEAD, NULL comparisons are never true, and the row is dropped without a special case. A flat step (4, 3, 3) fails because 3 > 3 is false.
The trend string is built oldest first with CONCAT(r3, '>', r2, '>', r1) — numbers are converted to text by CONCAT. The second form ranks the rows, joins the ranks 1, 2 and 3 of each employee and builds the same string with CONCAT_WS. The third groups the top three rows, demands exactly three, compares them by pivoting with MAX(CASE …), and lets GROUP_CONCAT(rating ORDER BY cycle_year SEPARATOR '>') build the string. All are one sort per employee.
Reference solution (MySQL)
SELECT emp_id, CONCAT(r3, '>', r2, '>', r1) AS trend
FROM (
SELECT emp_id, rating AS r1,
LEAD(rating, 1) OVER (PARTITION BY emp_id ORDER BY cycle_year DESC) AS r2,
LEAD(rating, 2) OVER (PARTITION BY emp_id ORDER BY cycle_year DESC) AS r3,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY cycle_year DESC) AS rn
FROM Appraisal
) t
WHERE rn = 1 AND r3 > r2 AND r2 > r1
ORDER BY emp_idAnother way
WITH ranked AS (
SELECT emp_id, rating, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY cycle_year DESC) AS rn FROM Appraisal
)
SELECT a.emp_id, CONCAT_WS('>', c.rating, b.rating, a.rating) AS trend
FROM ranked a
JOIN ranked b ON b.emp_id = a.emp_id AND b.rn = 2
JOIN ranked c ON c.emp_id = a.emp_id AND c.rn = 3
WHERE a.rn = 1 AND c.rating > b.rating AND b.rating > a.rating
ORDER BY a.emp_idAnother way
SELECT emp_id, GROUP_CONCAT(rating ORDER BY cycle_year SEPARATOR '>') AS trend
FROM (SELECT emp_id, cycle_year, rating, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY cycle_year DESC) AS rn FROM Appraisal) t
WHERE rn <= 3
GROUP BY emp_id
HAVING COUNT(*) = 3
AND MAX(CASE WHEN rn = 3 THEN rating END) > MAX(CASE WHEN rn = 2 THEN rating END)
AND MAX(CASE WHEN rn = 2 THEN rating END) > MAX(CASE WHEN rn = 1 THEN rating END)
ORDER BY emp_id← Performance Bonus Payout for FY2024-25 · Monthly Attrition Rate in 2024 →