Where winter is white
Canada, Russia, and Mongolia are the only countries with Olympians and average annual temperatures below freezing. More commonly, countries have cold weather occurring only during winter months. Countries with only a few months of freezing temperatures and snow still provide athletes the opportunity to train for events like skiing and bobsledding.
With this in mind, you will look at climate data for countries with Olympic athletes using the 40-year average monthly temperatures. You are really intrigued by the low Winter Olympics participation in Africa and decide to look at the temperature for all the Olympic regions in the southern hemisphere.
Write the query to optimize for readability by using a common table expression (CTE).
This exercise is part of the course
Improving Query Performance in PostgreSQL
Exercise instructions
- Write a CTE,
south_ctefor the southern hemisphere. - Find the average June temperature and precipitation from the
oclimatetable. - Join the results to view the average winter month temperature for all regions.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
___ ___ ___ -- CTE
(
SELECT region
, ROUND(AVG(temp_06),2) AS avg_winter_temp
, ROUND(AVG(precip_06),2) AS avg_winter_precip
FROM ___
WHERE region IN ('Africa','South America','Australia and Oceania')
GROUP BY region
)
SELECT south.region, south.avg_winter_temp, south.avg_winter_precip
, COUNT(DISTINCT ath.athlete_id)
FROM ___ as south
INNER JOIN athletes_recent ath
ON south.region = ath.region
AND ath.season = 'Winter'
GROUP BY south.region, south.avg_winter_temp, south.avg_winter_precip
ORDER BY south.avg_winter_temp;