find-students-who-improved

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code>Scores</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
+-------------+---------+
(student_id, subject, exam_date) is the primary key for this table.
Each row contains information about a student&#39;s score in a specific subject on a particular exam date. score is between 0 and 100 (inclusive).
</pre>

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:

<ul>
	<li>Have taken exams in the same subject on at least two different dates</li>
	<li>Their latest score in that subject is higher than their first score</li>
</ul>

Return the result table&nbsp;ordered by <code>student_id,</code> <code>subject</code> in ascending order.

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

Scores table:

<pre class="example-io">
+------------+----------+-------+------------+
| student_id | subject | score | exam_date |
+------------+----------+-------+------------+
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
+------------+----------+-------+------------+
</pre>

Output:

<pre class="example-io">
+------------+----------+-------------+--------------+
| student_id | subject | first_score | latest_score |
+------------+----------+-------------+--------------+
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
+------------+----------+-------------+--------------+
</pre>

Explanation:

<ul>
	<li>Student 101 in Math: Improved from 70 to 85</li>
	<li>Student 101 in Physics: No improvement (dropped from 65 to 60)</li>
	<li>Student 102 in Math: Improved from 80 to 85</li>
	<li>Student 103 in Math: Only one exam, not eligible</li>
	<li>Student 104 in Physics: Improved from 75 to 85</li>
</ul>

Result table is ordered by student_id, subject.
</div>

find_students_who_improved

Find Students Who Improved

表：<code>Scores</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
+-------------+---------+
(student_id, subject, exam_date) 是这张表的主键。
每一行包含有关学生在特定考试日期特定科目成绩的信息。分数范围从 0 到 100（包括边界）。
</pre>

编写一个解决方案来查找 进步的学生。如果 同时 满足以下两个条件，则该学生被认为是进步的：

<ul>
	<li>在 同一科目 至少参加过两个不同日期的考试。</li>
	<li>他们在该学科 最近的分数 比他们 第一次该学科考试的分数更高。</li>
</ul>

返回结果表以&nbsp;<code>student_id</code>，<code>subject</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

Scores 表：

<pre>
+------------+----------+-------+------------+
| student_id | subject | score | exam_date |
+------------+----------+-------+------------+
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
+------------+----------+-------+------------+</pre>

出：

<pre class="example-io">
+------------+----------+-------------+--------------+
| student_id | subject | first_score | latest_score |
+------------+----------+-------------+--------------+
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
+------------+----------+-------------+--------------+
</pre>

解释：

<ul>
	<li>学生 101 的数学：从 70 分进步到 85 分。</li>
	<li>学生 101 的物理：没有进步（从 65 分退步到 60分）</li>
	<li>学生 102 的数学：从 80 进步到 85 分。</li>
	<li>学生 103 的数学：只有一次考试，不符合资格。</li>
	<li>学生 104 的物理：从 75 分进步到 85 分。</li>
</ul>

结果表以 student_id，subject 升序排序。
</div>