[PGM] Calculating Yearly E. Coli Size Deviations (SQL)

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

• The ECOLI_DATA table contains each E. coli colony's ID, parent ID, size, differentiation date, and genotype.
• Write SQL to calculate the difference between each colony's size and the maximum size for its year.
• Sort by year ascending, then deviation ascending.

1. Start with the inner subquery.
   • Find the maximum colony size for each year in ECOLI_DATA.
   • Extract the year with YEAR(DIFFERENTIATION_DATE) and find the maximum size with MAX(SIZE_OF_COLONY).
   • Group by year using GROUP BY YEAR(DIFFERENTIATION_DATE).
2. Calculate each colony's size deviation in the outer query.
   • Extract the year from ECOLI_DATA with YEAR(DIFFERENTIATION_DATE) and get the colony's size from SIZE_OF_COLONY.
   • Use a JOIN to make the yearly maximum from the inner subquery available to the outer query.
   • Match the year with YEAR(t1.DIFFERENTIATION_DATE) = t2.YEAR.
   • Calculate the deviation with MAX_COLONY - SIZE_OF_COLONY.
3. Sort using ORDER BY YEAR, YEAR_DEV.

SELECT YEAR(t1.DIFFERENTIATION_DATE) as YEAR, (t2.MAX_COLONY - t1.SIZE_OF_COLONY ) as YEAR_DEV, t1.ID
FROM ECOLI_DATA as t1
JOIN (
    SELECT YEAR(DIFFERENTIATION_DATE) as YEAR, MAX(SIZE_OF_COLONY) as MAX_COLONY
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
) t2 ON YEAR(t1.DIFFERENTIATION_DATE) = t2.YEAR
ORDER BY YEAR, YEAR_DEV

• Use a subquery to find each year's maximum colony size.
• Use a JOIN to calculate the difference between that maximum and each colony's size.