1
2WITH earnings_data AS (
3  SELECT 
4    CAST(time_period AS INTEGER) as year,
5    AVG(value) as median_hourly_earnings_eur
6  FROM "@eurostat.earn_ses_pub2s.tidy"
7  WHERE geo = 'Portugal' 
8    AND sizeclas = '10 employees or more'
9    AND sex = 'Total'
10  GROUP BY time_period
11),
12earnings_indexed AS (
13  SELECT 
14    year,
15    median_hourly_earnings_eur,
16    100.0 * median_hourly_earnings_eur / (SELECT median_hourly_earnings_eur FROM earnings_data WHERE year = 2014) as earnings_index
17  FROM earnings_data
18),
19inflation_data AS (
20  SELECT 
21    CAST(time_period AS INTEGER) as year,
22    value as inflation_rate_pct
23  FROM "@eurostat.tec00118.tidy"
24  WHERE geo = 'Portugal'
25),
26price_index AS (
27  SELECT 
28    year,
29    inflation_rate_pct,
30    100.0 * EXP(SUM(LN(1 + inflation_rate_pct/100.0)) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as cumulative_price_index
31  FROM inflation_data
32  WHERE year >= 2014
33),
34min_wage_data AS (
35  SELECT 
36    CAST(SUBSTR(time_period, 1, 4) AS INTEGER) as year,
37    AVG(value) as minimum_wage_eur
38  FROM "@eurostat.tps00155.tidy"
39  WHERE geo = 'Portugal'
40    AND currency = 'Euro'
41  GROUP BY CAST(SUBSTR(time_period, 1, 4) AS INTEGER)
42),
43min_wage_indexed AS (
44  SELECT 
45    year,
46    minimum_wage_eur,
47    100.0 * minimum_wage_eur / (SELECT minimum_wage_eur FROM min_wage_data WHERE year = 2019) as min_wage_index
48  FROM min_wage_data
49)
50SELECT 
51  COALESCE(e.year, p.year, m.year) as year,
52  ROUND(e.earnings_index, 1) as median_earnings_index,
53  ROUND(p.cumulative_price_index, 1) as cost_of_living_index,
54  ROUND(m.min_wage_index, 1) as minimum_wage_index
55FROM earnings_indexed e
56FULL OUTER JOIN price_index p ON e.year = p.year
57FULL OUTER JOIN min_wage_indexed m ON COALESCE(e.year, p.year) = m.year
58WHERE COALESCE(e.year, p.year, m.year) >= 2014
59ORDER BY year
60