1WITH latest_per_country AS (
2  SELECT DISTINCT ON (economy)
3    economy, year, value
4  FROM @worldbank.education_statistics.uis_per_11t15_bullied
5  WHERE economy IN (
6    'AUT', 'BEL', 'BGR', 'HRV', 'CYP', 'CZE', 'DNK', 'EST', 'FIN', 'FRA', 'DEU', 'GRC', 'HUN',
7    'IRL', 'ITA', 'LVA', 'LTU', 'LUX', 'MLT', 'NLD', 'POL', 'PRT', 'ROU', 'SVK', 'SVN', 'ESP', 'SWE'
8  )
9  AND value is not null
10  ORDER BY economy, year DESC
11),
12uk_data AS (
13  SELECT 'United Kingdom' as economy, year, value
14  FROM @worldbank.education_statistics.uis_per_11t15_bullied
15  WHERE economy = 'GBR' AND value is not null
16  ORDER BY year DESC
17  LIMIT 1
18),
19us_data AS (
20  SELECT economy, year, value
21  FROM @worldbank.education_statistics.uis_per_11t15_bullied
22  WHERE economy = 'USA' AND value is not null
23  ORDER BY year DESC
24  LIMIT 1
25),
26eu_avg AS (
27  SELECT ROUND(AVG(value),1) AS avg_value
28  FROM latest_per_country
29)
30
31SELECT economy, ROUND(value,1) as perc_students_reported_bullying FROM uk_data
32UNION ALL
33SELECT 'EU-27' AS economy, ROUND(avg_value,1) as perc_students_reported_bullying FROM eu_avg
34UNION ALL
35SELECT economy, ROUND(value,1) as perc_students_reported_bullying FROM us_data
36
37