Global economies: Analysing measures of economic growth, prosperity, inflation and unemployment

-- Building a correlation matrix to examine relationships between GDP, gross_savings, total_investment, and net export of economies in the EU

DROP TABLE IF EXISTS correlations;  

CREATE TEMP TABLE gdp_components AS
  SELECT 
	e.code, 
    e.year,
    e.gdp_percapita,
	e.gross_savings,
	p.size, 
    e.total_investment, 
    e.exports,
    e.imports
  FROM world.economies AS e
  INNER JOIN world.populations AS p
  ON e.code = p.country_code AND e.year = p.year
  INNER JOIN world.eu_countries AS eu
  ON e.code = eu.code 
  WHERE e.year IN (2015);
  
CREATE TEMP TABLE gdp_final AS 
  SELECT
	code,
	year,
    ROUND(((gdp_percapita * size)/1000000000)::numeric, 2) AS gdp,
	gross_savings,
	total_investment, 
	exports - imports AS net_export 
  FROM gdp_components
  WHERE total_investment IS NOT NULL
    AND (exports - imports) IS NOT NULL
    AND (gdp_percapita * size/1000000000) IS NOT NULL;

CREATE TEMP TABLE correlations AS
SELECT 
  'gdp'::varchar AS measure,
  CORR(gdp, gdp) AS gdp, 
  CORR(gdp, gross_savings) AS gross_savings, 
  CORR(gdp, total_investment) AS total_investment, 
  CORR(gdp, net_export) AS net_export
FROM gdp_final;

INSERT INTO correlations 
SELECT 
  'gross_savings'::varchar AS measure,
  CORR(gross_savings, gdp) AS gdp,
  CORR(gross_savings, gross_savings) AS gross_savings,
  CORR(gross_savings, total_investment) AS total_investment,
  CORR(gross_savings, net_export) AS net_export
FROM gdp_final; 

INSERT INTO correlations
SELECT
  'total_investment'::varchar AS measure,
  CORR(total_investment, gdp) AS gdp,
  CORR(total_investment, gross_savings) AS gross_savings,
  CORR(total_investment, total_investment) AS total_investment,
  CORR(total_investment, net_export) AS net_export
FROM gdp_final;

INSERT INTO correlations
SELECT
  'net_export'::varchar AS measure,
  CORR(net_export, gdp) AS gdp,
  CORR(net_export, gross_savings) AS gross_savings,
  CORR(net_export, total_investment) AS total_investment,
  CORR(net_export,net_export) AS net_export
FROM gdp_final;

SELECT 
  measure,
  ROUND(gdp::numeric,2) AS gdp,
  ROUND(gross_savings::numeric,2) AS gross_savings,
  ROUND(total_investment::numeric,2) AS total_investment, 
  ROUND(net_export::numeric,2) AS net_export
FROM correlations;

-- Comparing average GDP per capita, average gross savings, average total investment, and average net export across different income groups across global countries

SELECT
  income_group, 
  ROUND(AVG(gdp_percapita::numeric),2) AS avg_gdp_percapita,
  ROUND(AVG(gross_savings::numeric),2) AS avg_gross_savings_million,
  ROUND(AVG(total_investment::numeric),2) AS avg_total_investment_million,
  ROUND(AVG((exports-imports)::numeric),2) AS avg_net_export_million 
FROM world.economies
GROUP BY income_group
ORDER BY AVG(gdp_percapita::numeric) DESC;

-- Comparing average GDP per capita, average gross savings, average total investment, and average net export across different income groups across EU countires 

SELECT
  e.income_group, 
  ROUND(AVG(e.gdp_percapita::numeric),2) AS avg_gdp_percapita,
  ROUND(AVG(e.gross_savings::numeric),2) AS avg_gross_savings_million,
  ROUND(AVG(e.total_investment::numeric),2) AS avg_total_investment_million,
  ROUND(AVG((e.exports-e.imports)::numeric),2) AS avg_net_export_million 
FROM world.economies AS e
INNER JOIN world.eu_countries AS eu
ON e.code = eu.code
GROUP BY e.income_group
ORDER BY AVG(e.gdp_percapita::numeric) DESC;

-- Analysing the relationship between inflation rate and unemployment rate between different income groups in 2015 
-- Conclusions will be drawn after benchmarking against the Phillips Curve Model (which suggests an inverse relationship between inflation rate and unemployment rate) 

DROP TABLE IF EXISTS correlations; 

CREATE TEMP TABLE high_inflation_unemployment AS 
SELECT
  code,
  income_group,
  inflation_rate,
  unemployment_rate
FROM world.economies
WHERE year = 2015
  AND inflation_rate IS NOT NULL
  AND unemployment_rate IS NOT NULL
  AND income_group IN ('High income'); 
  
CREATE TEMP TABLE upper_middle_inflation_unemployment AS 
SELECT
  code,
  income_group,
  inflation_rate,
  unemployment_rate
FROM world.economies
WHERE year = 2015
  AND inflation_rate IS NOT NULL
  AND unemployment_rate IS NOT NULL
  AND income_group LIKE ('Upper%'); 
  
CREATE TEMP TABLE lower_middle_inflation_unemployment AS 
SELECT
  code,
  income_group,
  inflation_rate,
  unemployment_rate
FROM world.economies
WHERE year = 2015
  AND inflation_rate IS NOT NULL
  AND unemployment_rate IS NOT NULL
  AND income_group LIKE ('Lower%'); 

CREATE TEMP TABLE correlations AS
SELECT 
  'High income'::varchar AS income_group, 
  CORR(inflation_rate, unemployment_rate) AS correlation
FROM high_inflation_unemployment; 

INSERT INTO correlations
SELECT
  'Upper middle income'::varchar AS income_group, 
  CORR(inflation_rate, unemployment_rate) AS correlation 
FROM upper_middle_inflation_unemployment;

INSERT INTO correlations 
SELECT 
  'Lower middle income'::varchar AS income_group,
  CORR(inflation_rate, unemployment_rate) AS correlation
FROM lower_middle_inflation_unemployment;

SELECT 
  income_group,
  ROUND(correlation::numeric, 2) AS correlation,
  (CASE WHEN correlation < 0 THEN 'Negative'
    WHEN correlation = 0 THEN 'No correlation'
    WHEN correlation > 0 THEN 'Positive' END) AS relationship,
  (CASE WHEN ABS(correlation::numeric)<=0.09 THEN 'Trivial'
    WHEN ABS(correlation::numeric)<=0.29 THEN 'Low to medium'
    WHEN ABS(correlation::numeric)<=0.49 THEN 'Medium to essential'
    WHEN ABS(correlation::numeric)<=0.69 THEN 'Essential to very strong'
    WHEN ABS(correlation::numeric)<=0.89 THEN 'Very strong'
    WHEN ABS(correlation::numeric)<=0.99 THEN 'Almost perfect'
    WHEN ABS(correlation::numeric)=1 THEN 'Perfect' END) AS strength,
  (CASE WHEN correlation < 0 THEN 'Movement along'
    WHEN correlation = 0 THEN 'Counteracts'
    WHEN correlation > 0 THEN 'Shift' END) AS phillips_curve_model,
  (CASE WHEN correlation < 0 THEN 'AD'
    WHEN correlation = 0 THEN 'Uncertain'
    WHEN correlation > 0 THEN 'SRAS' END)	AS growth_driven_by
FROM correlations;