1. "cte 구문에서 max(SIZEOFCOLONY) over(partition by year(DIFFERENTIATION_DATE) 를 하면
   집계 함수 오류가 발생하구요." 와 "partition by 를 했을 때 max(sizeofcolony) 를 집계 함수를 쓰더라도 group by 로 묶이지 않은 컬럼에 대해서 쓸 수 없는 로직을 잘 모르겠습니다." -- 라는 말씀은 WINDOW함수와 GROUP BY 를 함께 쓰시려고 하셨던것인지 헷갈립니다. 질문을 잘 이해하지 못했습니다ㅠㅠ... 혹시나 함께 쓰시려고 했다면 함께 동작하는 원리가 아니라는 것을 알아두시면 좋을 것 같습니다.
   
   SQL에서 실행순서는 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 이며, GROUP BY 에서 행을 줄여버리는 집계를 사용하게 됩니다. 따라서 WINDOW 함수를 함께 사용할 수 있는 상황이 되지 않습니다. 만약 GROUP BY 결과에 대해서 WINDOW 함수를 사용하려면 서브쿼리로 넣은 뒤 사용할 수 있겠죠!

2. 쓰신 쿼리를 통과하는 쿼리로 손보았습니다.
   크게 수정된 부분은 세 가지 입니다.

   • CTE의 GROUP BY가 날짜별로 되어있었습니다. 문제에서의 집계 기준은 연도입니다.
   • 따라서 연도 기준으로 메인 쿼리에서 계산할 때 쓰이기 때문에 SELECT 절에 (집계기준인) 연도가 있어야 하고, 연도가 GROUP BY절에 있기 때문에 SELECT 절에 먼저 써주어야합니다. 집계시 집계 순서대로 SELECT 절에 함께 있어야하는 것은 SQL에서 대부분 있는 상황이므로 참고하시면 좋을 것 같습니다.
   • 메인 쿼리에서의 조인을 바꾸었습니다. 지금 원하는 것은 id (원래 데이터 행)별로 맥스 크기에서 해당 행 데이터에 해당하는 대장균 크기를 빼는 것입니다. 따라서 원래 데이터를 좌측에 둔 LEFT JOIN을 사용해야 하며, 계산의 기준은 해당 연도이므로 연도를 조인 기준으로 넣었습니다. (외래키 조인에서 컬럼명이 서로다른 상황은 많기 때문에 USING보다는 ON을 사용하는 것을 권장하는 것 같습니다. 이점도 참고하시면 좋을 것 같습니다.)

with MAX_SIZE_YEAR as 
    (select YEAR(DIFFERENTIATION_DATE) AS YEAR,
            max(SIZE_OF_COLONY) as MAX_SIZE
       from ECOLI_DATA
      group by YEAR(DIFFERENTIATION_DATE))

select extract(year from DIFFERENTIATION_DATE) as YEAR,
        (MAX_SIZE - SIZE_OF_COLONY) as YEAR_DEV,
        ID
  from ECOLI_DATA e
       LEFT JOIN MAX_SIZE_YEAR ON extract(year from e.DIFFERENTIATION_DATE) = MAX_SIZE_YEAR.YEAR
 order by YEAR, YEAR_DEV asc;

1. 또한 max(SIZE_OF_COLONY) over(partition by year(DIFFERENTIATION_DATE) 쓰신 컬럼 자체는 잘못된 부분이 없습니다. 저도 마찬가지로 풀었구요! 이것을 사용하는 예시해답을 보여드리겠습니다.
   

SELECT  YEAR(differentiation_date) AS YEAR
      , MAX(size_of_colony) OVER (PARTITION BY YEAR(differentiation_date))   -- here!
            - size_of_colony AS YEAR_DEV
      , id
  FROM  ECOLI_DATA
 ORDER  BY 1, 2;

도움이 되셨으면 좋겠습니다...!