The match is OVER

The OVER() clause allows you to apply an aggregate function across a dataset, 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 which we will cover later in this chapter.

In this exercise, you will revise some queries from previous chapters using the OVER() clause.

Deze oefening maakt deel uit van de cursus

Data Manipulation in SQL

Cursus bekijken

Oefeninstructies

Select the match ID, country name, season, home_goal, and away_goal from the match and country tables.
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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

SELECT 
	-- Select the match 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;

Code bewerken en uitvoeren