Hristo Buyukliev Hristo Buyukliev - 4 months ago 7
SQL Question

Select countries with population close to mean

I'm doing khanacademy's SQL course, and I'm playing with this dataset. It includes the columns rank (Primary Key), country, population, etc. I want to select countries which are close the global population mean - e.g. if the global population mean is 30m, I want to select countries with 20-40m population.

Now, I tried several things, but none worked. This question is similar, and I tried to modify the answer like this:

SELECT a.country, a.population, b.max_pop
FROM countries_by_population a
INNER JOIN (
SELECT rank, MAX(population) as max_pop
FROM countries_by_population
GROUP BY rank
) b ON a.rank = b.rank
WHERE a.population > b.max_pop*0.9;


However, that returns all rows, and max_pop is just the population for this country. Not working aside, I feel there should be a simpler solution than joining the table with itself. What is it?

Answer

The most important difference here is that there's no group by rank.

select *
from countries_by_population
where population / (select avg(population) from countries_by_population) 
    between 0.66 and 1.33 /* approximate range */