[SQL50] 1280. Students and Examinations

난이도: EASY

 

문제 링크: https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50

문제

Table: Students
+---------------+---------+
| 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.

---

 

시도 1

SELECT p.student_id, 
       p.student_name, 
       s.subject_name, 
       COUNT(e.student_id) AS attended_exams 

FROM Students p
    LEFT JOIN Examinations e ON p.student_id = e.student_id  
    LEFT JOIN Subjects s ON s.subject_name = e.subject_name 

GROUP BY p.student_id, p.student_name, s.subject_name
ORDER BY p.student_id, s.subject_name

 

처음 시도 결과 (실패)

 

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          | Programming  | 1              |
| 6          | Alex         | null         | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |

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

- 보지않은 과목이있거나 아무 시험도 안본경우 출력이 안 됨.

  => Students 테이블과 Subjects 테이블 Cross Join하게 수정

 

- 안 본 경우 0이 아니고 NULL로 표시 

  => COALESCE 로 처리되게 수정

---

 

나의 최종 제출 답안:

SELECT p.student_id, 
       p.student_name, 
       s.subject_name, 
       COALESCE(COUNT(e.student_id), 0) AS attended_exams 

FROM Students p
    CROSS JOIN Subjects s
    LEFT JOIN Examinations e 
        ON p.student_id = e.student_id  
        AND s.subject_name = e.subject_name
    
GROUP BY p.student_id, p.student_name, s.subject_name
ORDER BY p.student_id, s.subject_name

 

---

 

chatGPT에게 더 간단하고 효율적이게 수정해달라고 요청한 답안:

 

제시해주신 쿼리는 문제 요구사항(모든 학생과 모든 과목의 조합에 대해 해당 학생이 해당 과목의 시험에 참석한 횟수를 출력)을 올바르게 충족시키며, 데이터량이 적당한 경우에는 효율적입니다.

 

하지만 만약 Examinations 테이블의 데이터가 많다면, 먼저 해당 테이블에서 학생별, 과목별 참석 횟수를 집계한 서브쿼리를 만든 후 이를 Students와 Subjects의 CROSS JOIN 결과와 LEFT JOIN하는 방식이 조금 더 효율적일 수 있습니다. 이렇게 하면 불필요한 중간 결과(즉, 학생-과목 조합마다 매번 Examinations 테이블을 스캔하는 것)를 줄일 수 있습니다.

 

예를 들어, 다음과 같이 작성할 수 있습니다:

SELECT s.student_id,
       s.student_name,
       sub.subject_name,
       NVL(e.attended_exams, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN (
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
) e ON s.student_id = e.student_id
     AND sub.subject_name = e.subject_name
ORDER BY s.student_id, sub.subject_name;

 

이 방식은

• 서브쿼리: Examinations 테이블에서 학생과 과목별 참석 횟수를 미리 계산합니다.
• CROSS JOIN: 모든 학생과 과목의 조합을 만듭니다.
• LEFT JOIN: 미리 집계된 결과와 연결하여 각 조합의 참석 횟수를 가져옵니다.

데이터 규모가 크거나 인덱스가 적절히 설정되어 있지 않은 경우, 이렇게 미리 집계하는 방식이 성능 개선에 도움이 될 수 있습니다.
그러나 데이터량이 적은 상황에서는 제시해주신 쿼리도 충분히 효율적입니다.