Appraisal Ratings Falling Two Cycles Running

Medium
238.

Appraisal Ratings Falling Two Cycles Running

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

ColumnType
emp_idint
cycle_yearint
ratingint

Primary key (together): 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.

Example

The rows must come back in the order shown.

Appraisal

emp_idcycle_yearrating
120215
120225
120234
120243
220224
220233
220243
320205
320224
320242
2 more rows

Output

emp_idtrend
15>4>3
35>4>2
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.

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

ColumnType
emp_idint
cycle_yearint
ratingint

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_idcycle_yearrating
120215
120225
120234
120243
220224
220233
220243
320205
320224
320242
420234
420242

Output

emp_idtrend
15>4>3
35>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_id

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

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