An Analysis of World Populations using SQL, Tableau and a Video Presentation
Introduction
Import Python Libraries
import pandas as pd
import matplotlib.pyplot as plt
Have a look at the Schema / Tables
SELECT *
FROM 'countries.csv'
SELECT *
FROM 'population_years.csv'
Answering Questions with SQL Analysis
1. How many entries in the countries table are from Africa?
SELECT COUNT(*)
FROM 'countries.csv'
WHERE continent = 'Africa'
2. What was the total population of the continent of Oceania in 2005?
SELECT continent, year, ROUND(SUM(population), 2) AS pop_in_mill
FROM 'population_years.csv' AS pop
JOIN 'countries.csv' AS con
ON pop.country_id = con.id
WHERE continent = 'Oceania' AND year = 2005
GROUP BY continent, year;
3. What is the average population of countries in South America in 2003?
SELECT continent, year, ROUND(AVG(population), 2) AS mil_pop
FROM 'population_years.csv' AS pop
INNER JOIN 'countries.csv' AS con
ON pop.country_id = con.id
WHERE continent = 'South America' AND year = 2003
GROUP BY continent, year
4. What country had the smallest population in 2007?
SELECT name, MIN(population) as min_pop
FROM 'countries.csv' AS con
JOIN 'population_years.csv'AS pop
ON con.id = pop.id
WHERE year = 2007
GROUP BY name
ORDER BY min_pop ASC
LIMIT 1;
5. What is the average population of Poland during the time period covered by this dataset?
SELECT MIN(year), MAX(year)
FROM 'population_years.csv'
SELECT name, ROUND(AVG(population),2) AS pop
FROM 'countries.csv' AS con
JOIN 'population_years.csv' AS pop
ON con.id = pop.country_id
WHERE YEAR BETWEEN 2000 AND 2010 and name = 'Poland'
GROUP BY name;
6. How many countries have the word “The” in their name?
SELECT name,
COUNT(name) AS count
FROM 'countries.csv'
WHERE name LIKE '%the%'
OR name LIKE '%The%'
GROUP BY name;
7. What was the total population of each continent in 2010?
SELECT continent,
ROUND(SUM(population),2) AS mil_pop,
ROUND((SUM(population) * 0.001),2) AS bil_pop
FROM 'countries.csv' AS con
JOIN 'population_years.csv'AS pop
ON con.id = pop.country_id
WHERE YEAR = 2010
GROUP BY continent
ORDER BY mil_pop DESC;
Visualizing Results with Tableau
%%HTML
<div style="position: relative; padding-bottom: 62.5%; height: 0;"><iframe src="https://www.loom.com/embed/b70127b7a893492084e84752be2627c3" frameborder="0" webkitallowfullscreen mozallowfullscreen allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
%%HTML
<div class='tableauPlaceholder' id='viz1630270953190' style='position: relative'><noscript><a href='#'><img alt=' ' src='https://public.tableau.com/static/images/Bi/BikeshareAnalysisDashboard/TimeAnalysisDashboard/1_rss.png' style='border: none' /></a></noscript><object class='tableauViz' style='display:none;'><param name='host_url' value='https%3A%2F%2Fpublic.tableau.com%2F' /> <param name='embed_code_version' value='3' /> <param name='site_root' value='' /><param name='name' value='BikeshareAnalysisDashboard/TimeAnalysisDashboard' /><param name='tabs' value='yes' /><param name='toolbar' value='yes' /><param name='static_image' value='https://public.tableau.com/static/images/Bi/BikeshareAnalysisDashboard/TimeAnalysisDashboard/1.png' /> <param name='animate_transition' value='yes' /><param name='display_static_image' value='yes' /><param name='display_spinner' value='yes' /><param name='display_overlay' value='yes' /><param name='display_count' value='yes' /><param name='language' value='en-US' /></object></div> <script type='text/javascript'> var divElement = document.getElementById('viz1630270953190'); var vizElement = divElement.getElementsByTagName('object')[0]; if ( divElement.offsetWidth > 800 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else if ( divElement.offsetWidth > 500 ) { vizElement.style.width='100%';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else { vizElement.style.width='100%';vizElement.style.minHeight='1250px';vizElement.style.maxHeight=(divElement.offsetWidth*1.77)+'px';} var scriptElement = document.createElement('script'); scriptElement.src = 'https://public.tableau.com/javascripts/api/viz_v1.js'; vizElement.parentNode.insertBefore(scriptElement, vizElement); </script>