WITH latest_per_country AS (
  SELECT DISTINCT ON (economy)
    economy, year, value
  FROM @worldbank.education_statistics.uis_per_11t15_bullied
  WHERE economy IN (
    'AUT', 'BEL', 'BGR', 'HRV', 'CYP', 'CZE', 'DNK', 'EST', 'FIN', 'FRA', 'DEU', 'GRC', 'HUN',
    'IRL', 'ITA', 'LVA', 'LTU', 'LUX', 'MLT', 'NLD', 'POL', 'PRT', 'ROU', 'SVK', 'SVN', 'ESP', 'SWE'
  )
  AND value is not null
  ORDER BY economy, year DESC
),
uk_data AS (
  SELECT 'United Kingdom' as economy, year, value
  FROM @worldbank.education_statistics.uis_per_11t15_bullied
  WHERE economy = 'GBR' AND value is not null
  ORDER BY year DESC
  LIMIT 1
),
us_data AS (
  SELECT economy, year, value
  FROM @worldbank.education_statistics.uis_per_11t15_bullied
  WHERE economy = 'USA' AND value is not null
  ORDER BY year DESC
  LIMIT 1
),
eu_avg AS (
  SELECT ROUND(AVG(value),1) AS avg_value
  FROM latest_per_country
)

SELECT economy, ROUND(value,1) as perc_students_reported_bullying FROM uk_data
UNION ALL
SELECT 'EU-27' AS economy, ROUND(avg_value,1) as perc_students_reported_bullying FROM eu_avg
UNION ALL
SELECT economy, ROUND(value,1) as perc_students_reported_bullying FROM us_data