Setting up the home team CTE

In this course, we've covered ways in which you can use CASE statements, subqueries, common table expressions, and window functions in your queries to structure a data set that best meets your needs. For this exercise, you will be using all of these concepts to generate a list of matches in which Manchester United was defeated during the 2014/2015 English Premier League season.

Your first task is to create the first query that filters for matches where Manchester United played as the home team. This will become a common table expression in a later exercise.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Create a CASE statement that identifies each match as a win, lose, or tie for Manchester United.
Fill out the logical operators for each WHEN clause in the CASE statement (equals, greater than, less than).
Join the tables on home team ID from match, and team_api_id from team.
Filter the query to only include games from the 2014/2015 season where Manchester United was the home team.

Hands-on interactive exercise

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

SELECT 
	m.id, 
    t.team_long_name,
    -- Identify matches as home/away wins or ties
	___ ___ m.home_goal ___ ___ ___ 'MU Win'
		___ m.home_goal ___ ___ ___ 'MU Loss'
        ___ 'Tie' ___ AS outcome
FROM ___ AS m
-- Left join team on the home team ID and team API id
LEFT JOIN ___ AS t 
ON ___ = t.team_api_id
WHERE 
	-- Filter for 2014/2015 and Manchester United as the home team
	___ = ___
	AND t.team_long_name = ___;

Edit and Run Code