Skip to main content

Exercise 3

Create Table for Aggregate Global Temperatures​

To analyze temperature measurements globally on an annual basis, you will create a table called aggregate_global_temperatures in the global_temperatures schema based on the stg_global_temperatures table in the global_temperatures schema . This view will group the data by year and provide aggregated temperature values using the appropriate aggregation functions.

To create the aggregate_global_temperatures view based on the stg_global_temperatures table in the global_temperatures schema, and extract the desired columns while applying the appropriate aggregation functions, follow these steps:

  1. Select the Date, LandAverageTemperature, LandMaxTemperature, LandMinTemperature, and LandAndOceanAverageTemperature columns from the stg_global_temperatures table.
  2. Extract the year from the Date column to obtain only the year portion of the date.
  3. Apply the AVG aggregation function to calculate the average values for LANDANDOCEANAVERAGETEMPERATURE and LANDAVERAGETEMPERATURE.
  4. Apply the MAX aggregation function to calculate the maximum value for LANDMAXTEMPERATURE.
  5. Apply the MIN aggregation function to calculate the minimum value for LANDMINTEMPERATURE.
  6. Group the data by the YEAR column.

This view will group the temperature data by year and provide the aggregated values for LANDAVERAGETEMPERATURE, LANDMAXTEMPERATURE, LANDMINTEMPERATURE, and LANDANDOCEANAVERAGETEMPERATURE.

  • YEAR: Integer
  • LANDAVERAGETEMPERATURE: Float
  • LANDMAXTEMPERATURE: Float
  • LANDMINTEMPERATURE: Float
  • LANDANDOCEANAVERAGETEMPERATURE: Float

Sample Output

YEARLANDAVERAGETEMPERATURELANDMAXTEMPERATURELANDMINTEMPERATURELANDANDOCEANAVERAGETEMPERATURE
18698.4320833339.618-0.50915.0655
18668.2921666679.636-0.88115.057
note

Note date column as the value like 2022-01-19 try to extract 2022 alone

Both Year and Date are Reserved Keyword

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