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

Commite9fc5eb

Browse files
authored
Added task 3421
1 parent8eee0d6 commite9fc5eb

File tree

3 files changed

+162
-0
lines changed

3 files changed

+162
-0
lines changed
Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
3421\. Find Students Who Improved
2+
3+
Medium
4+
5+
Table:`Scores`
6+
7+
+-------------+---------+
8+
| Column Name | Type |
9+
+-------------+---------+
10+
| student_id | int |
11+
| subject | varchar |
12+
| score | int |
13+
| exam_date | varchar |
14+
+-------------+---------+
15+
(student_id, subject, exam_date) is the primary key for this table.
16+
Each row contains information about a student's score in a specific subject on a particular exam date. score is between 0 and 100 (inclusive).
17+
18+
Write a solution to find the**students who have shown improvement**. A student is considered to have shown improvement if they meet**both** of these conditions:
19+
20+
* Have taken exams in the**same subject** on at least two different dates
21+
* Their**latest score** in that subject is**higher** than their**first score**
22+
23+
Return_the result table__ordered by_`student_id,``subject`_in**ascending** order_.
24+
25+
The result format is in the following example.
26+
27+
**Example:**
28+
29+
**Input:**
30+
31+
Scores table:
32+
33+
+------------+----------+-------+------------+
34+
| student_id | subject | score | exam_date |
35+
+------------+----------+-------+------------+
36+
| 101 | Math | 70 | 15-01-2023 |
37+
| 101 | Math | 85 | 15-02-2023 |
38+
| 101 | Physics | 65 | 15-01-2023 |
39+
| 101 | Physics | 60 | 15-02-2023 |
40+
| 102 | Math | 80 | 15-01-2023 |
41+
| 102 | Math | 85 | 15-02-2023 |
42+
| 103 | Math | 90 | 15-01-2023 |
43+
| 104 | Physics | 75 | 15-01-2023 |
44+
| 104 | Physics | 85 | 15-02-2023 |
45+
+------------+----------+-------+------------+
46+
47+
**Output:**
48+
49+
+------------+----------+-------------+--------------+
50+
| student_id | subject | first_score | latest_score |
51+
+------------+----------+-------------+--------------+
52+
| 101 | Math | 70 | 85 |
53+
| 102 | Math | 80 | 85 |
54+
| 104 | Physics | 75 | 85 |
55+
+------------+----------+-------------+--------------+
56+
57+
**Explanation:**
58+
59+
* Student 101 in Math: Improved from 70 to 85
60+
* Student 101 in Physics: No improvement (dropped from 65 to 60)
61+
* Student 102 in Math: Improved from 80 to 85
62+
* Student 103 in Math: Only one exam, not eligible
63+
* Student 104 in Physics: Improved from 75 to 85
64+
65+
Result table is ordered by student\_id, subject.
Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2025_01_17_Time_466_ms_(74.56%)_Space_0B_(100.00%)
3+
4+
WITH RankedAS (
5+
SELECT
6+
student_id,
7+
subject,
8+
FIRST_VALUE(score) OVER(PARTITION BY student_id,subjectORDER BY exam_date)AS first_score,
9+
FIRST_VALUE(score) OVER(PARTITION BY student_id,subjectORDER BY exam_dateDESC)AS latest_score
10+
FROM Scores
11+
)
12+
13+
SELECT*FROM Ranked
14+
WHERE first_score<latest_score
15+
GROUP BY student_id,subject
16+
ORDER BY student_id,subject
Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,81 @@
1+
packageg3401_3500.s3421_find_students_who_improved;
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 Scores ("
24+
+" student_id INT,"
25+
+" subject VARCHAR(50),"
26+
+" score INT,"
27+
+" exam_date VARCHAR(10)"
28+
+");"
29+
+"insert into Scores (student_id, subject, score, exam_date) values "
30+
+"('101', 'Math', '70', '15-01-2023');"
31+
+"insert into Scores (student_id, subject, score, exam_date) values "
32+
+"('101', 'Math', '85', '15-02-2023');"
33+
+"insert into Scores (student_id, subject, score, exam_date) values "
34+
+"('101', 'Physics', '65', '15-01-2023');"
35+
+"insert into Scores (student_id, subject, score, exam_date) values "
36+
+"('101', 'Physics', '60', '15-02-2023');"
37+
+"insert into Scores (student_id, subject, score, exam_date) values "
38+
+"('102', 'Math', '80', '15-01-2023');"
39+
+"insert into Scores (student_id, subject, score, exam_date) values "
40+
+"('102', 'Math', '85', '15-02-2023');"
41+
+"insert into Scores (student_id, subject, score, exam_date) values "
42+
+"('103', 'Math', '90', '15-01-2023');"
43+
+"insert into Scores (student_id, subject, score, exam_date) values "
44+
+"('104', 'Physics', '75', '15-01-2023');"
45+
+"insert into Scores (student_id, subject, score, exam_date) values "
46+
+"('104', 'Physics', '85', '15-02-2023');")
47+
classMysqlTest {
48+
@Test
49+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
50+
throwsSQLException,FileNotFoundException {
51+
try (finalConnectionconnection =dataSource.getConnection()) {
52+
try (finalStatementstatement =connection.createStatement();
53+
finalResultSetresultSet =
54+
statement.executeQuery(
55+
newBufferedReader(
56+
newFileReader(
57+
"src/main/java/g3401_3500/"
58+
+"s3421_find_students_who_improved/script.sql"))
59+
.lines()
60+
.collect(Collectors.joining("\n"))
61+
.replaceAll("#.*?\\r?\\n",""))) {
62+
assertThat(resultSet.next(),equalTo(true));
63+
assertThat(resultSet.getNString(1),equalTo("101"));
64+
assertThat(resultSet.getNString(2),equalTo("Math"));
65+
assertThat(resultSet.getNString(3),equalTo("70"));
66+
assertThat(resultSet.getNString(4),equalTo("85"));
67+
assertThat(resultSet.next(),equalTo(true));
68+
assertThat(resultSet.getNString(1),equalTo("102"));
69+
assertThat(resultSet.getNString(2),equalTo("Math"));
70+
assertThat(resultSet.getNString(3),equalTo("80"));
71+
assertThat(resultSet.getNString(4),equalTo("85"));
72+
assertThat(resultSet.next(),equalTo(true));
73+
assertThat(resultSet.getNString(1),equalTo("104"));
74+
assertThat(resultSet.getNString(2),equalTo("Physics"));
75+
assertThat(resultSet.getNString(3),equalTo("75"));
76+
assertThat(resultSet.getNString(4),equalTo("85"));
77+
assertThat(resultSet.next(),equalTo(false));
78+
}
79+
}
80+
}
81+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp