출처

https://school.programmers.co.kr/learn/courses/30/lessons/299310

문제

풀이

WITH MAX_BY_YEAR AS (
    SELECT
    	YEAR(DIFFERENTIATION_DATE) AS YEAR,
    	MAX(SIZE_OF_COLONY) AS MAX_COL
    FROM ECOLI_DATA
    GROUP BY 1
)

SELECT
    m.YEAR,
    MAX_COL - SIZE_OF_COLONY AS YEAR_DEV,
    ID    
FROM
    ECOLI_DATA e LEFT JOIN
    MAX_BY_YEAR m ON
    YEAR(e.DIFFERENTIATION_DATE) = m.YEAR
ORDER BY 1, 2