1
2WITH wages AS (
3  SELECT DISTINCT time_period::INTEGER as year, value as avg_wage
4  FROM "@eurostat.nama_10_fte.tidy" 
5  WHERE geo = 'Portugal'
6),
7housing AS (
8  SELECT year, rent_prices, price_to_income_ratio, real_house_price_idx
9  FROM "@owid.housing_prices.owid_housing_prices" 
10  WHERE country = 'Portugal' AND year >= 1995
11)
12SELECT 
13  w.year,
14  ROUND(w.avg_wage::NUMERIC, 0) as avg_wage_eur,
15  ROUND(h.rent_prices::NUMERIC, 2) as rent_index,
16  ROUND(h.price_to_income_ratio::NUMERIC, 1) as price_to_income_ratio,
17  ROUND(h.real_house_price_idx::NUMERIC, 1) as real_house_price_idx
18FROM wages w
19LEFT JOIN housing h ON w.year = h.year
20WHERE w.year IS NOT NULL
21GROUP BY w.year, w.avg_wage, h.rent_prices, h.price_to_income_ratio, h.real_house_price_idx
22ORDER BY w.year
23