Advented of SQL 2024 : 3년간 들어온 소장품 집계하기(DAY 12)

Hyeon·2024년 12월 12일

SQL 문제 풀이

목록 보기

48/61

문제

Museum of Modern Art Collection 데이터베이스에는 뉴욕 현대 미술관에 소장된 소장품과 그 작가 정보가 들어있습니다. 소장품 정보를 담고 있는 artworks 테이블은 소장품의 소장 일시(acquisition_date)와 소장품의 분류(classification) 정보가 들어있습니다. 이 정보를 바탕으로 2014년부터 2016년까지 3년간 어떤 분류의 소장품이 많이 추가되었는지 알고자 합니다.

아래와 예시와 같은 형태로 각 분류에 대해 연도별 추가된 소장품 수를 집계하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함해야하고, 컬럼 순서 역시 아래 예시 순서와 동일해야하며, 각 행은 분류(classification) 컬럼 기준으로 오름차순 정렬되어 있어야 합니다. 또한, 집계하는 3년간 추가된 특정 분류의 소장품이 없더라도 해당 분류와 집계 내역을 결과 테이블에서 누락시키지 말고 포함해주세요.

컬럼설명

classification: 소장품 분류
2014: 2014년
2015: 2015년
2016: 2016년

결과 테이블 예시

정답 코드


-- step 1. classifcation 모든 값 출력하기 (소장품이 없더라도 집계 내역을 결과 테이블에 누락시키면 안된다)

with cte_1 as (select distinct classification from artworks),
cte_2 as (

-- step 2-2. classification 및 각 연도별 개수 카운팅 합계

select classification,
sum(case when year_date = '2014' then counting else 0 end) as c_2014,
sum(case when year_date = '2015' then counting else 0 end) as c_2015,
sum(case when year_date = '2016' then counting else 0 end) as c_2016
from

-- step 2-1. classfication 및 연도별 counting 

(select strftime('%Y',acquisition_date) as year_date, classification ,count(artwork_id) as counting
from artworks 
group by strftime('%Y',acquisition_date),classification
having strftime('%Y',acquisition_date) in ('2014','2015','2016')) t
group by classification
order by 1 asc
)

-- step 3-1. step 1 과 step 2 데이터 조인, 이때 null값이어도 0으로 카운팅되어야하므로 ifnull사용
-- step 3-2. 오름차순 정렬이므로 order by 1 asc 

select c1.classification , ifnull(c_2014, 0) as '2014', ifnull(c_2015, 0) as '2015', ifnull(c_2016, 0) as '2016'
from cte_1 c1 left join cte_2 c2 on c1.classification = c2.classification
order by 1 asc;