Clean up with CTEs

In chapter 2, you generated a list of countries and the number of matches in each country with more than 10 total goals. The query in that exercise utilized a subquery in the FROM statement in order to filter the matches before counting them in the main query. Below is the query you created:

SELECT
  c.name AS country,
  COUNT(sub.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS sub
ON c.id = sub.country_id
GROUP BY country;

You can list one (or more) subqueries as common table expressions (CTEs) by declaring them ahead of your main query, which is an excellent tool for organizing information and placing it in a logical order.

In this exercise, let's rewrite a similar query using a CTE.

Questo esercizio fa parte del corso

Data Manipulation in SQL

Visualizza il corso

Istruzioni dell'esercizio

Complete the syntax to declare your CTE.
Select the country_id and match id from the match table in your CTE.
LEFT JOIN the CTE to the league table using country_id.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

-- Set up your CTE
___ match_list ___ (
    SELECT 
  		country_id, 
  		___
    FROM match
    WHERE (home_goal + away_goal) >= 10)
-- Select league name and count of matches from the CTE
SELECT
    l.name AS league,
    COUNT(match_list.id) AS matches
FROM league AS l
-- Join the CTE to the league table using country_id
LEFT JOIN ___ ON l.id = ___
GROUP BY l.name;

Modifica ed esegui il codice