Subqueries vs. CTEs
You have learned different ways of writing queries and how these choices impact performance. This exercise will again look at country demographics for the African countries with athletes competing in the Olympics. You will focus on the Summer Olympics.
Using pre-written queries, you will restructure the queries and assess the impact of the changes. You will start by seeing the difference in query plans when using subqueries compared to common table expressions (CTEs).
Run the execution plan for each step.
Diese Übung ist Teil des Kurses
Improving Query Performance in PostgreSQL
Interaktive Übung
Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.
-- Subquery
___ ___
SELECT city
, sex
, COUNT(DISTINCT athlete_id) as no_athletes
, AVG(age) as avg_age
FROM athletes_summ
WHERE country_code IN (SELECT olympic_cc FROM demographics WHERE gdp > 10000 and year = 2016)
AND year = 2016
GROUP BY city, sex;