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
เชื่อมสองตารางด้วย country_iso_alpha3 และ year พร้อมกัน
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;