[프로그래머스 | SQL 고득점 Kit | max, join] 연도별 대장균 크기의 편차 구하기

 

SELECT CONVERT(DATE_FORMAT(e1.DIFFERENTIATION_DATE,'%Y'), SIGNED) AS YEAR
     , e2.BIG - e1.SIZE_OF_COLONY AS YEAR_DEV
     , e1.ID
FROM ECOLI_DATA e1
        JOIN
     (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') AS YEARs
           , MAX(SIZE_OF_COLONY) AS BIG
      FROM ECOLI_DATA 
      GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')) e2 
      ON DATE_FORMAT(e1.DIFFERENTIATION_DATE,'%Y') = e2.YEARs
ORDER BY YEAR ASC, YEAR_DEV ASC