← ALL CHALLENGES
CHALLENGE / BEGINNER / READY TO QUERY

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?

02 / SOURCE TABLES

Tables to use

03 / RELATIONSHIP

ER diagram

ONE TO ONE RELATIONSHIP
worldbank_international_tourist_arrivalscountry_iso_alpha3Key fieldyearKey fieldinternational_tourist_arrivals
worldbank_population_totalcountry_iso_alpha3Join fieldyearJoin fieldpopulation_total

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;