country_geography.country_iso_alpha3 = worldbank_inflation_consumer_prices_annual_pct.country_iso_alpha3 · one geography row to many annual inflation observations; EU membership is checked by date
Match country_iso_alpha3; test EU membership at December 31 of year within valid_from–valid_to.
04 / ANALYST GUIDE
Guide
Analysis requirements
Join worldbank_inflation_consumer_prices_annual_pct to country_geography by country_iso_alpha3 and keep un_m49_region_name = 'Europe'.
For each country, select the latest non-null inflation observation. Classify EU membership at December 31 of that observation year using the membership interval's valid_from and inclusive valid_to dates. Thus a country that joins or leaves during a year is classified by its year-end status.
Compare the two groups' inflation distributions, keeping year visible. Restrict to one shared year for a strict same-year comparison.
The membership snapshot records entry dates for current EU members and the United Kingdom's exit; it is not a complete historical registry. The data include ISO-coded territories and may need additional scope review for a country-only comparison. Inflation measures annual consumer price change, not the price level.
Suggested query
Expand suggested query
SQL / STARTER QUERY
WITH latest_inflation AS (
SELECT i.country_iso_alpha3, i.country_name, i.year,
i.inflation_consumer_prices_annual_pct,
ROW_NUMBER() OVER (PARTITION BY i.country_iso_alpha3 ORDER BY i.year DESC) AS recency
FROM worldbank_inflation_consumer_prices_annual_pct AS i
JOIN country_geography AS g
ON i.country_iso_alpha3 = g.country_iso_alpha3
WHERE g.un_m49_region_name = 'Europe'
AND i.inflation_consumer_prices_annual_pct IS NOT NULL
)
SELECT i.country_iso_alpha3, i.country_name, i.year,
i.inflation_consumer_prices_annual_pct,
CASE WHEN EXISTS (
SELECT 1 FROM country_organization_membership AS m
WHERE m.country_iso_alpha3 = i.country_iso_alpha3
AND m.organization_code = 'EU'
AND m.membership_status = 'member'
AND m.valid_from <= MAKE_DATE(i.year, 12, 31)
AND (m.valid_to IS NULL OR m.valid_to >= MAKE_DATE(i.year, 12, 31))
) THEN 'EU member' ELSE 'Non-EU Europe' END AS eu_status
FROM latest_inflation AS i
WHERE i.recency = 1
ORDER BY eu_status, i.country_name;