Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                
Skip to content

Commit 975b81e

Browse files
javadevjavadev
authored
Added task 3580
1 parent 1dffcda commit 975b81e

File tree

3 files changed

+229
-0
lines changed

3 files changed

+229
-0
lines changed

‎src/main/java/g3501_3600/s3580_find_consistently_improving_employees/readme.md

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,112 @@
1+
3580\. Find Consistently Improving Employees
2+
3+
Medium
4+
5+
Table: `employees`
6+
7+
+-------------+---------+
8+
| Column Name | Type |
9+
+-------------+---------+
10+
| employee_id | int |
11+
| name | varchar |
12+
+-------------+---------+
13+
employee_id is the unique identifier for this table.
14+
Each row contains information about an employee.
15+
16+
Table: `performance_reviews`
17+
18+
+-------------+------+
19+
| Column Name | Type |
20+
+-------------+------+
21+
| review_id | int |
22+
| employee_id | int |
23+
| review_date | date |
24+
| rating | int |
25+
+-------------+------+
26+
review_id is the unique identifier for this table.
27+
Each row represents a performance review for an employee.
28+
The rating is on a scale of 1-5 where 5 is excellent and 1 is poor.
29+
30+
Write a solution to find employees who have consistently improved their performance over **their last three reviews**.
31+
32+
* An employee must have **at least** `3` **review** to be considered
33+
* The employee's **last** `3` **reviews** must show **strictly increasing ratings** (each review better than the previous)
34+
* Use the most recent `3` reviews based on `review_date` for each employee
35+
* Calculate the **improvement score** as the difference between the latest rating and the earliest rating among the last `3` reviews
36+
37+
Return _the result table ordered by **improvement score** in **descending** order, then by **name** in **ascending** order_.
38+
39+
The result format is in the following example.
40+
41+
**Example:**
42+
43+
**Input:**
44+
45+
employees table:
46+
47+
+-------------+----------------+
48+
| employee_id | name |
49+
+-------------+----------------+
50+
| 1 | Alice Johnson |
51+
| 2 | Bob Smith |
52+
| 3 | Carol Davis |
53+
| 4 | David Wilson |
54+
| 5 | Emma Brown |
55+
+-------------+----------------+
56+
57+
performance\_reviews table:
58+
59+
+-----------+-------------+-------------+--------+
60+
| review_id | employee_id | review_date | rating |
61+
+-----------+-------------+-------------+--------+
62+
| 1 | 1 | 2023-01-15 | 2 |
63+
| 2 | 1 | 2023-04-15 | 3 |
64+
| 3 | 1 | 2023-07-15 | 4 |
65+
| 4 | 1 | 2023-10-15 | 5 |
66+
| 5 | 2 | 2023-02-01 | 3 |
67+
| 6 | 2 | 2023-05-01 | 2 |
68+
| 7 | 2 | 2023-08-01 | 4 |
69+
| 8 | 2 | 2023-11-01 | 5 |
70+
| 9 | 3 | 2023-03-10 | 1 |
71+
| 10 | 3 | 2023-06-10 | 2 |
72+
| 11 | 3 | 2023-09-10 | 3 |
73+
| 12 | 3 | 2023-12-10 | 4 |
74+
| 13 | 4 | 2023-01-20 | 4 |
75+
| 14 | 4 | 2023-04-20 | 4 |
76+
| 15 | 4 | 2023-07-20 | 4 |
77+
| 16 | 5 | 2023-02-15 | 3 |
78+
| 17 | 5 | 2023-05-15 | 2 |
79+
+-----------+-------------+-------------+--------+
80+
81+
**Output:**
82+
83+
+-------------+----------------+-------------------+
84+
| employee_id | name | improvement_score |
85+
+-------------+----------------+-------------------+
86+
| 2 | Bob Smith | 3 |
87+
| 1 | Alice Johnson | 2 |
88+
| 3 | Carol Davis | 2 |
89+
+-------------+----------------+-------------------+
90+
91+
**Explanation:**
92+
93+
* **Alice Johnson (employee\_id = 1):**
94+
* Has 4 reviews with ratings: 2, 3, 4, 5
95+
* Last 3 reviews (by date): 2023-04-15 (3), 2023-07-15 (4), 2023-10-15 (5)
96+
* Ratings are strictly increasing: 3 → 4 → 5
97+
* Improvement score: 5 - 3 = 2
98+
* **Carol Davis (employee\_id = 3):**
99+
* Has 4 reviews with ratings: 1, 2, 3, 4
100+
* Last 3 reviews (by date): 2023-06-10 (2), 2023-09-10 (3), 2023-12-10 (4)
101+
* Ratings are strictly increasing: 2 → 3 → 4
102+
* Improvement score: 4 - 2 = 2
103+
* **Bob Smith (employee\_id = 2):**
104+
* Has 4 reviews with ratings: 3, 2, 4, 5
105+
* Last 3 reviews (by date): 2023-05-01 (2), 2023-08-01 (4), 2023-11-01 (5)
106+
* Ratings are strictly increasing: 2 → 4 → 5
107+
* Improvement score: 5 - 2 = 3
108+
* **Employees not included:**
109+
* David Wilson (employee\_id = 4): Last 3 reviews are all 4 (no improvement)
110+
* Emma Brown (employee\_id = 5): Only has 2 reviews (needs at least 3)
111+
112+
The output table is ordered by improvement\_score in descending order, then by name in ascending order.

‎src/main/java/g3501_3600/s3580_find_consistently_improving_employees/script.sql

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2025_06_11_Time_449_ms_(91.67%)_Space_0.0_MB_(100.00%)
3+
WITH Ranked AS (
4+
SELECT
5+
e.employee_id,
6+
e.name,
7+
pr.review_date,
8+
pr.rating,
9+
RANK() OVER (
10+
PARTITION BY e.employee_id
11+
ORDER BY pr.review_date DESC
12+
) AS rnk,
13+
LAG(pr.rating) OVER (
14+
PARTITION BY e.employee_id
15+
ORDER BY pr.review_date DESC
16+
) AS lag_rating
17+
FROM employees e
18+
LEFT JOIN performance_reviews pr
19+
ON e.employee_id = pr.employee_id
20+
)
21+
SELECT
22+
employee_id,
23+
name,
24+
MAX(rating) - MIN(rating) AS improvement_score
25+
FROM Ranked
26+
WHERE rnk <= 3
27+
GROUP BY
28+
employee_id,
29+
name
30+
HAVING
31+
COUNT(*) = 3
32+
AND SUM(CASE WHEN lag_rating > rating THEN 1 ELSE 0 END) = 2
33+
ORDER BY
34+
improvement_score DESC,
35+
name ASC;

‎src/test/java/g3501_3600/s3580_find_consistently_improving_employees/MysqlTest.java

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
package g3501_3600.s3580_find_consistently_improving_employees;
2+
3+
import static org.hamcrest.CoreMatchers.equalTo;
4+
import static org.hamcrest.MatcherAssert.assertThat;
5+
6+
import java.io.BufferedReader;
7+
import java.io.FileNotFoundException;
8+
import java.io.FileReader;
9+
import java.sql.Connection;
10+
import java.sql.ResultSet;
11+
import java.sql.SQLException;
12+
import java.sql.Statement;
13+
import java.util.stream.Collectors;
14+
import javax.sql.DataSource;
15+
import org.junit.jupiter.api.Test;
16+
import org.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
import org.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
import org.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode = CompatibilityMode.MySQL,
22+
initialSqls =
23+
"CREATE TABLE employees(employee_id INTEGER, name VARCHAR(255)); "
24+
+ "INSERT INTO employees (employee_id, name) VALUES"
25+
+ " (1, 'Alice Johnson'),"
26+
+ " (2, 'Bob Smith'),"
27+
+ " (3, 'Carol Davis'),"
28+
+ " (4, 'David Wilson'),"
29+
+ " (5, 'Emma Brown');"
30+
+ "CREATE TABLE performance_reviews(review_id INTEGER, employee_id INTEGER"
31+
+ ", review_date DATE, rating INTEGER); "
32+
+ "INSERT INTO performance_reviews (review_id, employee_id, review_date, rating) VALUES"
33+
+ " (1, 1, '2023-01-15', 2),"
34+
+ " (2, 1, '2023-04-15', 3),"
35+
+ " (3, 1, '2023-07-15', 4),"
36+
+ " (4, 1, '2023-10-15', 5),"
37+
+ " (5, 2, '2023-02-01', 3),"
38+
+ " (6, 2, '2023-05-01', 2),"
39+
+ " (7, 2, '2023-08-01', 4),"
40+
+ " (8, 2, '2023-11-01', 5),"
41+
+ " (9, 3, '2023-03-10', 1),"
42+
+ " (10, 3, '2023-06-10', 2),"
43+
+ " (11, 3, '2023-09-10', 3),"
44+
+ " (12, 3, '2023-12-10', 4),"
45+
+ " (13, 4, '2023-01-20', 4),"
46+
+ " (14, 4, '2023-04-20', 4),"
47+
+ " (15, 4, '2023-07-20', 4),"
48+
+ " (16, 5, '2023-02-15', 3),"
49+
+ " (17, 5, '2023-05-15', 2);")
50+
class MysqlTest {
51+
@Test
52+
void testScript(@EmbeddedDatabase DataSource dataSource)
53+
throws SQLException, FileNotFoundException {
54+
try (final Connection connection = dataSource.getConnection()) {
55+
try (final Statement statement = connection.createStatement();
56+
final ResultSet resultSet =
57+
statement.executeQuery(
58+
new BufferedReader(
59+
new FileReader(
60+
"src/main/java/g3501_3600/"
61+
+ "s3580_find_consistently_improving_employees/"
62+
+ "script.sql"))
63+
.lines()
64+
.collect(Collectors.joining("\n"))
65+
.replaceAll("#.*?\\r?\\n", ""))) {
66+
assertThat(resultSet.next(), equalTo(true));
67+
assertThat(resultSet.getNString(1), equalTo("2"));
68+
assertThat(resultSet.getNString(2), equalTo("Bob Smith"));
69+
assertThat(resultSet.getNString(3), equalTo("3"));
70+
assertThat(resultSet.next(), equalTo(true));
71+
assertThat(resultSet.getNString(1), equalTo("1"));
72+
assertThat(resultSet.getNString(2), equalTo("Alice Johnson"));
73+
assertThat(resultSet.getNString(3), equalTo("2"));
74+
assertThat(resultSet.next(), equalTo(true));
75+
assertThat(resultSet.getNString(1), equalTo("3"));
76+
assertThat(resultSet.getNString(2), equalTo("Carol Davis"));
77+
assertThat(resultSet.getNString(3), equalTo("2"));
78+
assertThat(resultSet.next(), equalTo(false));
79+
}
80+
}
81+
}
82+
}

0 commit comments

Comments
 (0)