1WITH emu_data AS (
2    SELECT *
3    FROM @worldbank.world_development_indicators.fp_cpi_totl_zg
4    WHERE economy = 'EMU'
5),
6unpivoted AS (
7    SELECT
8        SUBSTR(column_name, 3)::INTEGER AS year,
9        column_value / 100 AS cpi
10    FROM
11        emu_data
12    UNPIVOT (
13        column_value FOR column_name IN (
14            yr1960, yr1961, yr1962, yr1963, yr1964, yr1965, yr1966, yr1967, yr1968, yr1969, yr1970,
15            yr1971, yr1972, yr1973, yr1974, yr1975, yr1976, yr1977, yr1978, yr1979, yr1980,
16            yr1981, yr1982, yr1983, yr1984, yr1985, yr1986, yr1987, yr1988, yr1989, yr1990,
17            yr1991, yr1992, yr1993, yr1994, yr1995, yr1996, yr1997, yr1998, yr1999, yr2000,
18            yr2001, yr2002, yr2003, yr2004, yr2005, yr2006, yr2007, yr2008, yr2009, yr2010,
19            yr2011, yr2012, yr2013, yr2014, yr2015, yr2016, yr2017, yr2018, yr2019, yr2020,
20            yr2021, yr2022, yr2023
21        )
22    )
23    UNION (SELECT 2024, 0)
24    UNION (SELECT 2025, 0)
25),
26inflation AS (SELECT
27  year,
28  (PRODUCT(1 + cpi) OVER (ORDER BY year DESC)) AS factor,
29  cpi,
30FROM unpivoted
31ORDER BY year DESC),
32transfers AS (SELECT YEAR(transfer_date) AS t_year, * FROM @kaggle.davidcariboo_player_scores.transfers)
33
34----
35
36
37-- SELECT * FROM inflation
38
39SELECT
40  t_year,
41  transfer_season,
42  player_name,
43  from_club_name,
44  to_club_name,
45  transfer_fee as nominal_transfer_fee,
46  CAST(inflation.factor * transfer_fee AS BIGINT) AS real_transfer_fee,
47  inflation.factor
48FROM transfers
49LEFT JOIN inflation ON t_year = inflation.year
50ORDER BY real_transfer_fee DESC
51LIMIT 100
52