CIA factbook analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DISCOVERING THE MOST POPULOUS COUNTRIES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INTRODUCTION\n",
    "We are going to be working with data from the `CIA World Factbook` which contains concise and detailed statistics about all of the countries on earth. The factbook contains demographic information such as:\n",
    "* `population` - The global poplation.\n",
    "* `population_growth` - The annual population growth rate as a percentage.\n",
    "* `area` - The total land and water area.\n",
    "\n",
    "Our goal in this analysis is to find out the following:\n",
    "* The countries with the most population.\n",
    "* The most densely populated countries.\n",
    "* The countries with the highest population growth rate.\n",
    "* The countries expected to grow the most the following year, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting To The Database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: None@factbook.db'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///factbook.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>type</th>\n",
       "        <th>name</th>\n",
       "        <th>tbl_name</th>\n",
       "        <th>rootpage</th>\n",
       "        <th>sql</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>table</td>\n",
       "        <td>sqlite_sequence</td>\n",
       "        <td>sqlite_sequence</td>\n",
       "        <td>3</td>\n",
       "        <td>CREATE TABLE sqlite_sequence(name,seq)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>table</td>\n",
       "        <td>facts</td>\n",
       "        <td>facts</td>\n",
       "        <td>47</td>\n",
       "        <td>CREATE TABLE &quot;facts&quot; (&quot;id&quot; INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, &quot;code&quot; varchar(255) NOT NULL, &quot;name&quot; varchar(255) NOT NULL, &quot;area&quot; integer, &quot;area_land&quot; integer, &quot;area_water&quot; integer, &quot;population&quot; integer, &quot;population_growth&quot; float, &quot;birth_rate&quot; float, &quot;death_rate&quot; float, &quot;migration_rate&quot; float)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('table', 'sqlite_sequence', 'sqlite_sequence', 3, 'CREATE TABLE sqlite_sequence(name,seq)'),\n",
       " ('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, \"a ... (4 characters truncated) ... land\" integer, \"area_water\" integer, \"population\" integer, \"population_growth\" float, \"birth_rate\" float, \"death_rate\" float, \"migration_rate\" float)')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM sqlite_master\n",
    " WHERE type='table';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview Of The Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>af</td>\n",
       "        <td>Afghanistan</td>\n",
       "        <td>652230</td>\n",
       "        <td>652230</td>\n",
       "        <td>0</td>\n",
       "        <td>32564342</td>\n",
       "        <td>2.32</td>\n",
       "        <td>38.57</td>\n",
       "        <td>13.89</td>\n",
       "        <td>1.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>al</td>\n",
       "        <td>Albania</td>\n",
       "        <td>28748</td>\n",
       "        <td>27398</td>\n",
       "        <td>1350</td>\n",
       "        <td>3029278</td>\n",
       "        <td>0.3</td>\n",
       "        <td>12.92</td>\n",
       "        <td>6.58</td>\n",
       "        <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>ag</td>\n",
       "        <td>Algeria</td>\n",
       "        <td>2381741</td>\n",
       "        <td>2381741</td>\n",
       "        <td>0</td>\n",
       "        <td>39542166</td>\n",
       "        <td>1.84</td>\n",
       "        <td>23.67</td>\n",
       "        <td>4.31</td>\n",
       "        <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>an</td>\n",
       "        <td>Andorra</td>\n",
       "        <td>468</td>\n",
       "        <td>468</td>\n",
       "        <td>0</td>\n",
       "        <td>85580</td>\n",
       "        <td>0.12</td>\n",
       "        <td>8.13</td>\n",
       "        <td>6.96</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>ao</td>\n",
       "        <td>Angola</td>\n",
       "        <td>1246700</td>\n",
       "        <td>1246700</td>\n",
       "        <td>0</td>\n",
       "        <td>19625353</td>\n",
       "        <td>2.78</td>\n",
       "        <td>38.78</td>\n",
       "        <td>11.49</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'af', 'Afghanistan', 652230, 652230, 0, 32564342, 2.32, 38.57, 13.89, 1.51),\n",
       " (2, 'al', 'Albania', 28748, 27398, 1350, 3029278, 0.3, 12.92, 6.58, 3.3),\n",
       " (3, 'ag', 'Algeria', 2381741, 2381741, 0, 39542166, 1.84, 23.67, 4.31, 0.92),\n",
       " (4, 'an', 'Andorra', 468, 468, 0, 85580, 0.12, 8.13, 6.96, 0.0),\n",
       " (5, 'ao', 'Angola', 1246700, 1246700, 0, 19625353, 2.78, 38.78, 11.49, 0.46)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "  LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>MIN(population)</th>\n",
       "        <th>MAX(population)</th>\n",
       "        <th>MIN(population_growth)</th>\n",
       "        <th>MAX(population_growth)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>7256490011</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 7256490011, 0.0, 4.02)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(population), MAX(population),\n",
    "       MIN(population_growth), MAX(population_growth)\n",
    "  FROM facts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see from our table, the minimum population for the countries is 0 and the  maximum populaion is just above 7billion which is close to the world population and quite impossible for a country to have such a population."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>250</td>\n",
       "        <td>ay</td>\n",
       "        <td>Antarctica</td>\n",
       "        <td>None</td>\n",
       "        <td>280000</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>261</td>\n",
       "        <td>xx</td>\n",
       "        <td>World</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>7256490011</td>\n",
       "        <td>1.08</td>\n",
       "        <td>18.6</td>\n",
       "        <td>7.8</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(250, 'ay', 'Antarctica', None, 280000, None, 0, None, None, None, None),\n",
       " (261, 'xx', 'World', None, None, None, 7256490011, 1.08, 18.6, 7.8, None)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "  WHERE population = (SELECT MIN(population)\n",
    "                     FROM facts)\n",
    "  OR population = (SELECT MAX(population)\n",
    "                  FROM facts);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reason for the outlier data is because our table contains rows for both Antartica which has no native population and the World. So this explains why our min population is 0 and our max population is the same as the population of the world."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary Statistic Revisited"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min_pop</th>\n",
       "        <th>max_pop</th>\n",
       "        <th>min_pop_growth</th>\n",
       "        <th>max_pop_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1367485388, 0.0, 4.02)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(population) AS min_pop,\n",
    "       MAX(population) AS max_pop,\n",
    "       MIN(population_growth) AS min_pop_growth,\n",
    "       MAX(population_growth) AS max_pop_growth \n",
    "  FROM facts\n",
    " WHERE name <> 'World';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From our table above, we can see that there is a country with nearly 1.4 billion people."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring The Most Populous Countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>ch</td>\n",
       "        <td>China</td>\n",
       "        <td>9596960</td>\n",
       "        <td>9326410</td>\n",
       "        <td>270550</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.45</td>\n",
       "        <td>12.49</td>\n",
       "        <td>7.53</td>\n",
       "        <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>in</td>\n",
       "        <td>India</td>\n",
       "        <td>3287263</td>\n",
       "        <td>2973193</td>\n",
       "        <td>314070</td>\n",
       "        <td>1251695584</td>\n",
       "        <td>1.22</td>\n",
       "        <td>19.55</td>\n",
       "        <td>7.32</td>\n",
       "        <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>197</td>\n",
       "        <td>ee</td>\n",
       "        <td>European Union</td>\n",
       "        <td>4324782</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>513949445</td>\n",
       "        <td>0.25</td>\n",
       "        <td>10.2</td>\n",
       "        <td>10.2</td>\n",
       "        <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>186</td>\n",
       "        <td>us</td>\n",
       "        <td>United States</td>\n",
       "        <td>9826675</td>\n",
       "        <td>9161966</td>\n",
       "        <td>664709</td>\n",
       "        <td>321368864</td>\n",
       "        <td>0.78</td>\n",
       "        <td>12.49</td>\n",
       "        <td>8.15</td>\n",
       "        <td>3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>id</td>\n",
       "        <td>Indonesia</td>\n",
       "        <td>1904569</td>\n",
       "        <td>1811569</td>\n",
       "        <td>93000</td>\n",
       "        <td>255993674</td>\n",
       "        <td>0.92</td>\n",
       "        <td>16.72</td>\n",
       "        <td>6.37</td>\n",
       "        <td>1.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>br</td>\n",
       "        <td>Brazil</td>\n",
       "        <td>8515770</td>\n",
       "        <td>8358140</td>\n",
       "        <td>157630</td>\n",
       "        <td>204259812</td>\n",
       "        <td>0.77</td>\n",
       "        <td>14.46</td>\n",
       "        <td>6.58</td>\n",
       "        <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>132</td>\n",
       "        <td>pk</td>\n",
       "        <td>Pakistan</td>\n",
       "        <td>796095</td>\n",
       "        <td>770875</td>\n",
       "        <td>25220</td>\n",
       "        <td>199085847</td>\n",
       "        <td>1.46</td>\n",
       "        <td>22.58</td>\n",
       "        <td>6.49</td>\n",
       "        <td>1.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>ni</td>\n",
       "        <td>Nigeria</td>\n",
       "        <td>923768</td>\n",
       "        <td>910768</td>\n",
       "        <td>13000</td>\n",
       "        <td>181562056</td>\n",
       "        <td>2.45</td>\n",
       "        <td>37.64</td>\n",
       "        <td>12.9</td>\n",
       "        <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>bg</td>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>148460</td>\n",
       "        <td>130170</td>\n",
       "        <td>18290</td>\n",
       "        <td>168957745</td>\n",
       "        <td>1.6</td>\n",
       "        <td>21.14</td>\n",
       "        <td>5.61</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>143</td>\n",
       "        <td>rs</td>\n",
       "        <td>Russia</td>\n",
       "        <td>17098242</td>\n",
       "        <td>16377742</td>\n",
       "        <td>720500</td>\n",
       "        <td>142423773</td>\n",
       "        <td>0.04</td>\n",
       "        <td>11.6</td>\n",
       "        <td>13.69</td>\n",
       "        <td>1.69</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(37, 'ch', 'China', 9596960, 9326410, 270550, 1367485388, 0.45, 12.49, 7.53, 0.44),\n",
       " (77, 'in', 'India', 3287263, 2973193, 314070, 1251695584, 1.22, 19.55, 7.32, 0.04),\n",
       " (197, 'ee', 'European Union', 4324782, None, None, 513949445, 0.25, 10.2, 10.2, 2.5),\n",
       " (186, 'us', 'United States', 9826675, 9161966, 664709, 321368864, 0.78, 12.49, 8.15, 3.86),\n",
       " (78, 'id', 'Indonesia', 1904569, 1811569, 93000, 255993674, 0.92, 16.72, 6.37, 1.16),\n",
       " (24, 'br', 'Brazil', 8515770, 8358140, 157630, 204259812, 0.77, 14.46, 6.58, 0.14),\n",
       " (132, 'pk', 'Pakistan', 796095, 770875, 25220, 199085847, 1.46, 22.58, 6.49, 1.54),\n",
       " (129, 'ni', 'Nigeria', 923768, 910768, 13000, 181562056, 2.45, 37.64, 12.9, 0.22),\n",
       " (14, 'bg', 'Bangladesh', 148460, 130170, 18290, 168957745, 1.6, 21.14, 5.61, 0.46),\n",
       " (143, 'rs', 'Russia', 17098242, 16377742, 720500, 142423773, 0.04, 11.6, 13.69, 1.69)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "    WHERE name <> 'World'\n",
    "    ORDER BY population DESC\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "China and India are the most populous countries with a population of over a billion people, followed by the European Union. Since the European is made up of several countries, we are going to redo the query,this time excluding the European Union to give us a move accurate output of the most populous countries. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>ch</td>\n",
       "        <td>China</td>\n",
       "        <td>9596960</td>\n",
       "        <td>9326410</td>\n",
       "        <td>270550</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.45</td>\n",
       "        <td>12.49</td>\n",
       "        <td>7.53</td>\n",
       "        <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>in</td>\n",
       "        <td>India</td>\n",
       "        <td>3287263</td>\n",
       "        <td>2973193</td>\n",
       "        <td>314070</td>\n",
       "        <td>1251695584</td>\n",
       "        <td>1.22</td>\n",
       "        <td>19.55</td>\n",
       "        <td>7.32</td>\n",
       "        <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>186</td>\n",
       "        <td>us</td>\n",
       "        <td>United States</td>\n",
       "        <td>9826675</td>\n",
       "        <td>9161966</td>\n",
       "        <td>664709</td>\n",
       "        <td>321368864</td>\n",
       "        <td>0.78</td>\n",
       "        <td>12.49</td>\n",
       "        <td>8.15</td>\n",
       "        <td>3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>id</td>\n",
       "        <td>Indonesia</td>\n",
       "        <td>1904569</td>\n",
       "        <td>1811569</td>\n",
       "        <td>93000</td>\n",
       "        <td>255993674</td>\n",
       "        <td>0.92</td>\n",
       "        <td>16.72</td>\n",
       "        <td>6.37</td>\n",
       "        <td>1.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>br</td>\n",
       "        <td>Brazil</td>\n",
       "        <td>8515770</td>\n",
       "        <td>8358140</td>\n",
       "        <td>157630</td>\n",
       "        <td>204259812</td>\n",
       "        <td>0.77</td>\n",
       "        <td>14.46</td>\n",
       "        <td>6.58</td>\n",
       "        <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>132</td>\n",
       "        <td>pk</td>\n",
       "        <td>Pakistan</td>\n",
       "        <td>796095</td>\n",
       "        <td>770875</td>\n",
       "        <td>25220</td>\n",
       "        <td>199085847</td>\n",
       "        <td>1.46</td>\n",
       "        <td>22.58</td>\n",
       "        <td>6.49</td>\n",
       "        <td>1.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>129</td>\n",
       "        <td>ni</td>\n",
       "        <td>Nigeria</td>\n",
       "        <td>923768</td>\n",
       "        <td>910768</td>\n",
       "        <td>13000</td>\n",
       "        <td>181562056</td>\n",
       "        <td>2.45</td>\n",
       "        <td>37.64</td>\n",
       "        <td>12.9</td>\n",
       "        <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>bg</td>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>148460</td>\n",
       "        <td>130170</td>\n",
       "        <td>18290</td>\n",
       "        <td>168957745</td>\n",
       "        <td>1.6</td>\n",
       "        <td>21.14</td>\n",
       "        <td>5.61</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>143</td>\n",
       "        <td>rs</td>\n",
       "        <td>Russia</td>\n",
       "        <td>17098242</td>\n",
       "        <td>16377742</td>\n",
       "        <td>720500</td>\n",
       "        <td>142423773</td>\n",
       "        <td>0.04</td>\n",
       "        <td>11.6</td>\n",
       "        <td>13.69</td>\n",
       "        <td>1.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>ja</td>\n",
       "        <td>Japan</td>\n",
       "        <td>377915</td>\n",
       "        <td>364485</td>\n",
       "        <td>13430</td>\n",
       "        <td>126919659</td>\n",
       "        <td>0.16</td>\n",
       "        <td>7.93</td>\n",
       "        <td>9.51</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(37, 'ch', 'China', 9596960, 9326410, 270550, 1367485388, 0.45, 12.49, 7.53, 0.44),\n",
       " (77, 'in', 'India', 3287263, 2973193, 314070, 1251695584, 1.22, 19.55, 7.32, 0.04),\n",
       " (186, 'us', 'United States', 9826675, 9161966, 664709, 321368864, 0.78, 12.49, 8.15, 3.86),\n",
       " (78, 'id', 'Indonesia', 1904569, 1811569, 93000, 255993674, 0.92, 16.72, 6.37, 1.16),\n",
       " (24, 'br', 'Brazil', 8515770, 8358140, 157630, 204259812, 0.77, 14.46, 6.58, 0.14),\n",
       " (132, 'pk', 'Pakistan', 796095, 770875, 25220, 199085847, 1.46, 22.58, 6.49, 1.54),\n",
       " (129, 'ni', 'Nigeria', 923768, 910768, 13000, 181562056, 2.45, 37.64, 12.9, 0.22),\n",
       " (14, 'bg', 'Bangladesh', 148460, 130170, 18290, 168957745, 1.6, 21.14, 5.61, 0.46),\n",
       " (143, 'rs', 'Russia', 17098242, 16377742, 720500, 142423773, 0.04, 11.6, 13.69, 1.69),\n",
       " (85, 'ja', 'Japan', 377915, 364485, 13430, 126919659, 0.16, 7.93, 9.51, 0.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "    WHERE name <> 'World'\n",
    "    AND name <> 'European Union'\n",
    "    ORDER BY population DESC\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "China with a population of nearly 1.4 billion, India with nearly 1.3 billion and The United States with nearly 322 million are the 3 most populous countries. Indonesia, Brazil, Pakistan, Nigeria, Bangladesh, Russia and Japan in that orde, completes the top 10 moat populous countries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Population Density\n",
    "* To get the population density of countries, we are going to look at countries with population above the average and area below the average.\n",
    "\n",
    "* We are going to make sure to discard of the rows that contains World and European Union as this can skew the averages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AVG(population)</th>\n",
       "        <th>AVG(area)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30235554.991666667</td>\n",
       "        <td>539893.1895161291</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(30235554.991666667, 539893.1895161291)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(population), AVG(area)\n",
    "  FROM facts\n",
    "  WHERE name <> 'World'\n",
    "  AND name <> 'European Union';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>bg</td>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>148460</td>\n",
       "        <td>130170</td>\n",
       "        <td>18290</td>\n",
       "        <td>168957745</td>\n",
       "        <td>1.6</td>\n",
       "        <td>21.14</td>\n",
       "        <td>5.61</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>gm</td>\n",
       "        <td>Germany</td>\n",
       "        <td>357022</td>\n",
       "        <td>348672</td>\n",
       "        <td>8350</td>\n",
       "        <td>80854408</td>\n",
       "        <td>0.17</td>\n",
       "        <td>8.47</td>\n",
       "        <td>11.42</td>\n",
       "        <td>1.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>iz</td>\n",
       "        <td>Iraq</td>\n",
       "        <td>438317</td>\n",
       "        <td>437367</td>\n",
       "        <td>950</td>\n",
       "        <td>37056169</td>\n",
       "        <td>2.93</td>\n",
       "        <td>31.45</td>\n",
       "        <td>3.77</td>\n",
       "        <td>1.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>it</td>\n",
       "        <td>Italy</td>\n",
       "        <td>301340</td>\n",
       "        <td>294140</td>\n",
       "        <td>7200</td>\n",
       "        <td>61855120</td>\n",
       "        <td>0.27</td>\n",
       "        <td>8.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>ja</td>\n",
       "        <td>Japan</td>\n",
       "        <td>377915</td>\n",
       "        <td>364485</td>\n",
       "        <td>13430</td>\n",
       "        <td>126919659</td>\n",
       "        <td>0.16</td>\n",
       "        <td>7.93</td>\n",
       "        <td>9.51</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>ks</td>\n",
       "        <td>Korea, South</td>\n",
       "        <td>99720</td>\n",
       "        <td>96920</td>\n",
       "        <td>2800</td>\n",
       "        <td>49115196</td>\n",
       "        <td>0.14</td>\n",
       "        <td>8.19</td>\n",
       "        <td>6.75</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>my</td>\n",
       "        <td>Malaysia</td>\n",
       "        <td>329847</td>\n",
       "        <td>328657</td>\n",
       "        <td>1190</td>\n",
       "        <td>30513848</td>\n",
       "        <td>1.44</td>\n",
       "        <td>19.71</td>\n",
       "        <td>5.03</td>\n",
       "        <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>120</td>\n",
       "        <td>mo</td>\n",
       "        <td>Morocco</td>\n",
       "        <td>446550</td>\n",
       "        <td>446300</td>\n",
       "        <td>250</td>\n",
       "        <td>33322699</td>\n",
       "        <td>1.0</td>\n",
       "        <td>18.2</td>\n",
       "        <td>4.81</td>\n",
       "        <td>3.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>np</td>\n",
       "        <td>Nepal</td>\n",
       "        <td>147181</td>\n",
       "        <td>143351</td>\n",
       "        <td>3830</td>\n",
       "        <td>31551305</td>\n",
       "        <td>1.79</td>\n",
       "        <td>20.64</td>\n",
       "        <td>6.56</td>\n",
       "        <td>3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>138</td>\n",
       "        <td>rp</td>\n",
       "        <td>Philippines</td>\n",
       "        <td>300000</td>\n",
       "        <td>298170</td>\n",
       "        <td>1830</td>\n",
       "        <td>100998376</td>\n",
       "        <td>1.61</td>\n",
       "        <td>24.27</td>\n",
       "        <td>6.11</td>\n",
       "        <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>139</td>\n",
       "        <td>pl</td>\n",
       "        <td>Poland</td>\n",
       "        <td>312685</td>\n",
       "        <td>304255</td>\n",
       "        <td>8430</td>\n",
       "        <td>38562189</td>\n",
       "        <td>0.09</td>\n",
       "        <td>9.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>163</td>\n",
       "        <td>sp</td>\n",
       "        <td>Spain</td>\n",
       "        <td>505370</td>\n",
       "        <td>498980</td>\n",
       "        <td>6390</td>\n",
       "        <td>48146134</td>\n",
       "        <td>0.89</td>\n",
       "        <td>9.64</td>\n",
       "        <td>9.04</td>\n",
       "        <td>8.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>173</td>\n",
       "        <td>th</td>\n",
       "        <td>Thailand</td>\n",
       "        <td>513120</td>\n",
       "        <td>510890</td>\n",
       "        <td>2230</td>\n",
       "        <td>67976405</td>\n",
       "        <td>0.34</td>\n",
       "        <td>11.19</td>\n",
       "        <td>7.8</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>182</td>\n",
       "        <td>ug</td>\n",
       "        <td>Uganda</td>\n",
       "        <td>241038</td>\n",
       "        <td>197100</td>\n",
       "        <td>43938</td>\n",
       "        <td>37101745</td>\n",
       "        <td>3.24</td>\n",
       "        <td>43.79</td>\n",
       "        <td>10.69</td>\n",
       "        <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>185</td>\n",
       "        <td>uk</td>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>243610</td>\n",
       "        <td>241930</td>\n",
       "        <td>1680</td>\n",
       "        <td>64088222</td>\n",
       "        <td>0.54</td>\n",
       "        <td>12.17</td>\n",
       "        <td>9.35</td>\n",
       "        <td>2.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>192</td>\n",
       "        <td>vm</td>\n",
       "        <td>Vietnam</td>\n",
       "        <td>331210</td>\n",
       "        <td>310070</td>\n",
       "        <td>21140</td>\n",
       "        <td>94348835</td>\n",
       "        <td>0.97</td>\n",
       "        <td>15.96</td>\n",
       "        <td>5.93</td>\n",
       "        <td>0.3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(14, 'bg', 'Bangladesh', 148460, 130170, 18290, 168957745, 1.6, 21.14, 5.61, 0.46),\n",
       " (65, 'gm', 'Germany', 357022, 348672, 8350, 80854408, 0.17, 8.47, 11.42, 1.24),\n",
       " (80, 'iz', 'Iraq', 438317, 437367, 950, 37056169, 2.93, 31.45, 3.77, 1.62),\n",
       " (83, 'it', 'Italy', 301340, 294140, 7200, 61855120, 0.27, 8.74, 10.19, 4.1),\n",
       " (85, 'ja', 'Japan', 377915, 364485, 13430, 126919659, 0.16, 7.93, 9.51, 0.0),\n",
       " (91, 'ks', 'Korea, South', 99720, 96920, 2800, 49115196, 0.14, 8.19, 6.75, 0.0),\n",
       " (107, 'my', 'Malaysia', 329847, 328657, 1190, 30513848, 1.44, 19.71, 5.03, 0.33),\n",
       " (120, 'mo', 'Morocco', 446550, 446300, 250, 33322699, 1.0, 18.2, 4.81, 3.36),\n",
       " (124, 'np', 'Nepal', 147181, 143351, 3830, 31551305, 1.79, 20.64, 6.56, 3.86),\n",
       " (138, 'rp', 'Philippines', 300000, 298170, 1830, 100998376, 1.61, 24.27, 6.11, 2.09),\n",
       " (139, 'pl', 'Poland', 312685, 304255, 8430, 38562189, 0.09, 9.74, 10.19, 0.46),\n",
       " (163, 'sp', 'Spain', 505370, 498980, 6390, 48146134, 0.89, 9.64, 9.04, 8.31),\n",
       " (173, 'th', 'Thailand', 513120, 510890, 2230, 67976405, 0.34, 11.19, 7.8, 0.0),\n",
       " (182, 'ug', 'Uganda', 241038, 197100, 43938, 37101745, 3.24, 43.79, 10.69, 0.74),\n",
       " (185, 'uk', 'United Kingdom', 243610, 241930, 1680, 64088222, 0.54, 12.17, 9.35, 2.54),\n",
       " (192, 'vm', 'Vietnam', 331210, 310070, 21140, 94348835, 0.97, 15.96, 5.93, 0.3)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "  WHERE population > (SELECT AVG(population)\n",
    "                      FROM facts\n",
    "                     WHERE name <> 'World'\n",
    "                     AND name <> 'European Union')\n",
    "  AND area < (SELECT AVG(area)\n",
    "             FROM facts\n",
    "             WHERE name <> 'World'\n",
    "             AND name <> 'European Union');\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bangladesh, Germany and Iraq are the top 3 countries with the highest population density."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Population To Area Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pop_area_ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macau</td>\n",
       "        <td>21168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>15267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Singapore</td>\n",
       "        <td>8141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hong Kong</td>\n",
       "        <td>6445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gaza Strip</td>\n",
       "        <td>5191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gibraltar</td>\n",
       "        <td>4876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahrain</td>\n",
       "        <td>1771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maldives</td>\n",
       "        <td>1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malta</td>\n",
       "        <td>1310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bermuda</td>\n",
       "        <td>1299</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Macau', 21168),\n",
       " ('Monaco', 15267),\n",
       " ('Singapore', 8141),\n",
       " ('Hong Kong', 6445),\n",
       " ('Gaza Strip', 5191),\n",
       " ('Gibraltar', 4876),\n",
       " ('Bahrain', 1771),\n",
       " ('Maldives', 1319),\n",
       " ('Malta', 1310),\n",
       " ('Bermuda', 1299)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name,\n",
    "       population/area AS pop_area_ratio\n",
    "  FROM facts\n",
    "  ORDER BY pop_area_ratio DESC\n",
    "  LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The countries with high population to area ratio as we expected are countries that do not have a high population density."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Countries With High Population Growth Rate\n",
    "\n",
    "* We are going to find out the countries with the top 10 population growth rate.\n",
    "* We are going to estimate by how much the population will grow the following year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>162</td>\n",
       "        <td>od</td>\n",
       "        <td>South Sudan</td>\n",
       "        <td>644329</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>12042910</td>\n",
       "        <td>4.02</td>\n",
       "        <td>36.91</td>\n",
       "        <td>8.18</td>\n",
       "        <td>11.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>106</td>\n",
       "        <td>mi</td>\n",
       "        <td>Malawi</td>\n",
       "        <td>118484</td>\n",
       "        <td>94080</td>\n",
       "        <td>24404</td>\n",
       "        <td>17964697</td>\n",
       "        <td>3.32</td>\n",
       "        <td>41.56</td>\n",
       "        <td>8.41</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>by</td>\n",
       "        <td>Burundi</td>\n",
       "        <td>27830</td>\n",
       "        <td>25680</td>\n",
       "        <td>2150</td>\n",
       "        <td>10742276</td>\n",
       "        <td>3.28</td>\n",
       "        <td>42.01</td>\n",
       "        <td>9.27</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>128</td>\n",
       "        <td>ng</td>\n",
       "        <td>Niger</td>\n",
       "        <td>None</td>\n",
       "        <td>1266700</td>\n",
       "        <td>300</td>\n",
       "        <td>18045729</td>\n",
       "        <td>3.25</td>\n",
       "        <td>45.45</td>\n",
       "        <td>12.42</td>\n",
       "        <td>0.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>182</td>\n",
       "        <td>ug</td>\n",
       "        <td>Uganda</td>\n",
       "        <td>241038</td>\n",
       "        <td>197100</td>\n",
       "        <td>43938</td>\n",
       "        <td>37101745</td>\n",
       "        <td>3.24</td>\n",
       "        <td>43.79</td>\n",
       "        <td>10.69</td>\n",
       "        <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>141</td>\n",
       "        <td>qa</td>\n",
       "        <td>Qatar</td>\n",
       "        <td>11586</td>\n",
       "        <td>11586</td>\n",
       "        <td>0</td>\n",
       "        <td>2194817</td>\n",
       "        <td>3.07</td>\n",
       "        <td>9.84</td>\n",
       "        <td>1.53</td>\n",
       "        <td>22.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>uv</td>\n",
       "        <td>Burkina Faso</td>\n",
       "        <td>274200</td>\n",
       "        <td>273800</td>\n",
       "        <td>400</td>\n",
       "        <td>18931686</td>\n",
       "        <td>3.03</td>\n",
       "        <td>42.03</td>\n",
       "        <td>11.72</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>109</td>\n",
       "        <td>ml</td>\n",
       "        <td>Mali</td>\n",
       "        <td>1240192</td>\n",
       "        <td>1220190</td>\n",
       "        <td>20002</td>\n",
       "        <td>16955536</td>\n",
       "        <td>2.98</td>\n",
       "        <td>44.99</td>\n",
       "        <td>12.89</td>\n",
       "        <td>2.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>219</td>\n",
       "        <td>cw</td>\n",
       "        <td>Cook Islands</td>\n",
       "        <td>236</td>\n",
       "        <td>236</td>\n",
       "        <td>0</td>\n",
       "        <td>9838</td>\n",
       "        <td>2.95</td>\n",
       "        <td>14.33</td>\n",
       "        <td>8.03</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>iz</td>\n",
       "        <td>Iraq</td>\n",
       "        <td>438317</td>\n",
       "        <td>437367</td>\n",
       "        <td>950</td>\n",
       "        <td>37056169</td>\n",
       "        <td>2.93</td>\n",
       "        <td>31.45</td>\n",
       "        <td>3.77</td>\n",
       "        <td>1.62</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(162, 'od', 'South Sudan', 644329, None, None, 12042910, 4.02, 36.91, 8.18, 11.47),\n",
       " (106, 'mi', 'Malawi', 118484, 94080, 24404, 17964697, 3.32, 41.56, 8.41, 0.0),\n",
       " (29, 'by', 'Burundi', 27830, 25680, 2150, 10742276, 3.28, 42.01, 9.27, 0.0),\n",
       " (128, 'ng', 'Niger', None, 1266700, 300, 18045729, 3.25, 45.45, 12.42, 0.56),\n",
       " (182, 'ug', 'Uganda', 241038, 197100, 43938, 37101745, 3.24, 43.79, 10.69, 0.74),\n",
       " (141, 'qa', 'Qatar', 11586, 11586, 0, 2194817, 3.07, 9.84, 1.53, 22.39),\n",
       " (27, 'uv', 'Burkina Faso', 274200, 273800, 400, 18931686, 3.03, 42.03, 11.72, 0.0),\n",
       " (109, 'ml', 'Mali', 1240192, 1220190, 20002, 16955536, 2.98, 44.99, 12.89, 2.26),\n",
       " (219, 'cw', 'Cook Islands', 236, 236, 0, 9838, 2.95, 14.33, 8.03, None),\n",
       " (80, 'iz', 'Iraq', 438317, 437367, 950, 37056169, 2.93, 31.45, 3.77, 1.62)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "  ORDER BY population_growth DESC\n",
    "  LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "About 7 countries with high population growth rate are African countires. With South Sudan having the highest population growth rate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding Countries With The Highest Estimated Growth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>Estimated_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>15270686.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6153684.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "        <td>4448270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "        <td>2906653.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethiopia</td>\n",
       "        <td>2874562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>2703324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>2506677.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>2355142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Congo, Democratic Republic of the</td>\n",
       "        <td>1944691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>1626074.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('India', 15270686.0),\n",
       " ('China', 6153684.0),\n",
       " ('Nigeria', 4448270.0),\n",
       " ('Pakistan', 2906653.0),\n",
       " ('Ethiopia', 2874562.0),\n",
       " ('Bangladesh', 2703324.0),\n",
       " ('United States', 2506677.0),\n",
       " ('Indonesia', 2355142.0),\n",
       " ('Congo, Democratic Republic of the', 1944691.0),\n",
       " ('Philippines', 1626074.0)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name,\n",
    "       ROUND((population_growth * population)/100) AS Estimated_growth\n",
    "  FROM facts\n",
    "  WHERE name <> 'World'\n",
    "  ORDER BY Estimated_growth DESC\n",
    "  LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our table shows that India is the country with the highest expected annual population growth estimate with just over 15million, followed by China with Just over 6million and the Nigeria with over 440,000."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Countries With Higher Death Rate Than Birth Rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>au</td>\n",
       "        <td>Austria</td>\n",
       "        <td>83871</td>\n",
       "        <td>82445</td>\n",
       "        <td>1426</td>\n",
       "        <td>8665550</td>\n",
       "        <td>0.55</td>\n",
       "        <td>9.41</td>\n",
       "        <td>9.42</td>\n",
       "        <td>5.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>bo</td>\n",
       "        <td>Belarus</td>\n",
       "        <td>207600</td>\n",
       "        <td>202900</td>\n",
       "        <td>4700</td>\n",
       "        <td>9589689</td>\n",
       "        <td>0.2</td>\n",
       "        <td>10.7</td>\n",
       "        <td>13.36</td>\n",
       "        <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>bk</td>\n",
       "        <td>Bosnia and Herzegovina</td>\n",
       "        <td>51197</td>\n",
       "        <td>51187</td>\n",
       "        <td>10</td>\n",
       "        <td>3867055</td>\n",
       "        <td>0.13</td>\n",
       "        <td>8.87</td>\n",
       "        <td>9.75</td>\n",
       "        <td>0.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>bu</td>\n",
       "        <td>Bulgaria</td>\n",
       "        <td>110879</td>\n",
       "        <td>108489</td>\n",
       "        <td>2390</td>\n",
       "        <td>7186893</td>\n",
       "        <td>0.58</td>\n",
       "        <td>8.92</td>\n",
       "        <td>14.44</td>\n",
       "        <td>0.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>hr</td>\n",
       "        <td>Croatia</td>\n",
       "        <td>56594</td>\n",
       "        <td>55974</td>\n",
       "        <td>620</td>\n",
       "        <td>4464844</td>\n",
       "        <td>0.13</td>\n",
       "        <td>9.45</td>\n",
       "        <td>12.18</td>\n",
       "        <td>1.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>ez</td>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>78867</td>\n",
       "        <td>77247</td>\n",
       "        <td>1620</td>\n",
       "        <td>10644842</td>\n",
       "        <td>0.16</td>\n",
       "        <td>9.63</td>\n",
       "        <td>10.34</td>\n",
       "        <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>en</td>\n",
       "        <td>Estonia</td>\n",
       "        <td>45228</td>\n",
       "        <td>42388</td>\n",
       "        <td>2840</td>\n",
       "        <td>1265420</td>\n",
       "        <td>0.55</td>\n",
       "        <td>10.51</td>\n",
       "        <td>12.4</td>\n",
       "        <td>3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>gm</td>\n",
       "        <td>Germany</td>\n",
       "        <td>357022</td>\n",
       "        <td>348672</td>\n",
       "        <td>8350</td>\n",
       "        <td>80854408</td>\n",
       "        <td>0.17</td>\n",
       "        <td>8.47</td>\n",
       "        <td>11.42</td>\n",
       "        <td>1.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>gr</td>\n",
       "        <td>Greece</td>\n",
       "        <td>131957</td>\n",
       "        <td>130647</td>\n",
       "        <td>1310</td>\n",
       "        <td>10775643</td>\n",
       "        <td>0.01</td>\n",
       "        <td>8.66</td>\n",
       "        <td>11.09</td>\n",
       "        <td>2.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>hu</td>\n",
       "        <td>Hungary</td>\n",
       "        <td>93028</td>\n",
       "        <td>89608</td>\n",
       "        <td>3420</td>\n",
       "        <td>9897541</td>\n",
       "        <td>0.22</td>\n",
       "        <td>9.16</td>\n",
       "        <td>12.73</td>\n",
       "        <td>1.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>it</td>\n",
       "        <td>Italy</td>\n",
       "        <td>301340</td>\n",
       "        <td>294140</td>\n",
       "        <td>7200</td>\n",
       "        <td>61855120</td>\n",
       "        <td>0.27</td>\n",
       "        <td>8.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>ja</td>\n",
       "        <td>Japan</td>\n",
       "        <td>377915</td>\n",
       "        <td>364485</td>\n",
       "        <td>13430</td>\n",
       "        <td>126919659</td>\n",
       "        <td>0.16</td>\n",
       "        <td>7.93</td>\n",
       "        <td>9.51</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>lg</td>\n",
       "        <td>Latvia</td>\n",
       "        <td>64589</td>\n",
       "        <td>62249</td>\n",
       "        <td>2340</td>\n",
       "        <td>1986705</td>\n",
       "        <td>1.06</td>\n",
       "        <td>10.0</td>\n",
       "        <td>14.31</td>\n",
       "        <td>6.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>lh</td>\n",
       "        <td>Lithuania</td>\n",
       "        <td>65300</td>\n",
       "        <td>62680</td>\n",
       "        <td>2620</td>\n",
       "        <td>2884433</td>\n",
       "        <td>1.04</td>\n",
       "        <td>10.1</td>\n",
       "        <td>14.27</td>\n",
       "        <td>6.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>116</td>\n",
       "        <td>md</td>\n",
       "        <td>Moldova</td>\n",
       "        <td>33851</td>\n",
       "        <td>32891</td>\n",
       "        <td>960</td>\n",
       "        <td>3546847</td>\n",
       "        <td>1.03</td>\n",
       "        <td>12.0</td>\n",
       "        <td>12.59</td>\n",
       "        <td>9.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>117</td>\n",
       "        <td>mn</td>\n",
       "        <td>Monaco</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>30535</td>\n",
       "        <td>0.12</td>\n",
       "        <td>6.65</td>\n",
       "        <td>9.24</td>\n",
       "        <td>3.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>139</td>\n",
       "        <td>pl</td>\n",
       "        <td>Poland</td>\n",
       "        <td>312685</td>\n",
       "        <td>304255</td>\n",
       "        <td>8430</td>\n",
       "        <td>38562189</td>\n",
       "        <td>0.09</td>\n",
       "        <td>9.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>140</td>\n",
       "        <td>po</td>\n",
       "        <td>Portugal</td>\n",
       "        <td>92090</td>\n",
       "        <td>91470</td>\n",
       "        <td>620</td>\n",
       "        <td>10825309</td>\n",
       "        <td>0.09</td>\n",
       "        <td>9.27</td>\n",
       "        <td>11.02</td>\n",
       "        <td>2.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>142</td>\n",
       "        <td>ro</td>\n",
       "        <td>Romania</td>\n",
       "        <td>238391</td>\n",
       "        <td>229891</td>\n",
       "        <td>8500</td>\n",
       "        <td>21666350</td>\n",
       "        <td>0.3</td>\n",
       "        <td>9.14</td>\n",
       "        <td>11.9</td>\n",
       "        <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>143</td>\n",
       "        <td>rs</td>\n",
       "        <td>Russia</td>\n",
       "        <td>17098242</td>\n",
       "        <td>16377742</td>\n",
       "        <td>720500</td>\n",
       "        <td>142423773</td>\n",
       "        <td>0.04</td>\n",
       "        <td>11.6</td>\n",
       "        <td>13.69</td>\n",
       "        <td>1.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>153</td>\n",
       "        <td>ri</td>\n",
       "        <td>Serbia</td>\n",
       "        <td>77474</td>\n",
       "        <td>77474</td>\n",
       "        <td>0</td>\n",
       "        <td>7176794</td>\n",
       "        <td>0.46</td>\n",
       "        <td>9.08</td>\n",
       "        <td>13.66</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>158</td>\n",
       "        <td>si</td>\n",
       "        <td>Slovenia</td>\n",
       "        <td>20273</td>\n",
       "        <td>20151</td>\n",
       "        <td>122</td>\n",
       "        <td>1983412</td>\n",
       "        <td>0.26</td>\n",
       "        <td>8.42</td>\n",
       "        <td>11.37</td>\n",
       "        <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>183</td>\n",
       "        <td>up</td>\n",
       "        <td>Ukraine</td>\n",
       "        <td>603550</td>\n",
       "        <td>579330</td>\n",
       "        <td>24220</td>\n",
       "        <td>44429471</td>\n",
       "        <td>0.6</td>\n",
       "        <td>10.72</td>\n",
       "        <td>14.46</td>\n",
       "        <td>2.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>214</td>\n",
       "        <td>sb</td>\n",
       "        <td>Saint Pierre and Miquelon</td>\n",
       "        <td>242</td>\n",
       "        <td>242</td>\n",
       "        <td>0</td>\n",
       "        <td>5657</td>\n",
       "        <td>1.08</td>\n",
       "        <td>7.42</td>\n",
       "        <td>9.72</td>\n",
       "        <td>8.49</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 'au', 'Austria', 83871, 82445, 1426, 8665550, 0.55, 9.41, 9.42, 5.56),\n",
       " (16, 'bo', 'Belarus', 207600, 202900, 4700, 9589689, 0.2, 10.7, 13.36, 0.7),\n",
       " (22, 'bk', 'Bosnia and Herzegovina', 51197, 51187, 10, 3867055, 0.13, 8.87, 9.75, 0.38),\n",
       " (26, 'bu', 'Bulgaria', 110879, 108489, 2390, 7186893, 0.58, 8.92, 14.44, 0.29),\n",
       " (44, 'hr', 'Croatia', 56594, 55974, 620, 4464844, 0.13, 9.45, 12.18, 1.39),\n",
       " (47, 'ez', 'Czech Republic', 78867, 77247, 1620, 10644842, 0.16, 9.63, 10.34, 2.33),\n",
       " (57, 'en', 'Estonia', 45228, 42388, 2840, 1265420, 0.55, 10.51, 12.4, 3.6),\n",
       " (65, 'gm', 'Germany', 357022, 348672, 8350, 80854408, 0.17, 8.47, 11.42, 1.24),\n",
       " (67, 'gr', 'Greece', 131957, 130647, 1310, 10775643, 0.01, 8.66, 11.09, 2.32),\n",
       " (75, 'hu', 'Hungary', 93028, 89608, 3420, 9897541, 0.22, 9.16, 12.73, 1.33),\n",
       " (83, 'it', 'Italy', 301340, 294140, 7200, 61855120, 0.27, 8.74, 10.19, 4.1),\n",
       " (85, 'ja', 'Japan', 377915, 364485, 13430, 126919659, 0.16, 7.93, 9.51, 0.0),\n",
       " (96, 'lg', 'Latvia', 64589, 62249, 2340, 1986705, 1.06, 10.0, 14.31, 6.26),\n",
       " (102, 'lh', 'Lithuania', 65300, 62680, 2620, 2884433, 1.04, 10.1, 14.27, 6.27),\n",
       " (116, 'md', 'Moldova', 33851, 32891, 960, 3546847, 1.03, 12.0, 12.59, 9.67),\n",
       " (117, 'mn', 'Monaco', 2, 2, 0, 30535, 0.12, 6.65, 9.24, 3.83),\n",
       " (139, 'pl', 'Poland', 312685, 304255, 8430, 38562189, 0.09, 9.74, 10.19, 0.46),\n",
       " (140, 'po', 'Portugal', 92090, 91470, 620, 10825309, 0.09, 9.27, 11.02, 2.67),\n",
       " (142, 'ro', 'Romania', 238391, 229891, 8500, 21666350, 0.3, 9.14, 11.9, 0.24),\n",
       " (143, 'rs', 'Russia', 17098242, 16377742, 720500, 142423773, 0.04, 11.6, 13.69, 1.69),\n",
       " (153, 'ri', 'Serbia', 77474, 77474, 0, 7176794, 0.46, 9.08, 13.66, 0.0),\n",
       " (158, 'si', 'Slovenia', 20273, 20151, 122, 1983412, 0.26, 8.42, 11.37, 0.37),\n",
       " (183, 'up', 'Ukraine', 603550, 579330, 24220, 44429471, 0.6, 10.72, 14.46, 2.25),\n",
       " (214, 'sb', 'Saint Pierre and Miquelon', 242, 242, 0, 5657, 1.08, 7.42, 9.72, 8.49)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    "  WHERE death_rate > birth_rate;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the exception of Japan, all of the countries with death rate higher than birth rate are all European countries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CONCLUSION\n",
    "Our goal at the start was to find the most populous countries, the countries with high population density, countries with high growth rate, etc. After our analysis we have discovered the following:\n",
    "\n",
    "1. China, India and The United States are the 3 most populous nations.\n",
    "2. Bangladesh, Germany and Iraq have the highest population density.\n",
    "3. The countries with the top 10 highest growth rate were mainly made up of African countries.\n",
    "4. India, China and Nigeria are the top 3 countries whose population is expected to grow the most the following year.\n",
    "5. Most of the countries that have death rate higher than birth rate are European countries with the exception of Japan."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}