Joining in the query plan
China and India each have over 1 billion people and together contain over 30% of the world's people. You want to see if they are the countries with the biggest population growth.
Since they are both in Asia, you gather population data from the World Bank for every Asian country between 1990 to 2017. To simplify your analysis, you decide to compare two years of population data. You compare the recent (2017) population to the 1990 population to find a population growth metric.
Do India and China top the list? Are they the Asian countries with the most growth since the 1990s?
After writing your query, check the query plan to see how the join impacts the plan.
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.
SELECT old.country
, old.region
, old.population_1990
, new.population_2017
, 100*((new.population_2017 - old.population_1990)/new.population_2017::float) as population_growth
FROM pop_1990 old
INNER JOIN pop_2017 new
USING(___)
ORDER BY population_growth DESC