[SQL문제풀기] 3년간 들어온 소장품 집계하기

문제

solvesql.com

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

SQLite

내가 작성한 정답

with a as (select classification, strftime('%Y',acquisition_date) year, count(artwork_id) c
          from artworks
          where strftime('%Y',acquisition_date) between '2014' and '2016'
          group by strftime('%Y',acquisition_date), classification),
    b as (select classification,
                case when year = '2014' then c end "y14",
                case when year = '2015' then c end "y15",
                case when year = '2016' then c end "y16"
          from a )
select a.classification, 
      ifnull(sum(b.y14),0) "2014", 
      ifnull(sum(b.y15),0) "2015", 
      ifnull(sum(b.y16),0) "2016"
from b b
right join (select distinct classification from artworks) a on b.classification = a.classification
group by a.classification;

지난 번과 같은 실수함

distinct 적용하지 않은 결과

distinct 적용한 결과

with a as (select classification, strftime ('%Y', acquisition_date) year, count(artwork_id) num
          from artworks
          where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016')
          group by classification, strftime ('%Y', acquisition_date)), 
      b as (select classification, 
            case year when '2014' then num else null end b2014,
            case year when '2015' then num else null end b2015,
            case year when '2016' then num else null end b2016
          from a)
select a.classification, ifnull(sum(b.b2014),0) '2014', 
      ifnull(sum(b.b2015),0) '2015', ifnull(sum(b.b2016),0) '2016'
from b b
right join (select distinct classification from artworks) a on b.classification= a.classification
group by a.classification