CODEWITHSUNDEEP
sqlmysql
Riinu Anslan
Riinu Anslan

Reputation: 13

BASIC JOIN test in Hackerrank

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.

Here's my input:

SELECT country.continent, ROUND(AVG(city.population), 0) 
FROM city
JOIN country ON city.countrycode = country.code
GROUP BY country.continent;

OUTPUT

continent avg_population
Asia 693038
Europe 175138
Oceania 109190
South America 147435
Africa 274439

It says that it's a wrong answer. What am I doing wrong?

Upvotes: 0

Views: 339

Answers (1)

Ravi Yadav
Ravi Yadav

Reputation: 617

Since you used ROUND, it will will round down values below 0.5 only(as mentioned in the comment by @fuber).

There are two ways to solve it:

  1. Use ROUND but subtract 0.5 to round down all values even if its greater than 0.5:
SELECT CO.CONTINENT, ROUND(AVG(C.POPULATION)-0.5)
FROM CITY AS C
INNER JOIN COUNTRY AS CO
ON C.COUNTRYCODE = CO.CODE
GROUP BY CO.CONTINENT;
  1. Use FLOOR:
SELECT CO.CONTINENT, FLOOR(AVG(C.POPULATION))
FROM CITY AS C
INNER JOIN COUNTRY AS CO
ON C.COUNTRYCODE = CO.CODE
GROUP BY CO.CONTINENT;

Upvotes: 0

Related Questions