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 * FROM inflation

SELECT
  t_year,
  transfer_season,
  player_name,
  from_club_name,
  to_club_name,
  transfer_fee as nominal_transfer_fee,
  --CAST(inflation.factor * transfer_fee AS BIGINT) AS real_transfer_fee,
  --inflation.factor
FROM transfers
--LEFT JOIN inflation ON t_year = inflation.year
ORDER BY real_transfer_fee DESC
LIMIT 100