Own SQL Queries: World Data

SELECT * FROM world.countries LIMIT 3;

SELECT * FROM world.populations LIMIT 3;

SELECT * FROM world.economies LIMIT 3;

SELECT * FROM world.eu_countries LIMIT 3;

SELECT * FROM world.languages LIMIT 3;

SELECT * FROM world.economies2010 LIMIT 3;

SELECT * FROM world.economies2015 LIMIT 3;

SELECT * FROM world.economies2019 LIMIT 3;

WITH cte AS(
	SELECT name, p.year AS year, unemployment_rate, life_expectancy
	FROM world.countries AS c
	JOIN world.economies AS e USING (code)
	JOIN world.populations AS p ON c.code = p.country_code
		AND e.year = p.year
	WHERE unemployment_rate IS NOT NULL
	),
	cte2 AS(
		SELECT name
		FROM cte
		GROUP BY name
		HAVING COUNT(*) = 2
	),
	cte3 AS(
		SELECT *,
			LAG(unemployment_rate) OVER(PARTITION BY name ORDER BY year) AS lag_u_rate,
			LAG(life_expectancy) OVER(PARTITION BY name ORDER BY year) AS lag_l_exp
		FROM cte
		WHERE name IN (SELECT * FROM cte2)
	)

SELECT
	name,
	ROUND(-(unemployment_rate - lag_u_rate)::NUMERIC, 2) AS unemployment_rate_decrease,
	ROUND((life_expectancy - lag_l_exp)::NUMERIC, 2) AS life_expectancy_increase
FROM (SELECT * FROM cte3 WHERE year = 2015) AS final
WHERE unemployment_rate - lag_u_rate < 0
	AND life_expectancy - lag_l_exp > 0;

WITH cte AS (
	SELECT
		*,
		CASE WHEN name in (SELECT name FROM world.eu_countries) THEN 'Yes'
			ELSE 'No' END AS eu_country
	FROM world.countries AS c
	JOIN world.economies USING(code)
	),
	cte2 AS (
		SELECT eu_country, income_group, COUNT(*) AS cnt
		FROM cte
		GROUP BY eu_country, income_group
	)
	
SELECT
	eu_country AS eu_countries,
	income_group, CONCAT(ROUND((cnt/ovr_cnt)::NUMERIC*100, 1), '%') as proportion
FROM cte2
LEFT JOIN (
	SELECT eu_country, SUM(cnt) AS ovr_cnt
	FROM cte2
	GROUP BY eu_country
) AS overall USING(eu_country)
ORDER BY eu_country DESC, cnt/ovr_cnt DESC;