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.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Select the league name and average total goals scored from
leagueandmatch. - 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.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select the league name and average goals scored
___ AS league,
___(___ + ___.away_goal) AS avg_goals,
-- Rank each league according to the average goals
___ ___(___ ___ AVG(___.home_goal + ___)) AS league_rank
FROM league AS l
LEFT JOIN match AS m
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
-- Order the query by the rank you created
ORDER BY ___;