CO₂ & Greenhouse Gas Emissions Data Analysis

WITH sum_src_emission AS (
SELECT year AS [Year],
	SUM(coal_co2) AS coal_co2,
	SUM(cement_co2) AS cement_co2,
	SUM(flaring_co2) AS flaring_co2,
	SUM(gas_co2) AS gas_co2,
	SUM(oil_co2) AS oil_co2,
	SUM(other_industry_co2) AS other_industry_co2
FROM CO2EmissionsDB..CountryCO2EmissionPerSourceType
GROUP BY [Year]
),
unpivoted_src_emission AS (
	SELECT *
	FROM (
			SELECT *
			FROM sum_src_emission
		) AS a
		UNPIVOT (
			SourceCO2Emission FOR Sources IN (
				coal_co2,
				cement_co2,
				flaring_co2,
				gas_co2,
				oil_co2,
				other_industry_co2
			)
		) AS p
),
emission_group_by_yr_source  AS (
	SELECT Sources,
		MAX(SourceCO2Emission) AS MaxSrcCO2Emission
	FROM unpivoted_src_emission
	GROUP BY Sources
)
SELECT unpivotedTable.[Year],
	unpivotedTable.SourceCO2Emission AS [Source CO2 Emission],
	SUBSTRING(REPLACE(unpivotedTable.Sources, '_', ' '), 1, LEN(unpivotedTable.Sources) - 4) AS [Source]
FROM emission_group_by_yr_source maxEmission
	INNER JOIN unpivoted_src_emission unpivotedTable
	ON maxEmission.Sources = unpivotedTable.Sources AND
	maxEmission.MaxSrcCO2Emission = unpivotedTable.SourceCO2Emission

WITH max_co2_per_country AS (
	SELECT country AS [Country],
			year AS [Year],
			co2 AS CO2,
			MAX(co2) OVER (PARTITION BY country) AS [Max CO2 Emission]
	FROM CO2EmissionsDB..CountriesCO2EmissionsPerYear
	WHERE co2 IS NOT NULL
),
max_co2_per_country2 AS (
	SELECT [Country], [Year], [Max CO2 Emission]
	FROM max_co2_per_country
	WHERE [MAX CO2 Emission] = co2
),
max_co2_shares AS (
	SELECT perCountry.[Country],
			perCountry.[Year],
			perCountry.[Max CO2 Emission],
			perSrc.coal_co2 / perCountry.[Max CO2 Emission] AS [Coal Share],
			perSrc.cement_co2 / perCountry.[Max CO2 Emission] AS [Cement Share],
			perSrc.flaring_co2 / perCountry.[Max CO2 Emission] AS [Flaring Share],
			perSrc.gas_co2 / perCountry.[Max CO2 Emission] AS [Gas Share],
			perSrc.oil_co2 / perCountry.[Max CO2 Emission] AS [Oil Share],
			perSrc.other_industry_co2 / perCountry.[Max CO2 Emission] AS [Other Industry Share]
	FROM max_co2_per_country2 perCountry
			INNER JOIN CO2EmissionsDB..CountryCO2EmissionPerSourceType perSrc
			ON perCountry.[Country] = perSrc.country AND 
			perCountry.[Year] = perSrc.year
),
cross_apply_src_shares AS (
	SELECT ms.[Country], ms.[Year], ms.[Max CO2 Emission],
			t.[Source Type], t.[CO2 Emission Share]
	FROM max_co2_shares ms
			CROSS APPLY (
			VALUES
				('coal', ms.[Coal Share]),
				('cement', ms.[Cement Share]),
				('flaring', ms.[Flaring Share]),
				('Gas', ms.[Gas Share]),
				('oil', ms.[Oil Share]),
				('Other Industry', ms.[Other Industry Share])
			) t ([Source Type], [CO2 Emission Share])
)
SELECT c1.[Country], c1.[Year], c1.[Source Type], 
	   ROUND(c1.[Max CO2 Emission], 3) AS [Max CO2 Emission],
	   ROUND(c1.[CO2 Emission Share], 3) AS [Source CO2 Emission Share]
FROM cross_apply_src_shares c1
WHERE c1.[CO2 Emission Share] = (
	SELECT MAX(c2.[CO2 Emission Share])
	FROM cross_apply_src_shares c2
	WHERE c1.[Country] = c2.[Country]
)