CTEs with nested subqueries

If you find yourself listing multiple subqueries in the FROM clause with nested statement, your query will likely become long, complex, and difficult to read.

Since many queries are written with the intention of being saved and re-run in the future, proper organization is key to a seamless workflow. Arranging subqueries as CTEs will save you time, space, and confusion in the long run!

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Declare a CTE that calculates the total goals from matches in August of the 2013/2014 season.
Left join the CTE onto the league table using country_id from the match_list CTE.
Filter the list on the inner subquery to only select matches in August of the 2013/2014 season.

Hands-on interactive exercise

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

-- Set up your CTE
___ match_list ___ (
    SELECT 
  		country_id,
  	   (home_goal + away_goal) AS goals
    FROM match
  	-- Create a list of match IDs to filter data in the CTE
    WHERE id IN (
       SELECT ___
       FROM ___
       WHERE season = ___ AND EXTRACT(MONTH FROM ___) = ___))
-- Select the league name and average of goals in the CTE
SELECT 
	___,
    ___(___)
FROM league AS l
-- Join the CTE onto the league table
LEFT JOIN ___ ON l.id = match_list.___
GROUP BY l.name;

Edit and Run Code