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!

Questo esercizio fa parte del corso

Data Manipulation in SQL

Visualizza il corso

Istruzioni dell'esercizio

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

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

-- 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 using country_id
LEFT JOIN ___ ON l.id = match_list.___
GROUP BY l.name;

Modifica ed esegui il codice