Review: Analyzing CIA Factbook Data Using SQL

Hi all,

Sharing my completed guided project - Analyzing CIA Factbook Data Using SQL.

Analyzing-CIA-Factbook-Data-Using-SQL.ipynb (22.2 KB)

https://app.dataquest.io/m/257/guided-project%3A-analyzing-cia-factbook-data-using-sql/6/finding-densely-populated-countries

I’d like to the reviewers to check the correctness of the final three queries in particular in the project (the ones left for us to explore here)

Looking forward to your comments and suggestions.

Thanks,
Anjali

Click here to view the jupyter notebook file in a new tab

1 Like

Thanks for sharing this project, Anjali! Everything looks good, organized with headings and analysis comments in the Markdown cells. For the final queries, it looks over all pretty good. Regarding the water/land ratio question, it looks like from the table that only 2 of the countries in the list have more water area than land area (ratio > 1, though Puerto Rico is still pretty high). In the population growth question the database seems to have a line where they combined the countries in the European Union so it shows up in the list like the World population (I wonder how the breakdown of that segment would look…). I’ll bet there are a lot of other interesting questions we could answer from this dataset as well, like the countries with declines in populations. (It wasn’t asked, but seeing this made me curious!)

2 Likes

I just went through this project. I think the row with ‘World’ as country needs to be filtered out for calculating the averages. My calculation return 14 densely populated countries vs 7 countries from Dataquest official example answer.

-- list of countries that are densely populated
SELECT name
FROM facts
WHERE name <> 'World' AND  
    population > (
        SELECT AVG(population)
        FROM facts
        WHERE name <> 'World') AND
     area < (
        SELECT AVG(area)
        FROM facts
        WHERE name <> 'World'
     )
name
Bangladesh
Germany
Iraq
Italy
Japan
Korea, South
Morocco
Philippines
Poland
Spain
Thailand
Uganda
United Kingdom
Vietnam
2 Likes

Hey @rwong, I did take another close look at my project and what you’ve said here. I think you are right and it is the way to go to find the densely populated countries. Thanks for pointing.

Hi @rwong, even my output displayed 14 densely populated countries. For simplifying your code, you could have excluded – name <> ‘World’ AND – after the first WHERE clause.

SELECT name
FROM facts
WHERE population > (SELECT AVG(population) FROM facts WHERE name <> ‘World’)
AND area < (SELECT AVG(area) FROM facts WHERE name <> ‘World’);

Best,
Reshwant