Find Consistently Improving Employees - Problem

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_date for 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

employees
Column Name Type Description
employee_id PK int Unique identifier for each employee
name varchar Employee's full name
Primary Key: employee_id
performance_reviews
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)
Primary Key: review_id

Input & Output

Example 1 — Consistently Improving Employee
Input Tables:
employees
employee_id name
1 John Smith
2 Alice Johnson
3 Bob Wilson
performance_reviews
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
Output:
name improvement_score
Alice Johnson 1
💡 Note:

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.
Example 2 — Multiple Improving Employees
Input Tables:
employees
employee_id name
1 Sarah Davis
2 Mike Brown
performance_reviews
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
Output:
name improvement_score
Sarah Davis 1
💡 Note:

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.
Example 3 — No Qualifying Employees
Input Tables:
employees
employee_id name
1 Tom Wilson
performance_reviews
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
Output:
name improvement_score
Tom Wilson 1
💡 Note:

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_date is a valid date
  • Each employee can have multiple performance reviews

Visualization

Tap to expand
Find Consistently Improving Employees INPUT Emp Name Reviews E1 Alice [3,4,5,6,7] E2 Bob [5,4,6,7,8] E3 Carol [2,3,5,8,9] E4 Dan [7,6,5,4,3] E5 Eve [4,5] Last 3 Reviews Focus: Alice: [5, 6, 7] Bob: [6, 7, 8] Carol: [5, 8, 9] Requirements: - Min 3 reviews - Strictly increasing ALGORITHM STEPS 1 Filter by Count Keep employees with reviews.length >= 3 2 Extract Last 3 Get slice(-3) of reviews for each employee 3 Check Increasing r[0] < r[1] < r[2] must be strictly true 4 Calculate Score score = r[2] - r[0] (latest - earliest) Validation Check: Alice: 5<6<7 OK s=2 Bob: 6<7<8 OK s=2 Carol: 5<8<9 OK s=4 Dan: 5>4>3 FAIL Eve: len=2 FAIL FINAL RESULT Sort Order: 1. score DESC, 2. name ASC Rank Name Score 1 Carol 4 2 Alice 2 3 Bob 2 Alice vs Bob: same score (2) Alice < Bob alphabetically Output Array: [{name:"Carol", score:4}, {name:"Alice", score:2}, {name:"Bob", score:2}] Key Insight: The optimal approach uses a single pass through employees with O(n) filtering. For each employee, we only examine the last 3 reviews (constant time), check strict inequality, and compute the improvement score. Sorting qualified employees is O(k log k) where k is the count of improving employees. TutorialsPoint - Find Consistently Improving Employees | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K Views
Medium Frequency
~18 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen