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.

Description Solution Video 11:24

3580. Find Consistently Improving Employees

Medium58.4% acceptance

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 3 review 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.

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.

Topics

Database

11:24

Still stuck?Read the written solution, or watch a walkthrough.

Open solution

LanguageLoading editor…

1# Write your MySQL query statement below

Starter codeMySQLSpaces: 4

Examples

Case 1

Input

{"headers":{"employees":["employee_id","name"],"performance_reviews":["review_id","employee_id","review_date","rating"]},"rows":{"employees":[[1,"Alice Johnson"],[2,"Bob Smith"],[3,"Carol Davis"],[4,"David Wilson"],[5,"Emma Brown"]],"performance_reviews":[[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]]}}

Your code is saved on this device.Solution