PARTITION BY a column
The PARTITION BY clause allows you to calculate separate "windows" based on columns you want to divide your results. For example, you can create a single column that calculates an overall average of goals scored for each season.
In this exercise, you will be creating a data set of games played by Legia Warszawa (Warsaw League), the top ranked team in Poland, and comparing their individual game performance to the overall average for that season.
Where do you see more outliers? Are they Legia Warszawa's home or away games?
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Complete the two window functions that calculate the home and away goal averages. Partition the window functions by season to calculate separate averages for each season.
- Filter the query to only include matches played by Legia Warszawa,
id = 8673.
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 the average goals scored partitioned by season
___(___) ___(___ ___ ___) AS season_homeavg,
___(___) ___(___ ___ ___) AS season_awayavg
FROM match
-- Filter the data set for Legia Warszawa matches only
WHERE
___ = ___
OR ___ = ___
ORDER BY (home_goal + away_goal) DESC;