Average Population of Each Continent HackerRank SQL Solution

Problem:-

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

Input Format

The CITY and COUNTRY tables are described as follows:

Solution:-

select c.continent,round(avg(ci.population)-0.5) as 'avg_population'
from city ci
inner join country c
ON ci.countrycode = c.code

group by c.continent;