ComeçarComece de graça

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.

Este exercício faz parte do curso

Improving Query Performance in PostgreSQL

Ver curso

Instruções do exercício

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

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

-- 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;
Editar e executar o código