[Leetcode] 1280. Students and Examinations

<문제>

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.

학생이 본 시험 횟수를 계산하는 문제.

 

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.

 

→ 모든 학생과 모든 과목이 포함된 집계를 해야함.

 

<테이블 예시>

# Students 테이블
+------------+--------------+
| 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              |
+------------+--------------+--------------+----------------+

 

처음 시도.

left join , group by 사용 

SELECT 
    s.student_id, 
    s.student_name, 
    e.subject_name, 
    COUNT(e.exam_id) AS attended_exams
FROM students s
LEFT JOIN examinations e
    ON s.student_id = e.student_id
GROUP BY 
    s.student_id, 
    s.student_name, 
    e.subject_name
ORDER BY 
    s.student_id, 
    e.subject_name;

 

틀렸던 이유:

subject 테이블에 따로 아이디가 없어 맵핑 하지 않음.

Examination테이블에 학생이 치루지 않은 과목의 경우 집계되지 않음.

 

해결:

Cross Join를 사용해 Student x Subjects 즉, 모든학생과 모든과목 조합생성한 뒤, 시험 본 횟수 집계

 

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 s.student_id = e.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,
    sub.subject_name;