World Population Data
(Download the CSV file used)

/* To see the file */  

SELECT * from PopulationData LIMIT 5;  

rank	cca3	country	continent	2023 population	2022 population	2020 population	2015 population	2010 population	2000 population	1990 population	1980 population	1970 population	area (km²)	density (km²)	growth rate	world percentage
1	IND	India	Asia	1428627663	1417173173	1396387127	1322866505	1240613620	1059633675	870452165	696828385	557501301	3287590	481	0.81%	17.85%
2	CHN	China	Asia	1425671352	1425887337	1424929781	1393715448	1348191368	1264099069	1153704252	982372466	822534450	9706961	151	-0.02%	17.81%
3	USA	United States	North America	339996563	338289857	335942003	324607776	311182845	282398554	248083732	223140018	200328340	9372610	37	0.50%	4.25%
4	IDN	Indonesia	Asia	277534122	275501339	271857970	259091970	244016173	214072421	182159874	148177096	115228394	1904569	148	0.74%	3.47%
5	PAK	Pakistan	Asia	240485658	235824862	227196741	210969298	194454498	154369924	115414069	80624057	59290872	881912	312	1.98%	3.00%

/*Total population of each continent */  

SELECT continent, sum("2023 population") AS TotalPopulation FROM PopulationData GROUP BY continent ORDER BY 2 DESC;  

/*The top 10 densely populated countries*/  

SELECT country, "density (km²)" from PopulationData ORDER BY "density (km²)" DESC LIMIT 10;  

/*Total population of each continent this year, last year, and if it's a positive or negative change */  

SELECT continent, sum("2023 population") AS Pop23, sum("2022 population") AS Pop22,  
    CASE  
        WHEN (sum("2023 population")-sum("2022 population")) > 0 THEN 'Increase'  
        ELSE 'Decrease'  
    END AS "Change"    
FROM PopulationData GROUP BY continent ORDER BY 2 DESC;  

/*Calculating the average population per country within each continent*/  

WITH GroupedContinent AS (SELECT continent, COUNT(*) AS NoOfCountries, Sum("2023 population") AS TotalPop FROM PopulationData GROUP BY Continent)  


SELECT continent, NoOfCountries,TotalPop, TotalPop/NoOfCountries AS AveragePopulationPerCountry FROM GroupedContinent;  

/*Calculating the number of countries in each population bin - eg, 0-100m , 100m-200m, etc.*/  

SELECT   
    SUM(CASE WHEN "2023 population" >= 0 AND "2023 population" <= 100000000 THEN 1 ELSE 0 END) AS "0-100 million",  
    SUM(CASE WHEN "2023 population" > 100000000 AND "2023 population" <= 200000000 THEN 1 ELSE 0 END) AS "100-200 million",  
    SUM(CASE WHEN "2023 population" > 200000000 AND "2023 population" <= 300000000 THEN 1 ELSE 0 END) AS "200-300 million",  
    -- Add more case statements here  
FROM PopulationData;  

/*Calculate the countries where population is declining the most*/  

SELECT   
    Country,  
    "2010 population",  
    "2020 population",  
    "2010 population" - "2020 population" AS Population_Decrease  
FROM PopulationData  
ORDER BY Population_Decrease desc  
LIMIT 10;