Analyzing Country Statistics Using SQL

In this project, we'll be analyzing statistics about all of the countries on Earth. The main aim is just to practice some SQL, so we'll be limiting our exploration to:

Population and population growth
Average population and area
Densely-populated countriies

Also note that you won't be able to run this project on Deepnote. I completed it locally and manually uploaded it to Deepnote to publish it. Silly I know, I'm building a Jupyter publishing service so I no longer need to do this.

Data Overview

We'll be working with SQL data from the CIA World Factbook.

The Factbook contains demographic information like the following:

population: The global population.
population_growth: The annual population growth rate, as a percentage.
area: The total land and water area.

Let's preview some of the information:

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

%%sql SELECT * FROM facts LIMIT 5;

Data Analysis

Population and Population Growth

Let's run some SQL queries to explore general population and population growth information

%%sql SELECT MIN(population) as min_population FROM facts;

%%sql SELECT MAX(population) as max_population FROM facts;

%%sql SELECT MIN(population_growth) as min_population_growth FROM facts;

%%sql SELECT MAX(population_growth) as max_population_growth FROM facts;

From these queries, we can see a few interesting things:

There's a country with a population of 0.
There's a country with a population greater than 7256490011 (7.2 billion)

Let's see what who these guilty countries are

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

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

It looks like the database table contains a row for the World, which explains the 7.2 billion.

Similarly, having a row for Antarctica explains the population value of 0.

Average Population and Area

Let's find the average population and area for a country, excluding World which would bias the results

%%sql SELECT AVG(population) FROM facts WHERE name != "World";

%%sql SELECT AVG(area) FROM facts WHERE name != "World";

This highlights that the average country population size is 32,242,666 million, while the average area is 555,093 sq km.

Densely-Populated Countries

Given that we know the average population and area, let's find densely-populated countries. We'll consider them densely populated if they:

have a population size greater than the average
have an area less than the average

%%sql SELECT name FROM facts WHERE name != "World" and population > (SELECT AVG(population) FROM facts WHERE name != "World") and ar > (SELECT AVG(population) FROM facts WHERE name != "World")