1. Learn
    2. /
  2. Courses
    3. /
  3. Data Manipulation in SQL
  • 1

    We'll take the CASE

    Free

    In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.

  • 2

    Short and Simple Subqueries

    In this chapter, you will learn about subqueries in the SELECT, FROM, and WHERE clauses. You will gain an understanding of when subqueries are necessary to construct your dataset and where to best include them in your queries.

  • 3

    Correlated Queries, Nested Queries, and Common Table Expressions

    In this chapter, you will learn how to use nested and correlated subqueries to extract more complex data from a relational database. You will also learn about common table expressions and how to best construct queries using multiple common table expressions.

  • 4

    Window Functions

    You will learn about window functions and how to pass aggregate functions along a dataset. You will also learn how to calculate running totals and partitioned averages.

Initializing

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.