Exercise

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.

Instructions

100 XP
  • 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.