Session Ready
Exercise

Add a window function

Fantastic! You now have a result set that retrieves the match date, home team, away team, and the goals scored by each team. You have one final component of the question left -- how badly did Manchester United lose in each match?

In order to determine this, let's add a window function to the main query that ranks matches by the absolute value of the difference between home_goal and away_goal. This allows us to directly compare the difference in scores without having to consider whether Manchester United played as the home or away team!

The equation is complete for you -- all you need to do is properly complete the window function!

Instructions
100 XP
  • Set up the CTEs so that the home and away teams each have a name, ID, and score associated with them.
  • Select the date, home team name, away team name, home goal, and away goals scored in the main query.
  • Rank the matches and order by the difference in scores in descending order.