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.
This exercise is part of the course
Reporting in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
country_id,
year,
gdp,
-- Show the average gdp across all years per country
____ AS country_avg_gdp
FROM country_stats;