The match is OVER
The OVER() clause allows you to pass an aggregate function down a data set, similar to subqueries in SELECT. The OVER() clause offers significant benefits over subqueries in select -- namely, your queries will run faster, and the OVER() clause has a wide range of additional functions and clauses you can include with it that we will cover later on in this chapter.
In this exercise, you will revise some queries from previous chapters using the OVER() clause.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Select the match ID, country name, season, home, and away goals from the
matchandcountrytables. - Complete the query that calculates the average number of goals scored overall and then includes the aggregate value in each row using a window function.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select the id, country name, season, home, and away goals
___,
c.___ AS country,
m.season,
___.home_goal,
___,
-- Use a window to include the aggregate average in each row
___(___.home_goal + ___) ___ AS overall_avg
FROM match AS m
LEFT JOIN country AS c ON m.country_id = c.id;