Freedom Index Analysis

-- Explore the data in the table
SELECT *
FROM 'hfi_cc_2022.csv';
-- There are some missing values, and the level of detail in the data shows the scores obtained in each sub-area. 
-- To have a cleaner data set, these sub-scores won't be used. The first step is to create a new table with the wanted fields only.

-- Create a temporary table 
CREATE TEMP TABLE hfi_table AS
SELECT *
FROM 'hfi_cc_2022.csv';
-- Use information_schema.columns to see the data type of each field
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'hfi_table';

-- Create a new cleaned table without the sub-scores and rounding to 2 decimals each value
	SELECT year,
		countries,
		region,
		ROUND(hf_score, 2) AS hf_score,
		hf_rank,
		hf_quartile,
		ROUND(pf_rol, 2) AS rule_of_law,
		ROUND(pf_ss, 2) AS security_and_safety,
		ROUND(pf_movement, 2) AS movement,
		ROUND(pf_religion, 2) AS religion,
		ROUND(pf_assembly, 2) AS association,
		ROUND(pf_expression, 2) AS expression_and_information,
		ROUND(pf_identity, 2) AS relationships,
		ROUND(pf_score, 2) AS pf_score,
		pf_rank AS personal_rank,
		ROUND(ef_government, 2) AS size_of_government,
		ROUND(ef_legal, 2) AS legal_system_and_pr,
		ROUND(ef_money, 2) AS sound_money,
		ROUND(ef_trade, 2) AS freedom_to_trade,
		ROUND(ef_regulation, 2) AS regulation,
		ROUND(ef_score, 2) AS ef_score,
		ef_rank
FROM 'hfi_cc_2022.csv';

-- Select the top 10 countries with the highest levels of freedom in 2020
SELECT
	hf_rank,
	countries,
	hf_score,
	pf_score,
	ef_score
FROM hfi_cleaned
WHERE year = 2020
ORDER BY hf_rank 
LIMIT 10;

-- Explore the different regions in the data base
SELECT
	DISTINCT(region)
FROM hfi_cleaned;

-- First I create a CTE to get the average HFI score for every region, by year.
-- I use the ROW_NUMBER function to generate a rank by average HFI score
WITH avg_hfscore_region AS (
SELECT
	year,
	region,
	ROUND(AVG(hf_score), 2) AS avg_hf_score,
	ROW_NUMBER () OVER (PARTITION BY year ORDER BY avg_hf_score DESC) AS rank
FROM hfi_cleaned
GROUP BY year, region
ORDER BY year DESC)
-- Query the created CTE and, filter by rank and order it by year 
SELECT
	year,
	region,
	avg_hf_score
FROM avg_hfscore_region 
WHERE rank = 1
GROUP BY year, region, 	avg_hf_score
ORDER BY year DESC;
/*We can see that, in average, North America was the freest region in the last 20 years, 
but Western Europe surpased it in 2020.*/

/*Create a query that returns the count of countries 
in the first and fourth quartile for the year 2020*/
SELECT
	hf_quartile,
	COUNT(countries) AS count_countries
FROM hfi_cleaned
WHERE year = 2020 AND hf_quartile IN (1, 4)
GROUP BY hf_quartile;
/*It appears that there is just 2 more countries 
in the lowest quartile compared to the highest one*/