[leetcode | CROSS JOIN] Students and Examinations

💡문제 설명

1. Students, Subjects, 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.

The result format is in the following example.

 

이 문제는 각 학생이 각 시험에 몇 번 참석했는지를 구하는 문제입니다. 예를 들어, 학생이 특정 과목의 시험에 여러 번 응시했다면, 그 횟수를 세어 결과로 반환해야 합니다.

 

💡Output

 

💡문제 풀이

• 시험을 보지 않은 과목도 attended_exams 컬럼에 0으로 표시가 되어야 하므로, Students 테이블과 Examinations 테이블을 바로 JOIN 하면 안됨.
• CROSS JOIN 을 해줘야 함. 
  • CROSS JOIN ? 
  • 두 테이블 간의 **카티션 곱(Cartesian product)**을 반환하는 조인 방식입니다. 이것은 첫 번째 테이블의 각 행이 두 번째 테이블의 모든 행과 결합되는 것을 의미
  • EX) 
  • Students Table + Subjects Table

students table

subjects table

cross join students table + subjects table

CTE + Examinations 테이블 left join 결과

최종 테이블

 

# find the number of times each student attended each exam.
# ordered by student_id and subject_name. 

# CROSS JOIN 이 필요
# 세 개 join 해야할 때, CTE 사용하면 편리하다.
with StudentSubjects as (
	select 
		s.student_id, 
		s.student_name, 
		sub.subject_name
	from 
		Students s
	cross join
		Subjects sub
) -- CTE
select 
	ss.student_id, 
	ss.student_name,
	ss.subject_name,
	coalesce(count(e.student_id), 0) as attended_exams -- 결측값 0으로 대체
from 
	StudentSubjects ss
left join 
	Examinations e
on 
	ss.student_id = e.student_id 
	and ss.subject_name = e.subject_name
group by 
	ss.student_id, ss.student_name, ss.subject_name
order by 
	ss.student_id, ss.subject_name;

 

• 이후 Examinations 테이블을 left join 으로 합쳐준다. 
• 합친 후, student_id, student_name, subject_name 으로 group by 를 해준 후 각 그룹별 카운트를 세어줌으로써 학생, 과목 별 카운트를 집계한다. 이 때, coalesce 내장 함수를 사용해서 결측값을 0 으로 대체