Analyzing CIA Factbook Data Using SQL

kindly reviewAnalyzing CIA Factbook Data Using SQL.ipynb (100.4 KB)

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


Hey. Overall you did a nice job. Here are some observations/suggestions/opportunities for improvement:

  • Add some more comments next to the results. For example, near the beginning you found that the table has 261 rows. You could go on Google and get a sense of how complete the table is. Does it seem to have every country?

    There many opportunities throughout the project for this, I won’t explicitly call them out here.

  • Don’t forget to capitalize limit in SELECT * FROM facts limit 5;.

  • When you compute the average population, you didn’t get rid of the outliers found in the previous screen (neither did we in the solution).

    Returning to the point above, by having placed enough emphasis in the number of rows, you’d find that the average population multiplied by the number of countries yields over 16 billion people on Earth, which would stand out as incorrect. The point being that doing some work on the comments can help you spot issues.

  • When finding the countries with the highest ratios of water to land, what you’re actually doing is finding countries that have a higher than average water to land ratio. This is kind of works, but it’s somewhat inaccurate. I’d instead run something like the query below — I’m limiting to first five results to make it easier to fit the screen, but and holistic approach would be best.

    SELECT CAST(area_water AS FLOAT)/area_land AS water_land_ratio, *
      FROM facts
     ORDER BY water_land_ratio DESC
     LIMIT 5;
    water_land_ratio id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
    905.6666666666666 228 io British Indian Ocean Territory 54400 60 54340 None None None None None
    4.520231213872832 247 vq Virgin Islands 1910 346 1564 103574 0.59 10.31 8.54 7.67
    0.5547914317925592 246 rq Puerto Rico 13791 8870 4921 3598357 0.6 10.86 8.67 8.15
    0.3866133866133866 12 bf Bahamas, The 13880 10010 3870 324597 0.85 15.5 7.05 0.0
    0.2846728307254623 71 pu Guinea-Bissau 36125 28120 8005 1726170 1.91 33.38 14.33 0.0

    Notice that the gap between British Indian Ocean Territory and the Virgin Islands is much larger than the gap between the latter and Puerto Rico (even in relative comparison). What even is the British Indian Ocean Territory? Personally, I do not know. Should it be excluded from this analysis for some reason?

  • Regarding the query that tries to answer the question “Which countries will add the most people to their population next year?”, you’re not accounting for how large the population is. Countries whose birth to death rate is above average, but are small probably won’t be adding that many people.

    If, however, you interpreted the question as asking about relative population growth, then this could workout. I’d still prefer something more in line with the the query I wrote above. It’s fine, though.