Countries with subqueries or CTEs
Of the Olympic regions, Africa has the warmest winters. However, some African countries do participate in the Winter Olympics. For instance, Morocco has sent more than 20 athletes over the years. Perhaps looking at the data at the regional level is too broad. Maybe some African countries have colder winters than other African countries.
Take another look at winter climate data, but look on a country level. The athletes have been filtered down to include only African athletes, with the first African Olympians competing in 1960.
Use both a subquery and CTE structure and compare the results.
This exercise is part of the course
Improving Query Performance in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Climate by country with Olympian athletes
SELECT country
, temp_06
, precip_06
FROM climate
WHERE region = 'Africa'
AND olympic_cc IN (___ ___ ___ FROM ___)
ORDER BY temp_06;