Canadians temp table
You are interested in the Winter Olympics and want to look at athletes from countries with a winter climate (cold temperatures and snowfall). You know Canada has an average annual temperature below freezing, so you decide to look at Canadian athletes. You want to know all the winter sports that Canadians participate in and which sport has the most Canadian competitors.
The preloaded base table of Olympic athletes, athletes_recent
, is quite large. Even though it only includes athletes from two Olympic games, it has thousands of rows and could be slow to query.
Since you want to look at Canadian athletes only and then perform some exploratory analysis, you will first create a temporary table of Canadian athletes. Use this table to find the sport with the most athletes.
This exercise is part of the course
Improving Query Performance in PostgreSQL
Exercise instructions
- Create a temp table of the Canadian athletes from the
athletes_recent
table. - Choose only athletes in the Winter Olympics.
- Use the
canadians
temp table to find the sport with the most Canadian competitors.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a temp table of Canadians
___ ___ ___ canadians ___
SELECT *
FROM athletes_recent
WHERE country_code = 'CAN'
AND ___ = 'Winter'; -- The table has both summer and winter athletes
-- Find the most popular sport
SELECT sport
, COUNT(DISTINCT athlete_id) as no_athletes
FROM ___
GROUP BY sport
ORDER BY no_athletes DESC;