[Leet Code] 1280. Students and Examinations

Students and Examinations

각 학생이 각 시험에 참석한 횟수를 찾는 솔루션을 작성하세요. 결과 테이블은 학생 ID와 과목명 순으로 정렬되어야 합니다. 결과는 다음 예시와 같은 형식으로 반환됩니다.

 

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

 

문제 풀이

해당 문제는 모든 student_id과 모든 subject_name에 대하여 구해야 한다는 조건이 중요합니다. 하지만 Examinations 테이블에는 없는 subject_name이 있기 때문에 Students와 Subjects 테이블의 Cross join이 필요합니다.

 

따라서,

1. Students와 Subjects 테이블의 Cross Join
2. Examinations 테이블 Left Join student_id과 subject_name 기준
3. Group By를 이용하여 subject_name 의 수 구하기
4. Order By를 이용하여 순서 정하기

최종 코드

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