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.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Complete the syntax to declare your CTE.
- Select the
country_id
and matchid
from thematch
table in your CTE. - Left join the CTE to the league table using
country_id
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Set up your CTE
___ match_list ___ (
SELECT
country_id,
___
FROM match
WHERE (home_goal + away_goal) >= 10)
-- Select league 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
LEFT JOIN ___ ON l.id = ___
GROUP BY l.name;