Aan de slagGa gratis aan de slag

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.

Deze oefening maakt deel uit van de cursus

Improving Query Performance in PostgreSQL

Cursus bekijken

Oefeninstructies

  • 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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- 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;
Code bewerken en uitvoeren