Economic Growth and Public Transport in the EU.sqbpro

--- datasources: eurostat, world bank org
--- urls: https://ec.europa.eu/eurostat/en/ https://databank.worldbank.org/indicator/NY.GDP.MKTP.KD.ZG/1ff4a498/Popular-Indicators#



/*
DESCRIPTION: calculating rail density per 100km squared of land area as of 2021 in descending order
*/
CREATE TABLE RailDensity
AS
SELECT
	L.CountryName,
	Round(R.RailLines/(L.LandAreaKmSqr/100),1) AS 'Rail Density Per 100km Squared'
FROM
	LandArea L
INNER JOIN
	Rail R
ON
	L.CountryName = R.CountryName AND L.Year = R.Year
WHERE
	R.Year = 2021
	AND
	R.RailLines IS NOT NULL
ORDER BY
	"Rail Density Per 100km Squared" DESC


---



/*
DESCRIPTION: GDP growth rate by country
*/
CREATE TABLE GDPGrowth
AS
WITH
	GDP_CTE
AS (
		SELECT 
			CountryName,
			Year,
			GDP,
			CAST (LAG(GDP) OVER (PARTITION BY CountryName ORDER BY Year) AS REAL) AS PreviousYearGDP
		FROM
			T_GDP
)
SELECT
	CountryName,
	Year,
	CAST (round(((GDP_CTE.GDP - PreviousYearGDP)/PreviousYearGDP)*100,2) AS REAL) AS GDPGrowthRate
FROM
	GDP_CTE
WHERE
	PreviousYearGDP IS NOT NULL
ORDER BY
"Year" DESC




---



/*
DESCRIPTION: Aggregate GDP Growth Rate Across EU
*/
CREATE TABLE AnnualGDPGrowth
AS

WITH
	GDP_CTE
AS (
		SELECT 
			Year,
			sum(GDP) GDP,
			LAG(sum(GDP)) OVER (PARTITION BY CountryName ORDER BY Year) AS PreviousYearGDP
		FROM
			T_GDP
		GROUP BY
			Year
)
SELECT
	Year,
	round(avg(((GDP_CTE.GDP - PreviousYearGDP)/PreviousYearGDP)*100),2) AS GDPGrowthRate
FROM
	GDP_CTE
WHERE
	PreviousYearGDP IS NOT NULL
GROUP BY
	"Year"
ORDER BY
"YEAR" 


---



/*
DESCRIPTION: rail per 10,000 people for each country as of 2021 (excluding Estonia due to lack of rail data)
*/
CREATE TABLE RailPer10000
AS
SELECT
	Rail.CountryName,
	round(RailLines/(CAST (TotalPopulation AS REAL)/10000),2) AS "Rail Per 1000 People"
FROM
	Rail
JOIN
	Population
ON
 Population.CountryName = Rail.CountryName AND Population.Year = Rail.Year
WHERE
	Population.Year = 2021 AND RailLines IS NOT NULL
ORDER BY
"Rail Per 1000 People" DESC


---


/*
DESCRIPTION: change in rail lines from 1995 to 2021 by CountryCode (excluding estonia due to lack of data)
*/
CREATE TABLE RailGrowth
AS
WITH Rail_CTE AS
	(SELECT
		CountryName,
		Year,
		CAST (RailLines AS REAL),
		(CAST (RailLines AS REAL) - lag(CAST (RailLines AS REAL)) OVER (PARTITION BY CountryName ORDER BY Year)) AS "Change",
		(CAST (RailLines AS REAL) - lag(CAST (RailLines AS REAL)) OVER (PARTITION BY CountryName ORDER BY Year))/lag(CAST (RailLines AS REAL)) OVER (PARTITION BY CountryName ORDER BY Year)*100 AS PercentageChange
	FROM
	Rail
	WHERE
	Year = 2021 OR Year = 1995
	)
SELECT
	CountryName,
	Round(Change,3) AS "Change in km of rail from 1995 to 2021",
	Round(PercentageChange,3) AS "Percentage change in km of rail from 1995 to 2021"
FROM
	Rail_CTE
WHERE
	Change IS NOT NULL
ORDER BY
	PercentageChange DESC


---


/*
DESCRIPTION: annual GDP Per capita growth rate by country
*/

CREATE TABLE PerCapitaGDP AS
WITH
	GDP_CTE
AS (
		SELECT 
			G.CountryName,
			G.Year,
			GDP,
			TotalPopulation,
			GDP/TotalPopulation AS PerCapitaGDP,
			CAST (LAG(GDP/TotalPopulation) OVER (PARTITION BY G.CountryName ORDER BY G.Year) AS REAL) AS LagPerCap
		FROM
			T_GDP G
		JOIN
			Population P
		ON 
		G.CountryName = P.CountryName AND G.Year = P.Year
)
SELECT
	CountryName,
	Year,
	round("PerCapitaGDP",2) AS 'Real GDP Per Capita',
	CAST (round(((GDP_CTE.PerCapitaGDP - LagPerCap)/LagPerCap)*100,2) AS REAL) AS 'GDP Per Capita Growth'
FROM
	GDP_CTE
WHERE
	LagPerCap IS NOT NULL
ORDER BY
CountryName


---



WITH NewBuses_CTE AS
	(
	SELECT
		N.CountryName CountryName,
		N.Year Year,
		lag((N.BusQuantity/(P.TotalPopulation/1000))) OVER (PARTITION BY N.CountryName ORDER BY N.Year) AS BusLag,
		N.BusQuantity/(P.TotalPopulation/1000) AS BusPer1000
	FROM
		NewBuses N
	JOIN
		Population P
	ON
		N.CountryName = P.CountryName AND N.Year = p.Year
		)

SELECT
	CountryName,
	Year,
	round(BusPer1000, 2) AS "Buses Per Capita",
	round(((BusPer1000 - BusLag)/BusLag)*100,2) AS "Change in Buses per Capita"
FROM
	NewBuses_CTE



---


/*
DESCRIPTION: number of buses per 1000 people for each EU country, as of 2021
*/
CREATE TABLE Bus2021 AS
WITH NewBuses_CTE AS
	(
	SELECT
		N.CountryName CountryName,
		N.Year Year,
		lag((N.BusQuantity/(P.TotalPopulation/1000))) OVER (PARTITION BY N.CountryName ORDER BY N.Year) AS BusLag,
		N.BusQuantity/(P.TotalPopulation/1000) AS BusPer1000
	FROM
		NewBuses N
	JOIN
		Population P
	ON
		N.CountryName = P.CountryName AND N.Year = p.Year
		)

SELECT
	CountryName,
	Year,
	round(BusPer1000, 2) AS "Buses Per Capita"
FROM
	NewBuses_CTE
WHERE
	Year = 2021


---


/*
DESCRIPTION: Percentage change in the number of buses per 1000 people between 1996 and 2021 (exluding luxembourg due to lack of data)
*/
CREATE TABLE BusGrowth AS
WITH Bus_CTE AS
(
	SELECT
		N.CountryName Country,
		N.Year Year,
		CAST(BusQuantity AS REAL) AS '2021Bus',
		CAST(lag(BusQuantity) OVER (PARTITION BY N.CountryName ORDER BY N.Year) AS REAL) AS '1995Bus'
	FROM
		NewBuses N
	JOIN
		Population P
	ON
		N.CountryName = P.CountryName AND N.Year = p.Year
	WHERE
		N.Year = 1995 OR N.Year = 2021
)

SELECT
	Country,
	round((("2021Bus"-"1995Bus")/"1995Bus")*100,2) AS 'Change in Buses Per Capita'
FROM
	Bus_CTE
WHERE
	"Change in Buses Per Capita" IS NOT NULL

---


/* 
DESCRIPTION: Ranking countries by average growth in transportation provision from 1995 to 2021
*/
CREATE TABLE GrowthRank AS
SELECT
	B.Country,
	RANK() OVER (ORDER BY ("Change in Buses Per Capita"+"Percentage change in km of rail from 1995 to 2021")/2 DESC) Rank,
	("Change in Buses Per Capita"+"Percentage change in km of rail from 1995 to 2021")/2 AS 'Average Transport Growth'
FROM
	BusGrowth B
JOIN
	RailGrowth R
ON
	B.Country = R.CountryName