Which countries welcome the most tourists per resident?
Practice a country-year JOIN and ratio using tourism arrivals and population.
01 / THE QUESTION
Question
Which countries recorded the most international tourist arrivals relative to their resident population in 2019? How does that ranking differ from total arrivals?
worldbank_international_tourist_arrivals.country_iso_alpha3 = worldbank_population_total.country_iso_alpha3 + year · one tourism observation to one population observation for each country and year
04 / ANALYST GUIDE
Guide
Analysis requirements
Join the two country-year tables on both country_iso_alpha3 and year.
Use 2019 because the tourism series has non-null observations that year and it precedes the COVID-19 travel disruption.
Divide international_tourist_arrivals by population_total; exclude null arrivals and zero/null population.
Count arrivals, not unique people. One traveler can enter more than once and some source series include same-day visitors.
Inspect the raw arrivals and population alongside the ratio before making a comparison.
Suggested query
Expand suggested query
SQL / STARTER QUERY
SELECT t.country_iso_alpha3, t.country_name, t.year,
t.international_tourist_arrivals, p.population_total,
t.international_tourist_arrivals / p.population_total AS arrivals_per_resident
FROM worldbank_international_tourist_arrivals AS t
JOIN worldbank_population_total AS p
ON t.country_iso_alpha3 = p.country_iso_alpha3
AND t.year = p.year
WHERE t.year = 2019
AND t.international_tourist_arrivals IS NOT NULL
AND p.population_total > 0
ORDER BY arrivals_per_resident DESC
LIMIT 30;