Nest a subquery in FROM
What's the average number of matches per season where a team scored 5 or more goals? How does this differ by country?
Let's use a nested, correlated subquery to perform this operation. In the real world, you will probably find that nesting multiple subqueries is a task you don't have to perform often. In some cases, however, you may find yourself struggling to properly group by the column you want, or to calculate information requiring multiple mathematical transformations (i.e., an AVG of a COUNT).
Nesting subqueries and performing your transformations one step at a time, adding it to a subquery, and then performing the next set of transformations is often the easiest way to yield accurate information about your data. Let's get to it!
This exercise is part of the course
Data Manipulation in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Select matches where a team scored 5+ goals
SELECT
country_id,
season,
id
FROM match
WHERE home_goal ___ OR away_goal ___;