MYSQL/leetcode

Students and Examinations

수스리 2025. 7. 28. 11:35

문제

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.

 

Table: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.

 

Table: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

 

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.

 

Example 1:

Input: 
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+
Output: 
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+
Explanation: 
The result table should contain all students and all subjects.
Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
Alex did not attend any exams.
John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.
SELECT
        S.student_id,
        S.student_name,
        sub.subject_name,
        COUNT(E.student_id) AS attended_exams
FROM
        Students S
CROSS JOIN
        Subjects Sub
LEFT JOIN
        Examinations E
        ON E.student_id = S.student_id AND Sub.subject_name = E.subject_name
GROUP BY
        S.student_id,
        S.student_name,
        sub.subject_name
ORDER BY
        S.student_id

 

회고

문제가 까다롭다. 구조 자체는 쉽다. JOIN을 어떻게 할지 무엇을 집계함수로 집계할지를 정확하게 써야한다. 이 부분이 어려웠다/

또한 CROSS JOIN도 내가 개념을 잘 몰라서 어렵게 느껴졌다. 
문제를 차근차근 해결해 보자.

 

핵심개념

여기서 핵심개념은 CROSS_JOIN 이다. 나는 CROSS JOIN을 하지않고 바로 LEFT JOIN을 했다. 그랬더니 시험 치는 과목은 JOIN 됐으나 그렇지 않은 과목은 누락 되어서 나왔다. 시험을 치지 않는 과목은 아예 COUNT 할 수 가 없었다.

 

SELECT
		*
FROM
        Students S
CROSS JOIN
        Subjects Sub

결과

student_id                          student_name                    subject_name

1 Alice Math
1 Alice Physics
1 Alice Programming
2 Bob Math
2 Bob Physics
2 Bob Programming
6 Alex Math
6 Alex Physics
6 Alex Programming
13 John Math
13 John Physics
13 John Programming

이렇듯 CROSS JOIN은 모든 경우의 수를 조합해 준다. CROSS JOIN을 쓰지 않았으면 시험 본는 과목만 JOIN 된다.

 

다음 쿼리

SELECT
        S.student_id,
        S.student_name,
        sub.subject_name,
        COUNT(E.student_id) AS attended_exams
FROM
        Students S
CROSS JOIN
        Subjects Sub
LEFT JOIN
        Examinations E
        ON E.student_id = S.student_id AND Sub.subject_name = E.subject_name

student_id    student_name               Su.subject_name                   E.student_id         E.subject_name

1 Alice Math 1 Math
1 Alice Math 1 Math
1 Alice Math 1 Math
1 Alice Physics 1 Physics
1 Alice Physics 1 Physics
1 Alice Programming 1 Programming
2 Bob Math 2 Math
2 Bob Physics NULL NULL
2 Bob Programming 2 Programming
6 Alex Math NULL NULL
6 Alex Physics NULL NULL
6 Alex Programming NULL NULL
13 John Math 13 Math
13 John Physics 13 Physics
13 John Programming 13 Programming

이렇듯 LEFT JOIN으로 시험 치지 않는 과목도 합쳐준다. 그리고 과목 이름도 합쳐줘야 한다. LEFT JOIN을 써도 학생 번호만 합쳐도 시험 치는 과목만 합쳐진다. 

그리고 학생별 과목별로 카운트를 진행한다. 카운트 할 때는 E.student_id로 해야한다. 처음에 S.studnet_id로 했는데 이러면 모든 수를 다 집계한다. E.student_id는 안치는 과목과 번호는 NULL이 라서 이 부분으로 카운트를 하면 정답이 해결된다.

 

EASY 치고는 어렵다고 생각한다. 하지만 CROSS_JOIN과 집계함수를 정확히 써야 한다는 교훈을 얻었다.