WITH emu_data AS (
SELECT *
FROM @worldbank.world_development_indicators.fp_cpi_totl_zg
WHERE economy = 'EMU'
),
unpivoted AS (
SELECT
SUBSTR(column_name, 3)::INTEGER AS year,
column_value / 100 AS cpi
FROM
emu_data
UNPIVOT (
column_value FOR column_name IN (
yr1960, yr1961, yr1962, yr1963, yr1964, yr1965, yr1966, yr1967, yr1968, yr1969, yr1970,
yr1971, yr1972, yr1973, yr1974, yr1975, yr1976, yr1977, yr1978, yr1979, yr1980,
yr1981, yr1982, yr1983, yr1984, yr1985, yr1986, yr1987, yr1988, yr1989, yr1990,
yr1991, yr1992, yr1993, yr1994, yr1995, yr1996, yr1997, yr1998, yr1999, yr2000,
yr2001, yr2002, yr2003, yr2004, yr2005, yr2006, yr2007, yr2008, yr2009, yr2010,
yr2011, yr2012, yr2013, yr2014, yr2015, yr2016, yr2017, yr2018, yr2019, yr2020,
yr2021, yr2022, yr2023
)
)
UNION (SELECT 2024, 0)
UNION (SELECT 2025, 0)
),
inflation AS (SELECT
year,
(PRODUCT(1 + cpi) OVER (ORDER BY year DESC)) AS factor,
cpi,
FROM unpivoted
ORDER BY year DESC),
transfers AS (SELECT YEAR(transfer_date) AS t_year, * FROM @kaggle.davidcariboo_player_scores.transfers)
SELECT
t_year,
transfer_season,
player_name,
from_club_name,
to_club_name,
transfer_fee as nominal_transfer_fee,
FROM transfers
ORDER BY real_transfer_fee DESC
LIMIT 100