1WITH inflation_data AS 
2(
3  SELECT 
4    "year", 
5    "inflation_rate", 
6    EXP(SUM(LN(1 + "inflation_rate")) OVER (ORDER BY "year")) as "comp_inflation"
7  FROM @kaggle.prasertk_300_years_of_inflation_rate_in_us.inflation_data_1701_2022
8  WHERE "year" >= 1985
9  ORDER BY "year"
10)
11SELECT
12    fire_data."year",
13    fire_data."fires",
14    fire_data."acres",
15    CAST(REPLACE(REPLACE(fire_data."total", '$', ''), ',', '') AS BIGINT) AS "cost (nominal)",
16    CAST(REPLACE(REPLACE(fire_data."total", '$', ''), ',', '') AS BIGINT) / inflation_data."comp_inflation" * (SELECT "comp_inflation" FROM inflation_data WHERE "year" = 2020 ) AS "cost (inflation-adjusted)",
17    CAST(REPLACE(REPLACE(fire_data."doiagencies", '$', ''), ',', '') AS BIGINT) AS "department of interior costs",
18        fire_data."acres",
19    CAST(REPLACE(REPLACE(fire_data."forestservice", '$', ''), ',', '') AS BIGINT) AS "forestservice cost"
20FROM
21    @kaggle.kkhandekar_total_wildfires_acres_affected_1983_2020.federal_firefighting_costs_suppression_only AS fire_data
22INNER JOIN inflation_data
23    ON fire_data."year" = inflation_data."year"
24