You are given two tables: employees and performance_reviews. Find employees who have consistently improved their performance over their last three reviews.
Requirements:
- An employee must have at least 3 reviews to be considered
- The employee's last 3 reviews must show strictly increasing ratings (each review better than the previous)
- Use the most recent 3 reviews based on
review_datefor each employee - Calculate the improvement score as the difference between the latest rating and the earliest rating among the last 3 reviews
Return the result table ordered by improvement score in descending order, then by name in ascending order.
Table Schema
| Column Name | Type | Description |
|---|---|---|
employee_id
PK
|
int | Unique identifier for each employee |
name
|
varchar | Employee's full name |
| Column Name | Type | Description |
|---|---|---|
review_id
PK
|
int | Unique identifier for each review |
employee_id
|
int | Foreign key referencing employees table |
review_date
|
date | Date when the performance review was conducted |
rating
|
int | Performance rating on scale of 1-5 (5 is excellent, 1 is poor) |
Input & Output
| employee_id | name |
|---|---|
| 1 | John Smith |
| 2 | Alice Johnson |
| 3 | Bob Wilson |
| review_id | employee_id | review_date | rating |
|---|---|---|---|
| 1 | 1 | 2024-01-15 | 3 |
| 2 | 1 | 2024-02-15 | 4 |
| 3 | 1 | 2024-03-15 | 5 |
| 4 | 2 | 2024-01-10 | 2 |
| 5 | 2 | 2024-02-10 | 2 |
| 6 | 2 | 2024-03-10 | 3 |
| 7 | 3 | 2024-01-20 | 4 |
| 8 | 3 | 2024-02-20 | 3 |
| name | improvement_score |
|---|---|
| John Smith | 2 |
John Smith has 3 reviews with ratings 3→4→5, showing consistent improvement. Alice Johnson's ratings are 2→2→3 (not strictly increasing since 2=2). Bob Wilson only has 2 reviews, not meeting the minimum requirement of 3 reviews.
| employee_id | name |
|---|---|
| 1 | Sarah Davis |
| 2 | Mike Brown |
| review_id | employee_id | review_date | rating |
|---|---|---|---|
| 1 | 1 | 2024-01-01 | 2 |
| 2 | 1 | 2024-02-01 | 3 |
| 3 | 1 | 2024-03-01 | 5 |
| 4 | 1 | 2024-04-01 | 4 |
| 5 | 2 | 2024-01-05 | 1 |
| 6 | 2 | 2024-02-05 | 2 |
| 7 | 2 | 2024-03-05 | 3 |
| name | improvement_score |
|---|---|
| Mike Brown | 2 |
| Sarah Davis | 1 |
Sarah Davis has 4 reviews, but only the last 3 are considered: 3→5→4. Since 5>4 is false, she doesn't qualify for consistent improvement based on most recent 3. Wait, let me recalculate: last 3 reviews by date are 5→4 (decreasing), so she doesn't qualify. Mike Brown has ratings 1→2→3, showing consistent improvement with score 2. Only Mike qualifies.
| employee_id | name |
|---|---|
| 1 | Tom Wilson |
| review_id | employee_id | review_date | rating |
|---|---|---|---|
| 1 | 1 | 2024-01-01 | 4 |
| 2 | 1 | 2024-02-01 | 3 |
| 3 | 1 | 2024-03-01 | 5 |
| name | improvement_score |
|---|
Tom Wilson has ratings 4→3→5 over time. Since 3<4, this is not consistently improving (there's a decrease from 4 to 3), so no employees qualify for the result.
Constraints
-
1 ≤ employee_id ≤ 1000 -
1 ≤ rating ≤ 5 -
review_dateis a valid date - Each employee can have multiple performance reviews