Practice joining GDP per capita with life expectancy by country and year, then compare their relationship.
01 / THE QUESTION
Question
Do countries with higher GDP per capita also tend to have higher life expectancy? Find countries that do not follow the overall pattern in a year you choose.
worldbank_gdp_per_capita.country_iso_alpha3 = worldbank_life_expectancy_at_birth.country_iso_alpha3 + year · one GDP-per-capita observation to one life-expectancy observation for each country and year
04 / ANALYST GUIDE
Guide
Analysis requirements
Join worldbank_gdp_per_capita and worldbank_life_expectancy_at_birth on both country_iso_alpha3 and year.
Use the latest year with both non-null measures for each country; retain year because countries may have different latest common years. For a same-year comparison, filter to one year and report coverage.
Plot gdp_per_capita_current_usd against life_expectancy_at_birth_years and inspect outliers. Consider a log scale for GDP per capita if it helps reveal the spread.
GDP per capita in current US dollars is a measure of average economic output, not personal income. A cross-country association does not establish causation.
Suggested query
Expand suggested query
SQL / STARTER QUERY
WITH paired AS (
SELECT g.country_iso_alpha3, g.country_name, g.year,
g.gdp_per_capita_current_usd, l.life_expectancy_at_birth_years,
ROW_NUMBER() OVER (PARTITION BY g.country_iso_alpha3 ORDER BY g.year DESC) AS recency
FROM worldbank_gdp_per_capita AS g
JOIN worldbank_life_expectancy_at_birth AS l
ON g.country_iso_alpha3 = l.country_iso_alpha3
AND g.year = l.year
WHERE g.gdp_per_capita_current_usd IS NOT NULL
AND l.life_expectancy_at_birth_years IS NOT NULL
)
SELECT country_iso_alpha3, country_name, year, gdp_per_capita_current_usd, life_expectancy_at_birth_years
FROM paired
WHERE recency = 1
ORDER BY gdp_per_capita_current_usd DESC;