Flip OVER your results

In the last exercise, the rank generated in your query was organized from smallest to largest. By adding DESC to your window function, you can create a rank sorted from largest to smallest.

SELECT 
    id,
    RANK() OVER(ORDER BY home_goal DESC) AS rank
FROM match;

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Select the league name and average total goals scored from league and match.
Complete the window function to rank each league from highest to lowest average goals scored.
ORDER the main query BY the rank you just created.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
	-- Select the league name and average goals scored
	___ AS league,
    ___(___ + m.away_goal) AS avg_goals,
    -- Rank leagues in descending order by average goals
    ___ ___(___ ___ ___(___ + m.away_goal) ___) AS league_rank
FROM league AS l
LEFT JOIN match AS m 
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
-- Order the query by the rank you created
___ ___ ___;

Edit and Run Code