Analyzing CIA Factbook Data Using SQL

This SQL exercise has two main objectives. The first is to understand the process for using SQL in Jupyter Notebook. The second is to explore the CIA Factbook and derive insights around variations in population, geography, and death and birth rates across countries.

The first few results below address the first objective, and include:

Connecting Jupyter Notebook to the Factbook database file
Including '%%sql' to ensure code is read as SQL
Viewing the tables kept in the database of interest

In [22]:

%%capture
%load_ext sql
%sql sqlite:///factbook.db

Out[22]:

'Connected: None@factbook.db'

In [23]:

%%sql
SELECT * 
FROM sqlite_master 
WHERE type='table';

Done.

Out[23]:

type	name	tbl_name	rootpage	sql
table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)
table	facts	facts	47	CREATE TABLE "facts" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "code" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, "area" integer, "area_land" integer, "area_water" integer, "population" integer, "population_growth" float, "birth_rate" float, "death_rate" float, "migration_rate" float)

In approaching the second objective, the first action is to preview the 'facts' table to become familiar with the overall layout and variables within it.

In [24]:

%%sql
SELECT *
FROM facts
LIMIT 5;

Done.

Out[24]:

id	code	name	area	area_land	area_water	population	population_growth	birth_rate	death_rate	migration_rate
1	af	Afghanistan	652230	652230	0	32564342	2.32	38.57	13.89	1.51
2	al	Albania	28748	27398	1350	3029278	0.3	12.92	6.58	3.3
3	ag	Algeria	2381741	2381741	0	39542166	1.84	23.67	4.31	0.92
4	an	Andorra	468	468	0	85580	0.12	8.13	6.96	0.0
5	ao	Angola	1246700	1246700	0	19625353	2.78	38.78	11.49	0.46

Next we evaluate values related to population, the first area of interest, and we do this by checking the range of values for population and population growth. We then follow up and identify which countries are associated with the minimum and maximum values. From this we learn that a population of 0 makes sense because of Antarctica. However, we also learn that a row for 'World' is also included and is roughly the same value as the sum of all other rows in the table. It may be necessary in future analysis to understand why these values are not equal but, for now, we need to keep the 'World' row in mind when calculating certain summary statistics on columns later on.

In [25]:

%%sql
SELECT
    MIN(population),
    MAX(population),
    MIN(population_growth),
    MAX(population_growth)
FROM facts;

Done.

Out[25]:

MIN(population)	MAX(population)	MIN(population_growth)	MAX(population_growth)
0	7256490011	0.0	4.02

In [26]:

%%sql
SELECT
    name,
    population
FROM facts
WHERE population IN (SELECT MIN(population) FROM facts);

Done.

Out[26]:

name	population
Antarctica	0

In [27]:

%%sql
SELECT
    name,
    population
FROM facts
WHERE population IN (SELECT MAX(population) FROM facts);

Done.

Out[27]:

name	population
World	7256490011

In [28]:

%%sql
SELECT
    SUM(population) AS `World Population`
FROM facts
WHERE name NOT IN ('World');

Done.

Out[28]:

World Population
7770482643

Our first task is to find out which countries are densely populated. We define these countries as those with greater than average populations and smaller than average total areas. As such, we need to calculate the averages for 'Area' and 'Population' and use a subquery to help filter the conutries that meet our above parameters.

As mentioned earleir, we want to exclude 'World' row values from the average calculations to avoid inflating them. This will be ensure relevant countries are not unintentionally excluded.

In [29]:

%%sql
SELECT
    ROUND(AVG(population), 2) `Avg Population`,
    ROUND(AVG(area), 2) `Avg Total Area (sq. km)`
FROM facts
WHERE name NOT IN ('World');

Done.

Out[29]:

Avg Population	Avg Total Area (sq. km)
32242666.57	555093.55

In [30]:

%%sql
SELECT
    name,
    population,
    area
FROM facts
WHERE 
    population > (SELECT AVG(population) FROM facts WHERE name NOT IN ('World')) 
    AND
    area < (SELECT AVG(area) FROM facts WHERE name NOT IN ('World'))

Done.

Out[30]:

name	population	area
Bangladesh	168957745	148460
Germany	80854408	357022
Iraq	37056169	438317
Italy	61855120	301340
Japan	126919659	377915
Korea, South	49115196	99720
Morocco	33322699	446550
Philippines	100998376	300000
Poland	38562189	312685
Spain	48146134	505370
Thailand	67976405	513120
Uganda	37101745	241038
United Kingdom	64088222	243610
Vietnam	94348835	331210

The above table indicates that 15 countries fall into our definition of 'densely populated.'

While we are focused on the 'area' column, we also want to know more about each countries water-to-land ratio. In this table 'area' is actually the summed values of land and water area. However, these values are also disaggregated within the table, so we will use them to compute our ratio and discover which countries have the most water to land area, and also which countries generally have more water than land.

Again, an important first step is to chack the range of values for the 'area_land' and 'area_water' columns we will be using for our calculations. Our greatest concern regards the number of countries with zero land area as this will create an indeterminate result which cannot be interpreted for the sake of this analysis. Fortunately the countries with zero water area will not affect the ratio and will only return a zero value.

In [46]:

%%sql
SELECT
    MIN(area_land),
    MAX(area_land),
    MIN(area_water),
    MAX(area_water)
FROM facts
WHERE name NOT IN ('World');

Done.

Out[46]:

MIN(area_land)	MAX(area_land)	MIN(area_water)	MAX(area_water)
0	16377742	0	891163

In [47]:

%%sql
SELECT
    COUNT(*)
FROM facts
WHERE 
    area_land = 0;

Done.

Out[47]:

COUNT(*)
1

We find that the Holy See is the only row that has no land area, and coincidentally no water area. This will still return an indeterminate value, so the ratio calculation will be null.

In [33]:

%%sql
SELECT
    name,
    area_land,
    area_water
FROM facts
WHERE area_land = 0;

Done.

Out[33]:

name	area_land	area_water
Holy See (Vatican City)	0	0

In [34]:

%%sql
SELECT
    name,
    area_land,
    area_water
FROM facts
WHERE area_water = 0;

Done.

Out[34]:

name	area_land	area_water
Afghanistan	652230	0
Algeria	2381741	0
Andorra	468	0
Angola	1246700	0
Antigua and Barbuda	442	0
Bahrain	760	0
Barbados	430	0
Bhutan	38394	0
Cabo Verde	4033	0
Central African Republic	622984	0
Comoros	2235	0
Dominica	751	0
Equatorial Guinea	28051	0
Fiji	18274	0
Georgia	69700	0
Grenada	344	0
Kiribati	811	0
Kosovo	10887	0
Kuwait	17818	0
Lesotho	30355	0
Libya	1759540	0
Liechtenstein	160	0
Luxembourg	2586	0
Maldives	298	0
Malta	316	0
Marshall Islands	181	0
Mauritania	1030700	0
Micronesia, Federated States of	702	0
Monaco	2	0
Nauru	21	0
Oman	309500	0
Palau	459	0
Qatar	11586	0
Saint Kitts and Nevis	261	0
Saint Vincent and the Grenadines	389	0
San Marino	61	0
Sao Tome and Principe	964	0
Saudi Arabia	2149690	0
Serbia	77474	0
Seychelles	455	0
Timor-Leste	14874	0
Trinidad and Tobago	5128	0
Tuvalu	26	0
United Arab Emirates	83600	0
Vanuatu	12189	0
Holy See (Vatican City)	0	0
Yemen	527968	0
Ashmore and Cartier Islands	5	0
Christmas Island	135	0
Cocos (Keeling) Islands	14	0
Coral Sea Islands	3	0
Heard Island and McDonald Islands	412	0
Norfolk Island	36	0
Macau	28	0
Faroe Islands	1393	0
Clipperton Island	6	0
Saint Pierre and Miquelon	242	0
Wallis and Futuna	142	0
Aruba	180	0
Curacao	444	0
Sint Maarten	34	0
Cook Islands	236	0
Niue	260	0
Tokelau	12	0
Bouvet Island	49	0
Jan Mayen	377	0
Svalbard	62045	0
Anguilla	91	0
Bermuda	54	0
British Virgin Islands	151	0
Cayman Islands	264	0
Falkland Islands (Islas Malvinas)	12173	0
Gibraltar	6	0
Guernsey	78	0
Jersey	116	0
Isle of Man	572	0
Montserrat	102	0
Pitcairn Islands	47	0
Saint Helena, Ascension, and Tristan da Cunha	122	0
South Georgia and South Sandwich Islands	3903	0
Turks and Caicos Islands	948	0
American Samoa	199	0
Guam	544	0
Navassa Island	5	0
Northern Mariana Islands	464	0
Wake Island	6	0
Gaza Strip	360	0
Paracel Islands	7	0
Spratly Islands	5	0
Western Sahara	266000	0

In [35]:

%%sql
SELECT
    name,
    area_water,
    area_land,
    round(cast(area_water as float) / cast(area_land as float), 2) AS ratio
FROM facts
ORDER BY ratio DESC
LIMIT 10;

Done.

Out[35]:

name	area_water	area_land	ratio
British Indian Ocean Territory	54340	60	905.67
Virgin Islands	1564	346	4.52
Puerto Rico	4921	8870	0.55
Bahamas, The	3870	10010	0.39
Guinea-Bissau	8005	28120	0.28
Malawi	24404	94080	0.26
Netherlands	7650	33893	0.23
Uganda	43938	197100	0.22
Eritrea	16600	101000	0.16
Liberia	15049	96320	0.16

The next area to explore is population growth. We will consider population growth in two ways. The first is it is in the table -- a percentage of a country's population. This allows us to compare countries to each other -- for instance, to determine if one or more countries are experiencing rapid growth which could indicate impending economic or geopolitical issues. In this case we see that South Sudan (4.02%) can expect the greatest growth relative to its population whereas the Holy See (Vatican City), Cocos (Keeling) Islands, Greenland, and the Pitcairn Islands will see no growth (0.0%).

However, we can also view population growth as the raw number (rather than the percent) of people each country can expect. In this case we want to multiply the population growth percentage by population. To do this we also need to multiply the quantity by 0.01 because population growth is recorded as a percentage and not a decimal.

In [36]:

%%sql
SELECT
    name,
    population,
    population_growth
FROM facts
ORDER BY 
    population_growth DESC;

Done.

Out[36]:

name	population	population_growth
South Sudan	12042910	4.02
Malawi	17964697	3.32
Burundi	10742276	3.28
Niger	18045729	3.25
Uganda	37101745	3.24
Qatar	2194817	3.07
Burkina Faso	18931686	3.03
Mali	16955536	2.98
Cook Islands	9838	2.95
Iraq	37056169	2.93
Ethiopia	99465819	2.89
Zambia	15066266	2.88
Western Sahara	570866	2.82
Gaza Strip	1869055	2.81
Tanzania	51045882	2.79
Angola	19625353	2.78
Benin	10448647	2.78
Togo	7552318	2.69
Guinea	11780162	2.63
Cameroon	23739218	2.59
Madagascar	23812681	2.58
United Arab Emirates	5779760	2.58
Rwanda	12661733	2.56
Equatorial Guinea	740743	2.51
Liberia	4195666	2.47
Yemen	26737317	2.47
Congo, Democratic Republic of the	79375136	2.45
Mozambique	25303113	2.45
Nigeria	181562056	2.45
Senegal	13975834	2.45
Timor-Leste	1231116	2.42
Bahrain	1346613	2.41
Sierra Leone	5879098	2.35
Afghanistan	32564342	2.32
British Virgin Islands	33454	2.32
Turks and Caicos Islands	50280	2.3
Eritrea	6527689	2.25
Libya	6411776	2.23
Mauritania	3596702	2.23
Zimbabwe	14229541	2.21
Djibouti	828324	2.2
Ghana	26327649	2.18
Northern Mariana Islands	52344	2.18
Gambia, The	1967709	2.16
Central African Republic	5391539	2.13
Luxembourg	570252	2.13
Cayman Islands	56092	2.1
Oman	3286936	2.07
Anguilla	16418	2.03
Solomon Islands	622469	2.02
Congo, Republic of the	4755097	2.0
Vanuatu	272264	1.95
West Bank	2785366	1.95
Gabon	1705336	1.93
Kenya	45925301	1.93
Cote d'Ivoire	23295302	1.91
Guinea-Bissau	1726170	1.91
Chad	11631456	1.89
Singapore	5674472	1.89
Belize	347369	1.87
Algeria	39542166	1.84
Sao Tome and Principe	194006	1.84
Somalia	10616380	1.83
Guatemala	14918999	1.82
Egypt	88487396	1.79
Nepal	31551305	1.79
Papua New Guinea	6672429	1.78
Comoros	780971	1.77
Sudan	36108853	1.72
Tajikistan	8191958	1.71
Honduras	8746673	1.68
Marshall Islands	72191	1.66
Brunei	429646	1.62
Kuwait	2788534	1.62
Philippines	100998376	1.61
Bangladesh	168957745	1.6
Cambodia	15708756	1.58
Bolivia	10800882	1.56
Israel	8049314	1.56
Laos	6911544	1.55
Sint Maarten	39689	1.51
Pakistan	199085847	1.46
Saudi Arabia	27752316	1.46
Malaysia	30513848	1.44
Cyprus	1189197	1.43
Venezuela	29275460	1.39
New Caledonia	271615	1.38
Cabo Verde	545993	1.36
Ecuador	15868396	1.35
South Africa	53675563	1.33
Aruba	112162	1.33
Panama	3657024	1.32
Mongolia	2992908	1.31
Turkey	79414269	1.26
Ireland	4892305	1.25
Antigua and Barbuda	92436	1.24
Dominican Republic	10478756	1.23
Costa Rica	4814144	1.22
India	1251695584	1.22
Botswana	2182719	1.21
Iceland	331918	1.21
Iran	81824270	1.2
Mexico	121736809	1.18
Haiti	10110019	1.17
Paraguay	6783272	1.16
Kiribati	105711	1.15
Kazakhstan	18157122	1.14
Turkmenistan	5231422	1.14
Norway	5207689	1.13
Bhutan	741919	1.11
Kyrgyzstan	5664939	1.11
Swaziland	1435613	1.11
Christmas Island	1530	1.11
Suriname	579633	1.08
Saint Pierre and Miquelon	5657	1.08
World	7256490011	1.08
Australia	22751014	1.07
Latvia	1986705	1.06
Colombia	46736728	1.04
Lithuania	2884433	1.04
Moldova	3546847	1.03
Burma	56320206	1.01
Morocco	33322699	1.0
Nicaragua	5907881	1.0
Peru	30444999	0.97
Vietnam	94348835	0.97
Azerbaijan	9780780	0.96
French Polynesia	282703	0.94
Argentina	43431886	0.93
Uzbekistan	29199942	0.93
Indonesia	255993674	0.92
Spain	48146134	0.89
Tunisia	11037225	0.89
Lebanon	6184701	0.86
Bahamas, The	324597	0.85
Liechtenstein	37624	0.84
Sri Lanka	22053488	0.84
Jordan	8117564	0.83
Seychelles	92430	0.83
Chile	17508260	0.82
New Zealand	4438393	0.82
San Marino	33020	0.82
Tuvalu	10869	0.82
Sweden	9801616	0.8
Macau	592731	0.8
Jersey	97294	0.8
United States	321368864	0.78
Brazil	204259812	0.77
Belgium	11323973	0.76
Saint Kitts and Nevis	51936	0.76
Isle of Man	87545	0.76
Canada	35099836	0.75
Switzerland	8121830	0.71
Jamaica	2950210	0.68
Fiji	909389	0.67
Mauritius	1339827	0.64
Ukraine	44429471	0.6
Puerto Rico	3598357	0.6
Namibia	2212307	0.59
Virgin Islands	103574	0.59
Bulgaria	7186893	0.58
Samoa	197773	0.58
Austria	8665550	0.55
Estonia	1265420	0.55
Nauru	9540	0.55
United Kingdom	64088222	0.54
Guam	161785	0.54
Korea, North	24983205	0.53
Faroe Islands	50196	0.51
Bermuda	70196	0.5
Montserrat	5241	0.5
Grenada	110694	0.48
Micronesia, Federated States of	105216	0.46
Serbia	7176794	0.46
China	1367485388	0.45
France	66553766	0.43
Curacao	148406	0.43
Montenegro	647073	0.42
Netherlands	16947904	0.41
Finland	5476922	0.4
Palau	21265	0.38
Hong Kong	7141106	0.38
Saint Lucia	163922	0.34
Thailand	67976405	0.34
Guernsey	66080	0.34
Wallis and Futuna	15613	0.33
Lesotho	1947701	0.32
Barbados	290604	0.31
Malta	413965	0.31
Albania	3029278	0.3
Romania	21666350	0.3
American Samoa	54343	0.3
Saint Vincent and the Grenadines	102627	0.28
Italy	61855120	0.27
Uruguay	3341893	0.27
Slovenia	1983412	0.26
El Salvador	6141350	0.25
European Union	513949445	0.25
Gibraltar	29258	0.24
Saint Helena, Ascension, and Tristan da Cunha	7795	0.24
Taiwan	23415126	0.23
Denmark	5581503	0.22
Hungary	9897541	0.22
Dominica	73607	0.21
Belarus	9589689	0.2
Macedonia	2096015	0.2
Germany	80854408	0.17
Czech Republic	10644842	0.16
Japan	126919659	0.16
Syria	17064854	0.16
Armenia	3056382	0.15
Cuba	11031433	0.15
Korea, South	49115196	0.14
Bosnia and Herzegovina	3867055	0.13
Croatia	4464844	0.13
Trinidad and Tobago	1222363	0.13
Andorra	85580	0.12
Monaco	30535	0.12
Poland	38562189	0.09
Portugal	10825309	0.09
Georgia	4931226	0.08
Maldives	393253	0.08
Russia	142423773	0.04
Tonga	106501	0.03
Niue	1190	0.03
Svalbard	1872	0.03
Guyana	735222	0.02
Slovakia	5445027	0.02
Greece	10775643	0.01
Norfolk Island	2210	0.01
Tokelau	1337	0.01
Falkland Islands (Islas Malvinas)	3361	0.01
Holy See (Vatican City)	842	0.0
Cocos (Keeling) Islands	596	0.0
Greenland	57733	0.0
Pitcairn Islands	48	0.0
Kosovo	1870981	None
Ashmore and Cartier Islands	None	None
Coral Sea Islands	None	None
Heard Island and McDonald Islands	None	None
Clipperton Island	None	None
French Southern and Antarctic Lands	None	None
Saint Barthelemy	7237	None
Saint Martin	31754	None
Bouvet Island	None	None
Jan Mayen	None	None
Akrotiri	15700	None
British Indian Ocean Territory	None	None
Dhekelia	15700	None
South Georgia and South Sandwich Islands	None	None
Navassa Island	None	None
Wake Island	None	None
United States Pacific Island Wildlife Refuges	None	None
Antarctica	0	None
Paracel Islands	None	None
Spratly Islands	None	None
Arctic Ocean	None	None
Atlantic Ocean	None	None
Indian Ocean	None	None
Pacific Ocean	None	None
Southern Ocean	None	None

In [37]:

%%sql
SELECT 
    name,
    population,
    population_growth,
    ROUND(population * population_growth * .01, 2) AS `Number of People`
FROM facts
WHERE name NOT IN ('World')
ORDER BY `Number of People` DESC;

Done.

Out[37]:

name	population	population_growth	Number of People
India	1251695584	1.22	15270686.12
China	1367485388	0.45	6153684.25
Nigeria	181562056	2.45	4448270.37
Pakistan	199085847	1.46	2906653.37
Ethiopia	99465819	2.89	2874562.17
Bangladesh	168957745	1.6	2703323.92
United States	321368864	0.78	2506677.14
Indonesia	255993674	0.92	2355141.8
Congo, Democratic Republic of the	79375136	2.45	1944690.83
Philippines	100998376	1.61	1626073.85
Egypt	88487396	1.79	1583924.39
Brazil	204259812	0.77	1572800.55
Mexico	121736809	1.18	1436494.35
Tanzania	51045882	2.79	1424180.11
European Union	513949445	0.25	1284873.61
Uganda	37101745	3.24	1202096.54
Iraq	37056169	2.93	1085745.75
Turkey	79414269	1.26	1000619.79
Iran	81824270	1.2	981891.24
Vietnam	94348835	0.97	915183.7
Kenya	45925301	1.93	886358.31
Afghanistan	32564342	2.32	755492.73
Algeria	39542166	1.84	727575.85
South Africa	53675563	1.33	713884.99
Yemen	26737317	2.47	660411.73
Sudan	36108853	1.72	621072.27
Mozambique	25303113	2.45	619926.27
Cameroon	23739218	2.59	614845.75
Madagascar	23812681	2.58	614367.17
Malawi	17964697	3.32	596427.94
Niger	18045729	3.25	586486.19
Ghana	26327649	2.18	573942.75
Burkina Faso	18931686	3.03	573630.09
Burma	56320206	1.01	568834.08
Nepal	31551305	1.79	564768.36
Angola	19625353	2.78	545584.81
Mali	16955536	2.98	505274.97
Colombia	46736728	1.04	486061.97
South Sudan	12042910	4.02	484124.98
Cote d'Ivoire	23295302	1.91	444940.27
Malaysia	30513848	1.44	439399.41
Zambia	15066266	2.88	433908.46
Spain	48146134	0.89	428500.59
Venezuela	29275460	1.39	406928.89
Saudi Arabia	27752316	1.46	405183.81
Argentina	43431886	0.93	403916.54
Burundi	10742276	3.28	352346.65
United Kingdom	64088222	0.54	346076.4
Senegal	13975834	2.45	342407.93
Morocco	33322699	1.0	333226.99
Rwanda	12661733	2.56	324140.36
Zimbabwe	14229541	2.21	314472.86
Guinea	11780162	2.63	309818.26
Peru	30444999	0.97	295316.49
Benin	10448647	2.78	290472.39
France	66553766	0.43	286181.19
Uzbekistan	29199942	0.93	271559.46
Guatemala	14918999	1.82	271525.78
Ukraine	44429471	0.6	266576.83
Canada	35099836	0.75	263248.77
Cambodia	15708756	1.58	248198.34
Australia	22751014	1.07	243435.85
Thailand	67976405	0.34	231119.78
Chad	11631456	1.89	219834.52
Ecuador	15868396	1.35	214223.35
Kazakhstan	18157122	1.14	206991.19
Togo	7552318	2.69	203157.35
Japan	126919659	0.16	203071.45
Somalia	10616380	1.83	194279.75
Sri Lanka	22053488	0.84	185249.3
Bolivia	10800882	1.56	168493.76
Italy	61855120	0.27	167008.82
United Arab Emirates	5779760	2.58	149117.81
Honduras	8746673	1.68	146944.11
Eritrea	6527689	2.25	146873.0
Chile	17508260	0.82	143567.73
Libya	6411776	2.23	142982.6
Tajikistan	8191958	1.71	140082.48
Sierra Leone	5879098	2.35	138158.8
Germany	80854408	0.17	137452.49
Korea, North	24983205	0.53	132410.99
Dominican Republic	10478756	1.23	128888.7
Israel	8049314	1.56	125569.3
Papua New Guinea	6672429	1.78	118769.24
Haiti	10110019	1.17	118287.22
Central African Republic	5391539	2.13	114839.78
Singapore	5674472	1.89	107247.52
Laos	6911544	1.55	107128.93
Liberia	4195666	2.47	103632.95
Tunisia	11037225	0.89	98231.3
Congo, Republic of the	4755097	2.0	95101.94
Azerbaijan	9780780	0.96	93895.49
Belgium	11323973	0.76	86062.19
Mauritania	3596702	2.23	80206.45
Paraguay	6783272	1.16	78685.96
Sweden	9801616	0.8	78412.93
Netherlands	16947904	0.41	69486.41
Korea, South	49115196	0.14	68761.27
Oman	3286936	2.07	68039.58
Qatar	2194817	3.07	67380.88
Jordan	8117564	0.83	67375.78
Romania	21666350	0.3	64999.05
Kyrgyzstan	5664939	1.11	62880.82
Ireland	4892305	1.25	61153.81
Turkmenistan	5231422	1.14	59638.21
Nicaragua	5907881	1.0	59078.81
Norway	5207689	1.13	58846.89
Costa Rica	4814144	1.22	58732.56
Switzerland	8121830	0.71	57664.99
Russia	142423773	0.04	56969.51
West Bank	2785366	1.95	54314.64
Taiwan	23415126	0.23	53854.79
Lebanon	6184701	0.86	53188.43
Gaza Strip	1869055	2.81	52520.45
Panama	3657024	1.32	48272.72
Austria	8665550	0.55	47660.53
Kuwait	2788534	1.62	45174.25
Gambia, The	1967709	2.16	42502.51
Bulgaria	7186893	0.58	41683.98
Mongolia	2992908	1.31	39207.09
Moldova	3546847	1.03	36532.52
New Zealand	4438393	0.82	36394.82
Poland	38562189	0.09	34705.97
Serbia	7176794	0.46	33013.25
Guinea-Bissau	1726170	1.91	32969.85
Gabon	1705336	1.93	32912.98
Bahrain	1346613	2.41	32453.37
Lithuania	2884433	1.04	29998.1
Timor-Leste	1231116	2.42	29793.01
Syria	17064854	0.16	27303.77
Hong Kong	7141106	0.38	27136.2
Botswana	2182719	1.21	26410.9
Finland	5476922	0.4	21907.69
Hungary	9897541	0.22	21774.59
Puerto Rico	3598357	0.6	21590.14
Latvia	1986705	1.06	21059.07
Jamaica	2950210	0.68	20061.43
Belarus	9589689	0.2	19179.38
Equatorial Guinea	740743	2.51	18592.65
Djibouti	828324	2.2	18223.13
Czech Republic	10644842	0.16	17031.75
Cyprus	1189197	1.43	17005.52
Cuba	11031433	0.15	16547.15
Western Sahara	570866	2.82	16098.42
Swaziland	1435613	1.11	15935.3
El Salvador	6141350	0.25	15353.37
Comoros	780971	1.77	13823.19
Namibia	2212307	0.59	13052.61
Solomon Islands	622469	2.02	12573.87
Denmark	5581503	0.22	12279.31
Luxembourg	570252	2.13	12146.37
Portugal	10825309	0.09	9742.78
Albania	3029278	0.3	9087.83
Uruguay	3341893	0.27	9023.11
Mauritius	1339827	0.64	8574.89
Bhutan	741919	1.11	8235.3
Cabo Verde	545993	1.36	7425.5
Brunei	429646	1.62	6960.27
Estonia	1265420	0.55	6959.81
Belize	347369	1.87	6495.8
Suriname	579633	1.08	6260.04
Lesotho	1947701	0.32	6232.64
Fiji	909389	0.67	6092.91
Croatia	4464844	0.13	5804.3
Vanuatu	272264	1.95	5309.15
Slovenia	1983412	0.26	5156.87
Bosnia and Herzegovina	3867055	0.13	5027.17
Macau	592731	0.8	4741.85
Armenia	3056382	0.15	4584.57
Macedonia	2096015	0.2	4192.03
Iceland	331918	1.21	4016.21
Georgia	4931226	0.08	3944.98
New Caledonia	271615	1.38	3748.29
Sao Tome and Principe	194006	1.84	3569.71
Bahamas, The	324597	0.85	2759.07
Montenegro	647073	0.42	2717.71
French Polynesia	282703	0.94	2657.41
Trinidad and Tobago	1222363	0.13	1589.07
Aruba	112162	1.33	1491.75
Malta	413965	0.31	1283.29
Kiribati	105711	1.15	1215.68
Marshall Islands	72191	1.66	1198.37
Cayman Islands	56092	2.1	1177.93
Turks and Caicos Islands	50280	2.3	1156.44
Samoa	197773	0.58	1147.08
Antigua and Barbuda	92436	1.24	1146.21
Northern Mariana Islands	52344	2.18	1141.1
Slovakia	5445027	0.02	1089.01
Greece	10775643	0.01	1077.56
Barbados	290604	0.31	900.87
Guam	161785	0.54	873.64
Jersey	97294	0.8	778.35
British Virgin Islands	33454	2.32	776.13
Seychelles	92430	0.83	767.17
Isle of Man	87545	0.76	665.34
Curacao	148406	0.43	638.15
Virgin Islands	103574	0.59	611.09
Sint Maarten	39689	1.51	599.3
Saint Lucia	163922	0.34	557.33
Grenada	110694	0.48	531.33
Micronesia, Federated States of	105216	0.46	483.99
Saint Kitts and Nevis	51936	0.76	394.71
Bermuda	70196	0.5	350.98
Anguilla	16418	2.03	333.29
Liechtenstein	37624	0.84	316.04
Maldives	393253	0.08	314.6
Cook Islands	9838	2.95	290.22
Saint Vincent and the Grenadines	102627	0.28	287.36
San Marino	33020	0.82	270.76
Faroe Islands	50196	0.51	256.0
Guernsey	66080	0.34	224.67
American Samoa	54343	0.3	163.03
Dominica	73607	0.21	154.57
Guyana	735222	0.02	147.04
Andorra	85580	0.12	102.7
Tuvalu	10869	0.82	89.13
Palau	21265	0.38	80.81
Gibraltar	29258	0.24	70.22
Saint Pierre and Miquelon	5657	1.08	61.1
Nauru	9540	0.55	52.47
Wallis and Futuna	15613	0.33	51.52
Monaco	30535	0.12	36.64
Tonga	106501	0.03	31.95
Montserrat	5241	0.5	26.21
Saint Helena, Ascension, and Tristan da Cunha	7795	0.24	18.71
Christmas Island	1530	1.11	16.98
Svalbard	1872	0.03	0.56
Niue	1190	0.03	0.36
Falkland Islands (Islas Malvinas)	3361	0.01	0.34
Norfolk Island	2210	0.01	0.22
Tokelau	1337	0.01	0.13
Holy See (Vatican City)	842	0.0	0.0
Cocos (Keeling) Islands	596	0.0	0.0
Greenland	57733	0.0	0.0
Pitcairn Islands	48	0.0	0.0
Kosovo	1870981	None	None
Ashmore and Cartier Islands	None	None	None
Coral Sea Islands	None	None	None
Heard Island and McDonald Islands	None	None	None
Clipperton Island	None	None	None
French Southern and Antarctic Lands	None	None	None
Saint Barthelemy	7237	None	None
Saint Martin	31754	None	None
Bouvet Island	None	None	None
Jan Mayen	None	None	None
Akrotiri	15700	None	None
British Indian Ocean Territory	None	None	None
Dhekelia	15700	None	None
South Georgia and South Sandwich Islands	None	None	None
Navassa Island	None	None	None
Wake Island	None	None	None
United States Pacific Island Wildlife Refuges	None	None	None
Antarctica	0	None	None
Paracel Islands	None	None	None
Spratly Islands	None	None	None
Arctic Ocean	None	None	None
Atlantic Ocean	None	None	None
Indian Ocean	None	None	None
Pacific Ocean	None	None	None
Southern Ocean	None	None	None

From the second approach we can see that South Sudan's 4.02% growth translates to about 484,125 more people. Although South Sudan's growth percentage is the highest, India will experience the greatest increase in the number of people. While its growth rate is only 1.22%, that actually translates to about 15,270,686 more people.

The final topic to explore is death rates and birth rates. In particular we will look at which countries have a higher death rate than birth rate, which is characteristic of a natural population decline. This may seem like a redundant task, however population growth is a combination of these two variables in addition to the migration rate. Therefore, it would not be fair to assume that countries with high population growth rates cannot also face natural population decline (as a high positive migration rate could hide this).

All three rates (birth, death, and migration) are the average occurrences per 1,000 people. For example, a birth rate of 6.65 means that for every 1,000 people in the current population there are an average of 6.65 births. These figures can then be used to extrapolate an average number of total births, deaths, or migrants.

As with other columns, we will look at the range of values for birth, death, and migration rates. There seems to be no initial red flags (e.g. negative values for birth or death rates, etc).

In [51]:

%%sql
SELECT
    MIN(birth_rate),
    MAX(birth_rate),
    MIN(death_rate),
    MAX(death_rate),
    MIN(migration_rate),
    MAX(migration_rate)
FROM facts
WHERE name NOT IN ('World');

Done.

Out[51]:

MIN(birth_rate)	MAX(birth_rate)	MIN(death_rate)	MAX(death_rate)	MIN(migration_rate)	MAX(migration_rate)
6.65	45.45	1.53	14.89	0.0	22.39

To see which countries have the highest death rates relative to birth rates we will calculte the difference and list the results in descending order. All countries with a difference greater than zero will have a higher death rate than birth rate.

In [61]:

%%sql
SELECT
    name,
    death_rate,
    birth_rate,
    ROUND(death_rate - birth_rate, 2) AS difference,
    migration_rate,
    ROUND((death_rate - birth_rate - migration_rate) *0.1, 2) as `Net`,
    population_growth
FROM facts
WHERE difference > 0.00
    
ORDER BY difference DESC;

Done.

Out[61]:

name	death_rate	birth_rate	difference	migration_rate	Net	population_growth
Bulgaria	14.44	8.92	5.52	0.29	0.52	0.58
Serbia	13.66	9.08	4.58	0.0	0.46	0.46
Latvia	14.31	10.0	4.31	6.26	-0.19	1.06
Lithuania	14.27	10.1	4.17	6.27	-0.21	1.04
Ukraine	14.46	10.72	3.74	2.25	0.15	0.6
Hungary	12.73	9.16	3.57	1.33	0.22	0.22
Germany	11.42	8.47	2.95	1.24	0.17	0.17
Slovenia	11.37	8.42	2.95	0.37	0.26	0.26
Romania	11.9	9.14	2.76	0.24	0.25	0.3
Croatia	12.18	9.45	2.73	1.39	0.13	0.13
Belarus	13.36	10.7	2.66	0.7	0.2	0.2
Monaco	9.24	6.65	2.59	3.83	-0.12	0.12
Greece	11.09	8.66	2.43	2.32	0.01	0.01
Saint Pierre and Miquelon	9.72	7.42	2.3	8.49	-0.62	1.08
Russia	13.69	11.6	2.09	1.69	0.04	0.04
Estonia	12.4	10.51	1.89	3.6	-0.17	0.55
Portugal	11.02	9.27	1.75	2.67	-0.09	0.09
Japan	9.51	7.93	1.58	0.0	0.16	0.16
Italy	10.19	8.74	1.45	4.1	-0.27	0.27
Bosnia and Herzegovina	9.75	8.87	0.88	0.38	0.05	0.13
Czech Republic	10.34	9.63	0.71	2.33	-0.16	0.16
Moldova	12.59	12.0	0.59	9.67	-0.91	1.03
Poland	10.19	9.74	0.45	0.46	-0.0	0.09
Austria	9.42	9.41	0.01	5.56	-0.56	0.55

To summarize the final results, we can see that 24 countries from our table experience, to varying degrees, a higher death rate than birth rate. The largest difference occurs in Bulgaria where the natural population decline is about 5.5 people per 1,000 in the population.

Some additional columns were included to gauge my earlier hypothesis on the relationship between death rate, birth rate, and migration rate to the population growth rate. 'Net' is the difference between the 'difference' column and the migration rate multiplied by 0.1 to generate the percentage. The last column is the population growth rate from the 'facts' table and is included for comparison to the 'Net'column. The values are similar in many instances but not all. Based on data documentation, 'population_growth' is an average rate which is likely generated through multiple years of data whereas the 'Net' column is based on one year of data. It may still be useful for short-term analysis that assesses whether countries are performing at a higher or lower rate than trends dictate, but at this stage the metric still needs to be more rigorously tested -- another area for future research.