← ALL CHALLENGES
CHALLENGE / INTERMEDIATE / READY TO QUERY

Where are NATO members located?

Practice joining NATO membership with UN M49 geography and counting countries by region.

01 / THE QUESTION

Question

How are current NATO member countries distributed across UN M49 regions and sub-regions?

02 / SOURCE TABLES

Tables to use

03 / RELATIONSHIP

ER diagram

ONE TO MANY RELATIONSHIP
country_geographycountry_iso_alpha3Key fieldun_m49_region_nameun_m49_subregion_name
country_organization_membershipcountry_iso_alpha3Join fieldorganization_codemembership_statusvalid_fromvalid_to

country_geography.country_iso_alpha3 = country_organization_membership.country_iso_alpha3 · one geography row to many membership intervals

04 / ANALYST GUIDE

Guide

Analysis requirements

  • Join the tables on country_iso_alpha3.
  • Filter organization_code to NATO and membership_status to member; use valid_to IS NULL for current records in this snapshot.
  • Count distinct country codes by un_m49_region_name and un_m49_subregion_name.
  • Return the grouping and count, then inspect the underlying country rows before drawing conclusions.
  • Treat UN M49 as a statistical geography. This membership package is not a complete historical register.

Suggested query

Expand suggested query
SQL / STARTER QUERY
SELECT g.un_m49_region_name, g.un_m49_subregion_name,
       COUNT(DISTINCT m.country_iso_alpha3) AS member_countries
FROM country_organization_membership AS m
JOIN country_geography AS g
  ON m.country_iso_alpha3 = g.country_iso_alpha3
WHERE m.organization_code = 'NATO'
  AND m.membership_status = 'member'
  AND m.valid_to IS NULL
GROUP BY g.un_m49_region_name, g.un_m49_subregion_name
ORDER BY member_countries DESC;