Find Consistently Improving Employees solution and explanation
4 more videos on this problem
Leetcode MEDIUM 3580 - RANKING in SQL - Consistently Improving Employees | Everyday Data ScienceEveryday Data Science · 11:24 · 1K views
LeetCode SQL 3580 - Find Consistently Improving EmployeesAnalyticHub · 15:23 · 145 views
LeetCode 3580 – Find Consistently Improving Employees | SQL Interview Question ExplainedInsightVanta · 15:34 · 118 views
Master SQL Window Functions: ROW_NUMBER & LAG | LeetCode 3580 SolutionVivek The Analyst · 22:46 · 50 views
LeetCode. Sql, 3580. Find Consistently Improving Employees, уровень MEDIUMДиванная аналитика · 20:41 · 1 view
The problem
Table: employees
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id is the unique identifier for this table. Each row contains information about an employee.
Table: performance_reviews
+-------------+------+ | Column Name | Type | +-------------+------+ | review_id | int | | employee_id | int | | review_date | date | | rating | int | +-------------+------+ review_id is the unique identifier for this table. Each row represents a performance review for an employee. The rating is on a scale of 1-5 where 5 is excellent and 1 is poor.
Write a solution to find employees who have consistently improved their performance over their last three reviews.
- An employee must have at least
3review to be considered - The employee's last
3reviews must show strictly increasing ratings (each review better than the previous) - Use the most recent
3reviews based onreview_datefor each employee - Calculate the improvement score as the difference between the latest rating and the earliest rating among the last
3reviews
Return the result table ordered by improvement score in descending order, then by name in ascending order.
The result format is in the following example.
Example:
Input:
employees table:
+-------------+----------------+ | employee_id | name | +-------------+----------------+ | 1 | Alice Johnson | | 2 | Bob Smith | | 3 | Carol Davis | | 4 | David Wilson | | 5 | Emma Brown | +-------------+----------------+
performance_reviews table:
+-----------+-------------+-------------+--------+ | review_id | employee_id | review_date | rating | +-----------+-------------+-------------+--------+ | 1 | 1 | 2023-01-15 | 2 | | 2 | 1 | 2023-04-15 | 3 | | 3 | 1 | 2023-07-15 | 4 | | 4 | 1 | 2023-10-15 | 5 | | 5 | 2 | 2023-02-01 | 3 | | 6 | 2 | 2023-05-01 | 2 | | 7 | 2 | 2023-08-01 | 4 | | 8 | 2 | 2023-11-01 | 5 | | 9 | 3 | 2023-03-10 | 1 | | 10 | 3 | 2023-06-10 | 2 | | 11 | 3 | 2023-09-10 | 3 | | 12 | 3 | 2023-12-10 | 4 | | 13 | 4 | 2023-01-20 | 4 | | 14 | 4 | 2023-04-20 | 4 | | 15 | 4 | 2023-07-20 | 4 | | 16 | 5 | 2023-02-15 | 3 | | 17 | 5 | 2023-05-15 | 2 | +-----------+-------------+-------------+--------+
Output:
+-------------+----------------+-------------------+ | employee_id | name | improvement_score | +-------------+----------------+-------------------+ | 2 | Bob Smith | 3 | | 1 | Alice Johnson | 2 | | 3 | Carol Davis | 2 | +-------------+----------------+-------------------+
Explanation:
- Alice Johnson (employee_id = 1):
- Has 4 reviews with ratings: 2, 3, 4, 5
- Last 3 reviews (by date): 2023-04-15 (3), 2023-07-15 (4), 2023-10-15 (5)
- Ratings are strictly increasing: 3 → 4 → 5
- Improvement score: 5 - 3 = 2
- Carol Davis (employee_id = 3):
- Has 4 reviews with ratings: 1, 2, 3, 4
- Last 3 reviews (by date): 2023-06-10 (2), 2023-09-10 (3), 2023-12-10 (4)
- Ratings are strictly increasing: 2 → 3 → 4
- Improvement score: 4 - 2 = 2
- Bob Smith (employee_id = 2):
- Has 4 reviews with ratings: 3, 2, 4, 5
- Last 3 reviews (by date): 2023-05-01 (2), 2023-08-01 (4), 2023-11-01 (5)
- Ratings are strictly increasing: 2 → 4 → 5
- Improvement score: 5 - 2 = 3
- Employees not included:
- David Wilson (employee_id = 4): Last 3 reviews are all 4 (no improvement)
- Emma Brown (employee_id = 5): Only has 2 reviews (needs at least 3)
The output table is ordered by improvement_score in descending order, then by name in ascending order.
Approach overview
Problem Overview: Each employee has a history of review ratings. Return the employees whose three most recent ratings are strictly increasing, along with their improvement, which is the latest rating minus the oldest of those three. Sort by improvement descending, then by name ascending.
Approach 1: Correlated Subqueries (O(n^2) time, O(1) extra space)
For each review, use a correlated subquery to count how many reviews for the same employee are more recent. Keep only rows where that count is less than 3, then compare the three ratings in order. Each subquery scans the table again, so the cost grows quickly with row count. Use this approach to confirm the problem logic, but do not submit it as-is. Interviewers will flag the repeated table scans.
Approach 2: Window Functions with ROW_NUMBER (O(n log n) time, O(n) space)
Partition the table by employee_id and order each partition by review_date DESC with ROW_NUMBER(). Row 1 is the latest review, row 3 is the third latest. Filter to rn <= 3, then aggregate per employee using conditional MAX(CASE WHEN rn = 1 ...) expressions to pivot the three ratings into columns. Keep employees with exactly three reviews where the oldest rating is less than the middle, and the middle is less than the latest. The improvement is the latest rating minus the oldest. The sort cost dominates, which gives O(n log n). This is the pattern to learn: rank within a group, filter by rank, then pivot.
Recommended for interviews: Interviewers expect the window function approach. The correlated subquery version shows you understand the requirement, but the ROW_NUMBER pivot shows you can write set-based SQL that scales. Practice the same pattern on other database problems and on window function problems to make it automatic.
Solution
Thinking
We only care whether an employee’s last three ratings are strictly increasing; the score is last minus first. Number reviews per employee by date descending and shift to get adjacent deltas.
Keep ranks
2and3(two deltas versus the latest review), require exactly two rows and a positive minimum delta, join names, and sort by score then name.
First, we extract the most recent three performance review records for each employee and calculate the difference in rating between each review and the previous one. Next, we filter out employees whose ratings are strictly increasing, and compute their improvement score (i.e., the last rating minus the first rating among the last three reviews). Finally, we sort the results by improvement score in descending order and by name in ascending order.
WITH
recent AS (
SELECT
employee_id,
review_date,
ROW_NUMBER() OVER (
PARTITION BY employee_id
ORDER BY review_date DESC
) AS rn,
(
LAG(rating) OVER (
PARTITION BY employee_id
ORDER BY review_date DESC
) - rating
) AS delta
FROM performance_reviews
)
SELECT
employee_id,
name,
SUM(delta) AS improvement_score
FROM
recent
JOIN employees USING (employee_id)
WHERE rn > 1 AND rn <= 3
GROUP BY 1
HAVING COUNT(*) = 2 AND MIN(delta) > 0
ORDER BY 3 DESC, 2;import pandas as pd
def find_consistently_improving_employees(
employees: pd.DataFrame, performance_reviews: pd.DataFrame
) -> pd.DataFrame:
performance_reviews = performance_reviews.sort_values(
["employee_id", "review_date"], ascending=[True, False]
)
performance_reviews["rn"] = (
performance_reviews.groupby("employee_id").cumcount() + 1
)
performance_reviews["lag_rating"] = performance_reviews.groupby("employee_id")[
"rating"
].shift(1)
performance_reviews["delta"] = (
performance_reviews["lag_rating"] - performance_reviews["rating"]
)
recent = performance_reviews[
(performance_reviews["rn"] > 1) & (performance_reviews["rn"] <= 3)
]
improvement = (
recent.groupby("employee_id")
.agg(
improvement_score=("delta", "sum"),
count=("delta", "count"),
min_delta=("delta", "min"),
)
.reset_index()
)
improvement = improvement[
(improvement["count"] == 2) & (improvement["min_delta"] > 0)
]
result = improvement.merge(employees[["employee_id", "name"]], on="employee_id")
result = result.sort_values(
by=["improvement_score", "name"], ascending=[False, True]
)
return result[["employee_id", "name", "improvement_score"]]Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subqueries | O(n^2) | O(1) | Quick prototype or to verify logic on small data |
| Window Functions with ROW_NUMBER | O(n log n) | O(n) | General case and interview answer |
Frequently asked questions
Is Find Consistently Improving Employees easy or hard?
It is rated Medium with a 58.4% acceptance rate. The difficulty comes from combining ranking, filtering to the latest three rows, and checking strict ordering in one query. Once you know the ROW_NUMBER pattern, the implementation is short.
Find Consistently Improving Employees Python/Java solution
This problem is written in SQL, so there is no Python or Java answer on the platform. If you want the same logic in Python, use pandas: sort by review_date descending within each employee_id, take the first three rows per group, and check that the ratings strictly increase. The pandas version runs in O(n log n) time.
How to solve Find Consistently Improving Employees in O(n)?
A single pass in SQL is not possible here because the database must sort reviews by date within each employee. Treat O(n log n) as the practical bound. In application code, you could do one pass per employee after sorting, but that still requires sorting, so the bound stays O(n log n).
What is the best approach for Find Consistently Improving Employees?
The best approach uses window functions. Apply ROW_NUMBER() partitioned by employee_id and ordered by review_date descending, keep the three most recent reviews, pivot them into columns, and check that the ratings strictly increase. This runs in O(n log n) time with O(n) space.
Is Find Consistently Improving Employees asked at Google/Amazon/Meta?
This problem is tagged as a database question on LeetCode, and it is a common style of SQL interview question at large tech companies. Amazon, Meta, and Google interviews often test ROW_NUMBER, conditional aggregation, and filtering on window results, which this problem combines.
What data structure is used in Find Consistently Improving Employees?
The problem uses a relational table, not a traditional data structure. The key SQL tools are the ROW_NUMBER() window function for ranking reviews within each employee, and conditional aggregation with CASE WHEN to pivot ratings into columns for comparison.
What is the time complexity of Find Consistently Improving Employees?
The optimal window function solution runs in O(n log n) time, where n is the number of review rows, because the partitioned ordering dominates the cost. The naive correlated subquery approach runs in O(n^2) time because it rescans the table for each row.