1WITH matched_data AS (
2  SELECT 
3    m.geo,
4    m.time,
5    m.murder_per_100000_people,
6    i.country,
7    i.immigrant_share_of_dest_population_all
8  FROM "@gapminder.murder_per_100000_people.murder_per_100000_people_by_geo_time" m
9  INNER JOIN "@owid.migrant_stock.owid_migrant_stock_1" i 
10    ON m.time = i.year
11  WHERE m.murder_per_100000_people IS NOT NULL 
12    AND i.immigrant_share_of_dest_population_all IS NOT NULL
13    AND (
14      (m.geo = 'aut' AND i.country = 'Austria') OR
15      (m.geo = 'aus' AND i.country = 'Australia') OR
16      (m.geo = 'bel' AND i.country = 'Belgium') OR
17      (m.geo = 'can' AND i.country = 'Canada') OR
18      (m.geo = 'che' AND i.country = 'Switzerland') OR
19      (m.geo = 'deu' AND i.country = 'Germany') OR
20      (m.geo = 'dnk' AND i.country = 'Denmark') OR
21      (m.geo = 'esp' AND i.country = 'Spain') OR
22      (m.geo = 'fra' AND i.country = 'France') OR
23      (m.geo = 'gbr' AND i.country = 'United Kingdom') OR
24      (m.geo = 'grc' AND i.country = 'Greece') OR
25      (m.geo = 'irl' AND i.country = 'Ireland') OR
26      (m.geo = 'ita' AND i.country = 'Italy') OR
27      (m.geo = 'jpn' AND i.country = 'Japan') OR
28      (m.geo = 'nld' AND i.country = 'Netherlands') OR
29      (m.geo = 'nor' AND i.country = 'Norway') OR
30      (m.geo = 'nzl' AND i.country = 'New Zealand') OR
31      (m.geo = 'pol' AND i.country = 'Poland') OR
32      (m.geo = 'swe' AND i.country = 'Sweden') OR
33      (m.geo = 'usa' AND i.country = 'United States')
34    )
35)
36SELECT 
37  time as year,
38  COUNT(*) as countries_with_data,
39  ROUND(AVG(murder_per_100000_people), 4) as avg_murder_rate,
40  ROUND(AVG(immigrant_share_of_dest_population_all), 2) as avg_immigrant_share_pct,
41  ROUND(CORR(murder_per_100000_people, immigrant_share_of_dest_population_all), 4) as yearly_correlation
42FROM matched_data
43GROUP BY time
44ORDER BY time
45