Advented of SQL 2024 : 유량(Flow)와 저량(Stock) (DAY 23)

문제 탐구

미술관의 소장 규모를 파악하기 위해 연도별로 새롭게 소장하게 된 작품의 수와, 연도별 누적 소장 작품 수를 계산하는 쿼리를 작성해주세요.

• 저량 지표에 변화가 없는 연도는 출력되지 않아야 하고, 소장 년도 정보가 없는 작품은 집계에서 제외해주세요.

• Acquisition year 컬럼을 기준으로 오름차순 정렬되어 있어야 합니다.

📍출력 컬럼
Acquisition year: 소장 연도
New acquisitions this year (Flow): 해당 연도에 새롭게 소장하게 된 작품의 수
Total collection size (Stock): 해당 연도까지 누적 소장 작품 수

풀이 과정

1번째 시도

-- 제출은 1931, 정답은 1929 (2번째 레코드 1번째 컬럼 값이 다르다)

with cte_1 as (select strftime('%Y',acquisition_date) as ac_year, count(artwork_id) as counting
from artworks
where ac_year is not null
group by strftime('%Y',acquisition_date)),
cte_2 as (select *, sum(counting) over (order by counting) as counting_1 from cte_1)
select ac_year as 'Acquisition year',counting as 'New acquisitions this year (Flow)',counting_1 as 'Total collection size (Stock)'
from cte_2;

📌오래된 연도별 순서대로 출력하기
->order by 1 asc

2번째 시도

-- 2번째 레코드, 3번째 컬럼의 값이 서로 다릅니다. (제출: 21, 정답: 10)

with cte_1 as (select strftime('%Y',acquisition_date) as ac_year, count(artwork_id) as counting
from artworks
where ac_year is not null
group by strftime('%Y',acquisition_date)),
cte_2 as (select *, sum(counting) over (order by counting) as counting_1 from cte_1)
select ac_year as 'Acquisition year',counting as 'New acquisitions this year (Flow)',counting_1 as 'Total collection size (Stock)'
from cte_2 order by 1 ;

📌order by를 counting 기준으로 해서 연도별 누적으로 구해지지 않았다.
order by 연도로 해야함
-> order by ac_year로 변경

3번째 시도(정답)

with cte_1 as (select strftime('%Y',acquisition_date) as ac_year, count(artwork_id) as counting
from artworks
where ac_year is not null
group by strftime('%Y',acquisition_date)),
cte_2 as (select *, sum(counting) over (order by ac_year) as counting_1 from cte_1 )
select ac_year as 'Acquisition year',counting as 'New acquisitions this year (Flow)',counting_1 as 'Total collection size (Stock)'
from cte_2 
order by 1 ;