Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit975b81e

Browse files
authored
Added task 3580
1 parent1dffcda commit975b81e

File tree

3 files changed

+229
-0
lines changed

3 files changed

+229
-0
lines changed
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.
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 RankedAS (
4+
SELECT
5+
e.employee_id,
6+
e.name,
7+
pr.review_date,
8+
pr.rating,
9+
RANK() OVER (
10+
PARTITION BYe.employee_id
11+
ORDER BYpr.review_dateDESC
12+
)AS rnk,
13+
LAG(pr.rating) OVER (
14+
PARTITION BYe.employee_id
15+
ORDER BYpr.review_dateDESC
16+
)AS lag_rating
17+
FROM employees e
18+
LEFT JOIN performance_reviews pr
19+
ONe.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+
ANDSUM(CASE WHEN lag_rating> rating THEN1 ELSE0 END)=2
33+
ORDER BY
34+
improvement_scoreDESC,
35+
nameASC;
Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
packageg3501_3600.s3580_find_consistently_improving_employees;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.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+
classMysqlTest {
51+
@Test
52+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
53+
throwsSQLException,FileNotFoundException {
54+
try (finalConnectionconnection =dataSource.getConnection()) {
55+
try (finalStatementstatement =connection.createStatement();
56+
finalResultSetresultSet =
57+
statement.executeQuery(
58+
newBufferedReader(
59+
newFileReader(
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)
[8]ページ先頭
©2009-2025 Movatter.jp