LeetCode 코딩 테스트 - Find Students Who Improved(LV.Medium)

 

 

Table: Scores

+-------------+---------+
| 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's score in a specific subject on a particular exam date. score is between 0 and 100 (inclusive).

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:

• Have taken exams in the same subject on at least two different dates
• Their latest score in that subject is higher than their first score

Return the result table ordered by student_id, subject in ascending order.

The result format is in the following example.

 

Example:

Input:

Scores table:

+------------+----------+-------+------------+
| 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 |
+------------+----------+-------+------------+

Output:

+------------+----------+-------------+--------------+
| student_id | subject  | first_score | latest_score |
+------------+----------+-------------+--------------+
| 101        | Math     | 70          | 85           |
| 102        | Math     | 80          | 85           |
| 104        | Physics  | 75          | 85           |
+------------+----------+-------------+--------------+

Explanation:

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

Result table is ordered by student_id, subject.

 

# 쿼리를 작성하는 목표, 확인할 지표 : 성적이 오른 학생 찾기 / subject, score, exam_date
# 쿼리 계산 방법 : 1. 시험을 두 번 이상 본 학생만 추출 -> 2. 과목별 첫 시험의 성적과 비교해서 가장 최근에 본 성적이 더 높은 학생만 추출 -> 3. 정렬
# 데이터의 기간 : x
# 사용할 테이블 : scores
# JOIN KEY : student_id, subject
# 데이터 특징 : x
WITH twice AS (
    # 1
    SELECT
        student_id,
        subject,
        COUNT(*) AS subject_cnt
    FROM scores
    GROUP BY
        student_id, subject
    HAVING
        COUNT(*) > 1
), base AS (
    SELECT
        s.student_id,
        s.subject,
        s.score,
        s.exam_date
    FROM scores AS s
    # 1
    INNER JOIN twice AS t
    ON s.student_id = t.student_id AND
        s.subject = t.subject
)
# 2
SELECT
    DISTINCT
        student_id,
        subject,
        first_score,
        latest_score
FROM (
SELECT
    student_id,
    subject,
    FIRST_VALUE(score) OVER(PARTITION BY student_id, subject ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_score,
    LAST_VALUE(score) OVER(PARTITION BY student_id, subject ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_score
FROM base   
) AS a
WHERE
    first_score < latest_score
# 3
ORDER BY
    student_id, subject