1280. Students and Examinations

1280. Students and Examinations

문제

1. 각 학생이 각 시험에 참석한 횟수를 구하는 쿼리 작성
2. student_id, subject_name 정렬

입력 테이블

1. Students
   • student_id
   • student_name
2. Subjects
   • subject_name
3. Examinations
   • student_id
   • subject_name

풀이

1. JOIN 활용

Students와 Subjects를 Cross Join

select *
from Students e
    cross join subjects s

2. GROUP BY 및 집계 함수 사용

Count()를 사용해 과목 개수 세기

select
	student_id, 
    subject_name,
    count(*) as attended_exams
from Examinations
group by student_id, subject_name

3. LEFT JOIN 활용 및 과목 없는 Null값 -> 0으로

select 
t1.student_id, 
t1.student_name, 
t1.subject_name, 
coalesce(t2.attended_exams, 0) as attended_exams
from temp_01 t1
left join temp_02 t2 
on t1.student_id = t2.student_id 
and t1.subject_name = t2.subject_name
order by t1.student_id, t1.subject_name

4.전체 코드

with temp_01 as (
    select *
    from Students e
        cross join subjects s
), temp_02 as (
    select student_id, subject_name, count(*) as attended_exams
    from Examinations
    group by student_id, subject_name
)
select 
	t1.student_id, 
    t1.student_name, 
    t1.subject_name, 
    coalesce(t2.attended_exams, 0) as attended_exams
from temp_01 t1
left join temp_02 t2 
	on t1.student_id = t2.student_id and t1.subject_name = t2.subject_name
order by t1.student_id, t1.subject_name