구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

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

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

-- 첫번째 풀이


WITH yearly_count AS (
    SELECT 
      EXTRACT(YEAR FROM acquisition_date) AS acquisition_year,
      COUNT(*) AS Flow
    FROM artworks
    WHERE EXTRACT(YEAR FROM acquisition_date) IS NOT NULL
    GROUP BY EXTRACT(YEAR FROM acquisition_date)
)

, Cumulative AS (
    SELECT
      Acquisition_year,
      Flow,
      SUM(Flow) OVER (ORDER BY Acquisition_year) AS Cumulative_Sum
    FROM yearly_count
)

SELECT
  Acquisition_year AS "Acquisition year",
  Flow AS "New acquisitions this year (Flow)",
  Cumulative_Sum AS "Total collection size (Stock)"
FROM Cumulative
WHERE Flow > 0
ORDER BY Acquisition_year






-- 두번째 풀이

WITH yearly_count AS (
    SELECT 
      LEFT(acquisition_date, 4) AS Acquisition_year,
      COUNT(*) AS Flow
    FROM artworks
    WHERE LEFT(acquisition_date, 4) IS NOT NULL
    GROUP BY LEFT(acquisition_date, 4)
)

, cumulative_stock AS (
    SELECT
      Acquisition_year,
      Flow,
      SUM(Flow) OVER (ORDER BY Acquisition_year) AS Cumulative_Sum
    FROM yearly_count
)

, comparison_prev_year AS (
    SELECT
      Acquisition_year,
      Flow AS Flow,
      Cumulative_Sum AS Stock,

      CASE WHEN LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year) IS NULL THEN 0 
      ELSE LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year)
      END AS LAG_Cumulative_Sum,

      CASE WHEN Cumulative_Sum = LAG(Cumulative_Sum) OVER(ORDER BY Acquisition_year) THEN 'target' 
      ELSE 'non_target'
      END AS comparison

    FROM cumulative_stock
)

SELECT
  Acquisition_year AS "Acquisition year",
  Flow AS "New acquisitions this year (Flow)",
  Stock AS "Total collection size (Stock)"
FROM comparison_prev_year
WHERE comparison = 'non_target'
ORDER BY Acquisition_year