[SQL] solvesql Advent of SQL 2024 23일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 23일차 - 유량(Flow)와 저량(Stock):https://solvesql.com/problems/flow-and-stock/

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

 

 

# 풀이

WITH
  acquisition_data AS (
    SELECT
      strftime ('%Y', acquisition_date) AS acquisition_year,
      COUNT(*) AS new_acquisitions
    FROM
      artworks
    WHERE
      acquisition_date IS NOT NULL
    GROUP BY
      acquisition_year
  ),
  accumulated_data AS (
    SELECT
      acquisition_year,
      new_acquisitions,
      SUM(new_acquisitions) OVER (
        ORDER BY
          acquisition_year ROWS BETWEEN UNBOUNDED PRECEDING
          AND CURRENT ROW
      ) AS total_collection
    FROM
      acquisition_data
  )
SELECT
  acquisition_year AS "Acquisition year",
  new_acquisitions AS "New acquisitions this year (Flow)",
  total_collection AS "Total collection size (Stock)"
FROM
  accumulated_data
ORDER BY
  acquisition_year

• acquisition_data CTE
  
  • SELECT
    
    • strftime ('%Y', acquisition_date) AS acquisition_year: acquisition_date의 연도를 추출하여 acquisition_year로 저장한다.
    • COUNT(*) AS new_acquisitions: 데이터의 개수를 계산하여 new_acquisitions로 저장한다.
  • FROM artworks: artworks 테이블에서 데이터를 가져온다.
  • WHERE acquisition_date IS NOT NULL: acquisition_date의 값이 NULL이 아닌 데이터만 선택한다.
  • GROUP BY acquisition_year: acquisition_year 컬럼을 기준으로 그룹화한다.
• accumulated_data CTE
  
  • SELECT
    
    • acquisition_year: acquisition_year 컬럼을 선택한다.
    • new_acquisitions: new_acquisitions 컬럼을 선택한다.
    • SUM(new_acquisitions) OVER (ORDER BY acquisition_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_collection: acquisition_year 컬럼을 기준으로 오름차순 정렬하고, 맨 처음 행부터 현재 행까지의 new_acquisitions의 누적 합계를 계산하여 total_collection으로 저장한다.
  • FROM acquisition_data: acquisition_data CTE에서 데이터를 가져온다.
• SELECT
  
  • acquisition_year AS "Acquisition year": acquisition_year 컬럼을 'Acquisition year'로 저장한다.
  • new_acquisitions AS "New acquisitions this year (Flow)": new_acquisitions 컬럼을 'New acquisitions this year (Flow)'로 저장한다.
  • total_collection AS "Total collection size (Stock)": total_collection 컬럼을 'Totla collection size (Stock)'로 저장한다.
• FROM accumulated_data: accumulated_data CTE에서 데이터를 가져온다.
• ORDER BY acquisition_year: acquisition_year 컬럼을 기준으로 오름차순 정렬한다.