WITH inflation_t AS 
(
  SELECT 
    "year", 
    "inflation_rate", 
    EXP(SUM(LN(1 + "inflation_rate")) OVER (ORDER BY "year")) as "comp_inflation"
  FROM @kaggle.prasertk_300_years_of_inflation_rate_in_us.inflation_data_1701_2022
  WHERE "year" >= 1985
  ORDER BY "year"
)
SELECT 
  YEAR(ratings_t."date") AS "year", 
  "household_rating" AS "rating", 
  "cost_of_30_second_ad_usd" AS "cost_per_30s (nominal)", 
  "cost_of_30_second_ad_usd" / "comp_inflation" * (SELECT "comp_inflation" FROM inflation_t WHERE "year" = 2020) AS "cost_per_30s (inflation-adjusted)",
  "average_viewers" / "cost_of_30_second_ad_usd" AS "viewers_per_dollar (nominal)",
  "average_viewers" / ("cost_of_30_second_ad_usd" / "comp_inflation" * (SELECT "comp_inflation" FROM inflation_t WHERE "year" = 2020)) AS "viewers_per_dollar (inflation-adjusted)"
FROM @kaggle.mattop_super_bowl_television_ratings.super_bowl_ratings ratings_t
INNER JOIN inflation_t
    ON YEAR(ratings_t."date") = inflation_t."year"
ORDER BY "year"