구름 아카이브

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

12. 3년간 들어온 소장품 집계하기

https://solvesql.com/problems/summary-of-artworks-in-3-years/

WITH 3years_data AS (
    SELECT
        LEFT(acquisition_date,4) AS acquisition_date,
        classification
    FROM artworks
)
SELECT
    classification,
    -- COUNT(CASE WHEN acquisition_date = '2014' THEN 1 ELSE NULL END) AS '2014',
    -- COUNT(CASE WHEN acquisition_date = '2015' THEN 1 ELSE NULL END) AS '2015',
    -- COUNT(CASE WHEN acquisition_date = '2016' THEN 1 ELSE NULL END) AS '2016'
    SUM(CASE WHEN acquisition_date = '2014' THEN 1 ELSE 0 END) AS '2014',
    SUM(CASE WHEN acquisition_date = '2015' THEN 1 ELSE 0 END) AS '2015',
    SUM(CASE WHEN acquisition_date = '2016' THEN 1 ELSE 0 END) AS '2016'

FROM 3years_data
GROUP BY classification
ORDER BY classification ASC