Get startedGet started for free

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

View Course

Exercise instructions

  • From cities, select the city name, country code, proper population, and metro area population, as well as the field city_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 from countries in 'Europe' or continents with 'America' at the end of their name.
  • Exclude NULL values in metroarea_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
___
Edit and Run Code