SQL | SOVESQL 2024 🎄: Day 23. 유량(Flow)와 저량(Stock)

Day 23. 유량(Flow)와 저량(Stock)

https://solvesql.com/problems/flow-and-stock/

select year(acquisition_date) as 'Acquisition year'
  , count(artwork_id) as 'New acquisitions this year (Flow)'
  , sum(count(artwork_id)) over 
      (order by year(acquisition_date) rows unbounded preceding) as 'Total collection size (Stock)'
from artworks
where acquisition_date is not null
group by year(acquisition_date)

❗COUNT() 함수와 윈도우 함수 사용해서 개수 세기
❗ROWS UNBOUNDED PRECEDING 사용해서 누적합 구하기
❗GROUP BY 사용해서 년도 기준으로 그룹화