Exercise

Testing out window functions

Window functions reference other rows within the report. There are a variety of window-specific functions to use, but all basic aggregation functions can be used as a window function. These include:

  • SUM()
  • AVG()
  • MAX()
  • MIN()

The syntax of a window function is FUNCTION(value) OVER (PARTITION BY field ORDER BY field). Note that the PARTITION BY and ORDER BY clauses are optional. The FUNCTION should be replaced with the function of your choice.

In this exercise, you will run a few different window functions on the country_stats table.

Instructions 1/4

undefined XP
  • 1

    Add the field country_avg_gdp that outputs the average gdp for each country.

  • 2

    Change country_avg_gdp to country_sum_gdp that shows the total gdp for each country across all years.

  • 3

    Change country_sum_gdp to country_max_gdp that shows the highest GDP for each country.

  • 4

    Change country_max_gdp to global_max_gdp that shows the highest GDP value for the entire world.