PARTITION BY multiple columns
The PARTITION BY clause can be used to break out window averages by multiple data points (columns). You can even calculate the information you want to use to partition your data! For example, you can calculate average goals scored by season and by country, or by the calendar year (taken from the date column).
In this exercise, you will calculate the average number home and away goals scored Legia Warszawa, and their opponents, partitioned by the month in each season.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Construct two window functions partitioning the average of home and away goals by season and month.
- Filter the dataset by Legia Warszawa's team ID (
8673) so that the window calculation only includes matches involving them.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
date,
season,
home_goal,
away_goal,
CASE WHEN hometeam_id = 8673 THEN 'home'
ELSE 'away' END AS warsaw_location,
-- Calculate average goals partitioned by season and month
___(home_goal) ___(___ ___ ___,
EXTRACT(___ FROM date)) AS season_mo_home,
___(away_goal) ___(___ ___ ___,
EXTRACT(___ FROM date)) AS season_mo_away
FROM match
WHERE
hometeam_id = ___
OR awayteam_id = ___
ORDER BY (home_goal + away_goal) DESC;