leetcode 1280. Students and Examinations (cross join)

문제 링크

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

Students and Examinations - LeetCode

easy로 된 문제이지만, 데이터를 집계하는데 생각해볼만한 부분이 있어서 간단하게 남겨놓으려고 작성합니다.

join문제를 만나면 주로 inner join, left join 을 많이 사용하는데 이번 문제에서는 cross join을 사용하게 됐습니다!

테이블 

1. Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+

2. Subjects 

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+

3. Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+

 

문제

학생들이 각 과목을 몇 번 시험을 쳤는지 집계하세요.

 

풀이]

1) 1차 풀이 및 회고

처음엔 students 테이블과 examinations 테이블을 조인하고 group by 로 집계했는데, 틀렸다고 떴다.

subjects 테이블을 전혀 사용하지 않았는데, 가끔 주어진 테이블을 사용하지 않고도 풀수있는 문제들이 종종 있어서 그런 케이스라고 생각했다.

기술 된 문제가 "Write a solution to find the number of times each student attended each exam." 이렇게 되어있어서 확인하지 못한 조건이 있었다.

아래 예시 output에서 Alex와 Bob의 physics 시험 횟수를 보면 0으로 집계되어 있다. 

이 문제에서 원하는건, 각 학생이 본 시험의 수만 집계하는것이 아닌 전체 과목에 대해서 시험을 본 횟수 인것이다. (0회 포함)

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

여기서 왜 subjects 테이블이 주어졌는지 알 수 있었다.

Examinations 테이블에 Alex의 컬럼이 없기 때문에 , students 테이블에 left join 해줄 경우, math, physics, programming 에 대한 행이 생기지 않는다. 

 

* students, Examinations 테이블만 조인해서 집계한 경우 결과 테이블

시험 결과가 없는 alex의 경우에 subject_name이 null값으로 되어있다.

심지어 count(*)로 했더니, attended_exams가 1로 잘못 집계되었다ㅠ

2) 풀이 수정

students 테이블과 subjects 테이블을 cross join해줘서 각 학생에게 전체 과목을 붙여주는 작업이 필요하다.

출처: 한빛

이제 시험을 보지 않은 Alex도 programming, physics, math에 대해 행이 생긴것을 확인할 수 있다.

Examinations 과 join하여 group by 로 집계해주면, 전체 학생의 전체 과목에 대한 시험 횟수를 집계할 수 있다.

 

* 정답 코드

select s1.student_id, s1.student_name, s2.subject_name, count(e.student_id) as attended_exams
from students as s1
cross join subjects as s2
left join Examinations as e on s1.student_id = e.student_id and s2.subject_name = e.subject_name
group by 1,2,3
order by 1,3