Find Consistently Improving Employees - Practice Coding Problems

Find Consistently Improving Employees - Problem

Database Medium

🚀 Employee Performance Tracker

Imagine you're building an HR analytics system for a company that wants to identify their rising stars - employees who consistently improve their performance over time!

You have two tables:

employees: Contains basic employee information (id and name)
performance_reviews: Contains performance ratings (1-5 scale, where 5 is excellent)

Your mission: Find employees who show consistent improvement in their last 3 performance reviews. Here's what qualifies:

📊 Must have at least 3 reviews total
📈 Last 3 reviews must show strictly increasing ratings (each better than previous)
🎯 Calculate an improvement score = (latest rating - earliest rating among last 3)

Return results ordered by improvement score (highest first), then by name alphabetically.

Example: If an employee's last 3 reviews were ratings [2, 3, 5], they qualify with improvement score = 5 - 2 = 3

Input & Output

example_1.sql — Basic Example

$ Input: employees: [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')] performance_reviews: [(1, 1, '2023-01-01', 2), (2, 1, '2023-02-01', 3), (3, 1, '2023-03-01', 5), (4, 2, '2023-01-01', 4), (5, 2, '2023-02-01', 4), (6, 2, '2023-03-01', 3)]

› Output: [{employee_id: 1, name: 'Alice', improvement_score: 3}]

💡 Note: Alice has reviews [2,3,5] which show consistent improvement (2<3<5). Improvement score = 5-2 = 3. Bob has [4,4,3] which is not improving, so he's excluded.

example_2.sql — Multiple Qualifying Employees

$ Input: employees: [(1, 'David'), (2, 'Emma'), (3, 'Frank')] performance_reviews: [(1, 1, '2023-01-01', 1), (2, 1, '2023-02-01', 3), (3, 1, '2023-03-01', 4), (4, 2, '2023-01-01', 2), (5, 2, '2023-02-01', 4), (6, 2, '2023-03-01', 5)]

› Output: [{employee_id: 2, name: 'Emma', improvement_score: 3}, {employee_id: 1, name: 'David', improvement_score: 3}]

💡 Note: Both David [1,3,4] and Emma [2,4,5] show improvement. Both have score 3, so ordered by name: Emma first, then David.

example_3.sql — Edge Case: Insufficient Reviews

$ Input: employees: [(1, 'Grace'), (2, 'Henry')] performance_reviews: [(1, 1, '2023-01-01', 3), (2, 1, '2023-02-01', 4), (3, 2, '2023-01-01', 1)]

› Output: []

💡 Note: Grace has only 2 reviews [3,4], Henry has only 1 review [1]. Neither has the required minimum 3 reviews, so no employees qualify.

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ review_id ≤ 10⁵
1 ≤ rating ≤ 5
review_date is a valid date string
Each employee must have at least 3 reviews to be considered
Employee names are unique and non-empty strings

Visualization

Tap to expand

Understanding the Visualization

Group by Employee

Separate all performance reviews by employee (like organizing race results by runner)

Sort by Date

Order each employee's reviews from most recent to oldest

Take Last 3

Focus only on the most recent 3 performance reviews for each employee

Check Improvement

Verify that ratings form a strictly increasing sequence: oldest < middle < newest

Calculate Score

Improvement score = newest rating - oldest rating among the last 3 reviews

Key Takeaway

🎯 Key Insight: Use window functions to partition data by employee and efficiently compare consecutive ratings using LAG() - this avoids expensive correlated subqueries and processes data in a single pass.

Asked in

f Meta 35 a Amazon 28 G Google 22 ⊞ Microsoft 18

The optimal approach uses SQL window functions with ROW_NUMBER() and LAG() to efficiently identify employees with consistently improving performance. Key insights: partition data by employee, rank reviews by date, and use LAG to compare consecutive ratings in a single pass through the data.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Correlated Subqueries)	O(n²)	O(1)	Use multiple nested subqueries to check each employee individually
Window Functions (Optimal)	O(n log n)	O(n)	Use window functions to efficiently rank and filter reviews in a single pass

Brute Force (Correlated Subqueries) — Algorithm Steps

For each employee, use a subquery to get all their reviews
Use another subquery to get only their last 3 reviews by date
Check if these 3 reviews form a strictly increasing sequence
Calculate improvement score as latest - earliest rating
Filter and sort the results

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan All Employees

Iterate through each employee in the employees table

Count Reviews

For each employee, count their total reviews using subquery

Get Last 3 Reviews

Use another subquery to get their most recent 3 reviews

Check Improvement

Verify if the 3 reviews show strictly increasing pattern

Calculate Score

Compute improvement score as latest - earliest rating

Code -

solution.c — C

// SQL Solution using correlated subqueries
#include <stdio.h>

int main() {
    char* sqlQuery = "\
SELECT \
    e.employee_id,\
    e.name,\
    (\
        SELECT MAX(pr3.rating) \
        FROM (\
            SELECT rating \
            FROM performance_reviews pr_inner \
            WHERE pr_inner.employee_id = e.employee_id \
            ORDER BY pr_inner.review_date DESC \
            LIMIT 3\
        ) pr3\
    ) - (\
        SELECT MIN(pr3.rating) \
        FROM (\
            SELECT rating \
            FROM performance_reviews pr_inner \
            WHERE pr_inner.employee_id = e.employee_id \
            ORDER BY pr_inner.review_date DESC \
            LIMIT 3\
        ) pr3\
    ) AS improvement_score\
FROM employees e\
WHERE (\
    SELECT COUNT(*) \
    FROM performance_reviews pr \
    WHERE pr.employee_id = e.employee_id\
) >= 3\
ORDER BY improvement_score DESC, e.name ASC;";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each employee, we scan through all their reviews multiple times

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

28.5K Views

Medium Frequency

~25 min Avg. Time

847 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

🚀 Employee Performance Tracker

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Correlated Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler