Year-over-year growth in SQL

year_over_year_growth.sql

-- =========================================
-- simple year-over-year
-- =========================================

SELECT
SUM(CASE WHEN year = 2008 THEN precipitation ELSE 0 END) AS cy_precipitation,
SUM(CASE WHEN year = 2009 THEN precipitation ELSE 0 END) AS py_precipitation,
SUM(CASE WHEN year = 2008 THEN precipitation ELSE 0.0 END) / SUM(CASE WHEN year = 2009 THEN precipitation ELSE 0.0 END) - 1.0  as change

FROM station_data
WHERE year BETWEEN 2008 AND 2009;

-- =========================================
--all years, approach 1, fastest
-- =========================================

SELECT s1.year,
s1.total_precipitation as cy_precipitation,
s2.total_precipitation as py_precipitation,
(s1.total_precipitation / s2.total_precipitation) - 1.0 AS diff

FROM
(
    SELECT year,
    SUM(precipitation) AS total_precipitation
    FROM station_data 
    GROUP BY year
) s1
INNER JOIN
(
    SELECT year,
    SUM(precipitation) AS total_precipitation
    FROM station_data 
    GROUP BY year
) s2
       
ON (s1.year - 1) = s2.year;

-- =========================================
-- all years, approach 2,  slower
-- =========================================

SELECT year,
cy_precipitation,
py_precipitation,
(cy_precipitation / py_precipitation) - 1.0 AS diff

FROM ( 

SELECT year,
    SUM(precipitation) AS cy_precipitation,
    (
        SELECT SUM(precipitation) 
        FROM station_data s2 
        WHERE s2.year = s1.year - 1
    ) py_precipitation
    
    FROM station_data s1
    
GROUP BY 1
);