← ALL CHALLENGES
CHALLENGE / INTERMEDIATE / READY TO QUERY

Is inflation different inside and outside the EU?

Practice joining inflation with UN M49 Europe and classifying EU membership at the observation year's end.

01 / THE QUESTION

Question

Among countries in UN M49 Europe, do EU members have different consumer price inflation from countries outside the EU?

02 / SOURCE TABLES

Tables to use

03 / RELATIONSHIP

ER diagram

ONE TO MANY RELATIONSHIP
country_geographycountry_iso_alpha3Key fieldun_m49_region_name
worldbank_inflation_consumer_prices_annual_pctcountry_iso_alpha3Join fieldyearinflation_consumer_prices_annual_pct

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

worldbank_inflation_consumer_prices_annual_pctcountry_iso_alpha3Key fieldyear
country_organization_membershipcountry_iso_alpha3Join fieldorganization_codemembership_statusvalid_fromvalid_to

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;