worldbank_gdp_per_capita.country_iso_alpha3 = worldbank_fertility_rate_total.country_iso_alpha3 + year · one GDP-per-capita observation to one fertility observation for each country and year
04 / ANALYST GUIDE
Guide
Analysis requirements
Join worldbank_gdp_per_capita and worldbank_fertility_rate_total on both country_iso_alpha3 and year.
Start with each country’s latest year with both measures; retain year because the years may differ. For a same-year comparison, choose one year and report missing-value exclusions.
Use a scatter chart to inspect the relationship and identify exceptions. Consider a log scale for GDP per capita.
Fertility rate is the estimated number of births per woman under that year's age-specific rates, not the observed number of children in a family. GDP per capita is not household income; association does not prove cause.
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, f.fertility_rate_births_per_woman,
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_fertility_rate_total AS f
ON g.country_iso_alpha3 = f.country_iso_alpha3
AND g.year = f.year
WHERE g.gdp_per_capita_current_usd IS NOT NULL
AND f.fertility_rate_births_per_woman IS NOT NULL
)
SELECT country_iso_alpha3, country_name, year, gdp_per_capita_current_usd, fertility_rate_births_per_woman
FROM paired
WHERE recency = 1
ORDER BY gdp_per_capita_current_usd DESC;