Final challenge
You've made it to the final challenge problem! Get ready to tackle this step-by-step.
Your task is to determine the top 10 capital cities in Europe and the Americas by city_perc
, a metric you'll calculate. city_perc
is a percentage that calculates the "proper" population in a city as a percentage of the total population in the wider metro area, as follows:
city_proper_pop / metroarea_pop * 100
Do not use table aliasing in this exercise.
This exercise is part of the course
Joining Data in SQL
Exercise instructions
- From
cities
, select the city name, country code, proper population, and metro area population, as well as the fieldcity_perc
, which calculates the proper population as a percentage of metro area population for each city (using the formula provided). - Filter city name with a subquery that selects
capital
cities fromcountries
in'Europe'
or continents with'America'
at the end of their name. - Exclude
NULL
values inmetroarea_pop
. - Order by
city_perc
(descending) and return only the first 10 rows.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Select fields from cities
___
-- Use subquery to filter city name
___
-- Add filter condition such that metroarea_pop does not have null values
___
-- Sort and limit the result
___