Baselight
WITH filtered_inflation AS
(
  SELECT 
    "year", 
    inflation_rate
  FROM 
    @kaggle.prasertk_300_years_of_inflation_rate_in_us.inflation_data_1701_2022
),
cumulative_inflation AS 
(
  SELECT 
    "year", 
    inflation_rate, 
    EXP(SUM(LN(1 + inflation_rate)) OVER (ORDER BY "year")) as comp_inflation
  FROM filtered_inflation
  WHERE "year" >= 1985
  ORDER BY "year"
)
SELECT
    fire_data."year",
    fire_data."doiagencies",
    fire_data."acres",
    CAST(REPLACE(REPLACE(fire_data."total", '$', ''), ',', '') AS BIGINT) AS "total_cost",
    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",
    fire_data."fires",
    cumulative_inflation.inflation_rate,
    cumulative_inflation.comp_inflation
FROM
    @kaggle.kkhandekar_total_wildfires_acres_affected_1983_2020.federal_firefighting_costs_suppression_only AS fire_data
INNER JOIN cumulative_inflation
    ON fire_data."year" = cumulative_inflation."year"
yeardoiagenciesacrestotal_costtotal_cost_normalisedfiresinflation_ratecomp_inflation
1985$78,438,0002896147239943000576544613.2501694825910.041.04
1986$91,153,0002719162202778000477689282.8453737859070.021.0608
1987$81,452,0002447296335109000759062287.7321994713000.041.103232
1988$149,317,00050092905789260001260901143.0369592727500.041.1473612800000001
1989$168,115,00018273104997870001036701293.3061972489490.051.2047293440000002
1990$144,252,0004621621397952000786158433.1568824664810.051.2649658112000002
1991$73,820,0002953578206120000391531024.04091793757540.041.3155644436480003
1992$87,166,0002069929377466000696124086.0759265873940.031.3550313769574405
1993$56,436,0001797574240436000430497953.57316434588100.031.3956823182661637
1994$161,135,00040735799183350001596377172.871435791070.031.4375527878141487
1995$110,126,0001840546477126000805249068.0378724822340.031.480679371448573
1996$153,683,00060659987011830001148923998.9363487963630.031.5250997525920305
1997$105,048,0002856959284148000456461711.98932296661960.021.555601747643871
1998$109,904,0001329704416704000656277097.6323539810430.021.5867137825967483
1999$154,416,0005626093515516000795978902.0857626924870.021.6184480582486833
2000$334,802,000738349314108020002114892268.974048922500.031.6670014999961438
2001$269,574,00035709119526960001386562020.009812840790.031.7170115449960281
2002$395,040,000718471216740400002388639542.133665734570.021.751351775895949
2003$303,638,000396084213271380001856524561.6148207636290.021.786378811413868
2004$281,244,000809788010072440001367987342.657921654610.031.8399701757562839
2005$294,054,00086893898189540001079865518.4745345667530.031.8951692810289724
2006$424,058,000987374517044770002182046925.2299595963850.031.9520243594598417
2007$470,491,000932804516201450002013676031.5566049857050.032.010585090243637
2008$392,783,000529246815858560001895248345.9459064789790.042.091008493853382
2009$218,418,00059217869205290001100119471.53161387879202.091008493853382
2010$231,214,0003422724809499000948458911.5791347719710.022.13282866373045
2011$318,789,000871136713745250001563571059.5855062741260.032.196813523642364
2012$465,832,000932623819024460002121666634.9439132677740.022.240749794115211
2013$399,199,000431954617409340001922320253.3331928475790.012.263157292056363
2014$326,194,000359561315221490001647784561.5814393632120.022.3084204378974906
2015$417,543,0001012514921305430002306394356.39047436815102.3084204378974906
2016$371,739,000550353819755450002117428799.9436002675950.012.3315046422764656
2017$508,000,0001002608629181650003066419454.66714990.022.378134735121995
2018$528,000,000876749231432560003238182331.2000003580830.022.4256974298244347
2019$440,000,000466436415900000001605899999.9999998504770.022.474211378420924
2020$510,000,0001012233622740000002274000000589500.012.498953492205133

Share link

Anyone who has the link will be able to view this.