1WITH filtered_inflation AS
2(
3  SELECT 
4    "year", 
5    inflation_rate
6  FROM 
7    @kaggle.prasertk_300_years_of_inflation_rate_in_us.inflation_data_1701_2022
8),
9cumulative_inflation AS 
10(
11  SELECT 
12    "year", 
13    inflation_rate, 
14    EXP(SUM(LN(1 + inflation_rate)) OVER (ORDER BY "year")) as comp_inflation
15  FROM filtered_inflation
16  WHERE "year" >= 1985
17  ORDER BY "year"
18)
19SELECT
20    fire_data."year",
21    fire_data."doiagencies",
22    fire_data."acres",
23    CAST(REPLACE(REPLACE(fire_data."total", '$', ''), ',', '') AS BIGINT) AS "total_cost",
24    CAST(REPLACE(REPLACE(fire_data."total", '$', ''), ',', '') AS BIGINT) / cumulative_inflation.comp_inflation * (SELECT comp_inflation FROM cumulative_inflation WHERE "year" = 2020 ) AS "total_cost_normalised",
25    fire_data."fires",
26    cumulative_inflation.inflation_rate,
27    cumulative_inflation.comp_inflation
28FROM
29    @kaggle.kkhandekar_total_wildfires_acres_affected_1983_2020.federal_firefighting_costs_suppression_only AS fire_data
30INNER JOIN cumulative_inflation
31    ON fire_data."year" = cumulative_inflation."year"