1WITH inflation_t 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  YEAR(ratings_t."date") AS "year", 
13  "household_rating" AS "rating", 
14  "cost_of_30_second_ad_usd" AS "cost_per_30s (nominal)", 
15  "cost_of_30_second_ad_usd" / "comp_inflation" * (SELECT "comp_inflation" FROM inflation_t WHERE "year" = 2020) AS "cost_per_30s (inflation-adjusted)",
16  "average_viewers" / "cost_of_30_second_ad_usd" AS "viewers_per_dollar (nominal)",
17  "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)"
18FROM @kaggle.mattop_super_bowl_television_ratings.super_bowl_ratings ratings_t
19INNER JOIN inflation_t
20    ON YEAR(ratings_t."date") = inflation_t."year"
21ORDER BY "year"