Get startedGet started for free

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

View Course

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;
Edit and Run Code