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
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 thematch_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;