Exercise: 8
Create view for Europe Big Three Emissions
Use the source table co2_emissions_and_temperatures_by_country to create a view for the emissions data of the three major European countries: France, Germany, and the United Kingdom.
1.To create the view europe_big_three_emissions with the desired columns in the carbon_emissions schema
2.Determine the join conditions based on the columns that link the tables. In your case, the join conditions are:
- global.Year = france.Year AND france.Country = 'France'
- global.Year = germany.Year AND germany.Country = 'Germany'
- global.Year = uk.Year AND uk.Country = 'United Kingdom' Adjust the join conditions based on your specific column names and criteria.
Reshape the data to meet the following requirements:
Select the following columns for the output view:
- YEAR: integer
- FRANCE_TOTALEMISSIONS: float
- FRANCE_PERCAPITAEMISSIONS: float
- GERMANY_TOTALEMISSIONS: float
- GERMANY_PERCAPITAEMISSIONS: float
- UNITEDKINGDOM_TOTALEMISSIONS: float
- UNITEDKINGDOM_PERCAPITAEMISSIONS: float
Sample Output
| YEAR | FRANCE_TOTALEMISSIONS | FRANCE_PERCAPITAEMISSIONS | GERMANY_TOTALEMISSIONS | GERMANY_PERCAPITAEMISSIONS | UK_TOTALEMISSIONS | UK_PERCAPITAEMISSIONS |
|---|---|---|---|---|---|---|
| 1845 | 17.836352 | 0.500937 | 12.611488 | 0.389193 | 99.986896 | 4.618767 |
| 1846 | 18.528848 | 0.518265 | 13.186736 | 0.403786 | 95.692688 | 4.381496 |
note
In the SQL statement, the use of double quotes around the YEAR column name is necessary because it is a reserved keyword in Snowflake.
select "YEAR" FROM TABLE