transparency_corruption_perceptions_index.country_iso_alpha3 = worldbank_gdp_per_capita.country_iso_alpha3 + year · one CPI score to one GDP-per-capita observation for each country and year
04 / ANALYST GUIDE
Guide
Analysis requirements
Join transparency_corruption_perceptions_index and worldbank_gdp_per_capita on both country_iso_alpha3 and year.
Start with each country’s latest year with both measures; retain year because years may differ. For a same-year comparison, choose one year and report the country count.
Plot cpi_score against gdp_per_capita_current_usd, then inspect countries that depart from the overall pattern.
CPI scores run from 0 (perceived as highly corrupt) to 100 (perceived as very clean). They measure perceptions of public-sector corruption, not observed incidents. Current-US-dollar GDP per capita is not personal income; association is not causation.
Suggested query
Expand suggested query
SQL / STARTER QUERY
WITH paired AS (
SELECT c.country_iso_alpha3, c.country_name, c.year,
c.cpi_score, g.gdp_per_capita_current_usd,
ROW_NUMBER() OVER (PARTITION BY c.country_iso_alpha3 ORDER BY c.year DESC) AS recency
FROM transparency_corruption_perceptions_index AS c
JOIN worldbank_gdp_per_capita AS g
ON c.country_iso_alpha3 = g.country_iso_alpha3
AND c.year = g.year
WHERE c.cpi_score IS NOT NULL
AND g.gdp_per_capita_current_usd IS NOT NULL
)
SELECT country_iso_alpha3, country_name, year, cpi_score, gdp_per_capita_current_usd
FROM paired
WHERE recency = 1
ORDER BY cpi_score DESC;