Filtering to freezing with a subquery
From the first Olympics in 1904 through the 2016 games, African countries have sent 7,845 athletes. However, only 55 of those athletes competed in the Winter Olympics.
One-quarter of Africa is covered by the Sahara, and the non-desert areas have year-round heat. Perhaps this lack of cold weather and snow limits the training opportunities for Winter Olympians.
Here, you will explore climate data to see if all African countries lack winter sports conditions. The World Bank collects average temperatures and precipitation for each country. Monthly and annual 40-year averages are preloaded with temperature in degrees Celsius (0 is freezing) and precipitation in millimeters.
Examine the climate data, looking for countries below freezing all year. Are there any in Africa?
This exercise is part of the course
Improving Query Performance in PostgreSQL
Exercise instructions
- Limit to athletes participating only in the Winter Olympics, using the
seasoncolumn. - Use a subquery to select only those countries from the
oclimatetable with an average annual temperature (temp_annual) below freezing (0 degrees).- Select the country codes,
olympic_ccfrom theoclimatetable to filter thecountry_codefrom theathletes_recenttable.
- Select the country codes,
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Countries cold enough for snow year-round
SELECT country_code
, country
, COUNT (DISTINCT athlete_id) AS winter_athletes -- Athletes can compete in multiple events
FROM athletes
WHERE country_code IN (___ ___ ___ ___ WHERE temp_annual < 0)
AND ___ = 'Winter'
GROUP BY country_code, country;