What's OVER here?

Window functions allow you to create a RANK of information according to any variable you want to use to sort your data. When setting this up, you will need to specify what column/calculation you want to use to calculate your rank. This is done by including an ORDER BY clause inside the OVER() clause. Below is an example:

SELECT 
    id,
    RANK() OVER(ORDER BY home_goal) AS rank
FROM match;

In this exercise, you will create a data set of ranked matches according to which leagues, on average, score the most goals in a match.

Select the league name and average total goals scored from league and match.
Complete the window function so it calculates the rank of average goals scored across all leagues in the database.
Order the rank by the average total of home and away goals scored.

Exercise

What's OVER here?

Instructions