Guided Project: CIA Factbook SQL unable to create rank() column

(A)
I am trying to get an auto-incrementing row showing rank of populations
1.China
2.India
etc
but cannot get the rank function to work instead I get 287…China 288…India please advise?

(B)
The query without myrank column does not show China.
Could it be explained why not please?

%%sql
SELECT name,population,
CAST(CAST(population AS FLOAT) / 
(SELECT MAX(population) FROM facts)*100 AS INT)||'%' 'Percent of world population'
FROM facts
WHERE name <> 'World' AND name <> 'European Union'
ORDER BY 3 DESC
LIMIT 5;

hi @jamesberentsen

I hope I am able to solve your queries in first attempt this time :pray:ing! :stuck_out_tongue:

  • For the first query, in OVER clause add DESC clause as well. It is right now taking the population in ascending order, thereby China is coming as 259 instead of 1.
    It’s ranking the country with the lowest population as 1 and then adding 1 to the subsequent countries, so China is coming as last at 259.
    As total rows in facts table are 261 - you have excluded World and European Union, so 259 rows remaining now.

  • For the second query, I just changed ORDER BY 3 DESC to ORDER BY 2 DESC and it seems to do the trick!

1 Like

Many thanks Rucha,

Yes you got right first attempt at explaining.


Thanks for your advice.
I spent long time searching for how to get the rank() to work, but could not find.

Oh I see why now for 2nd query thanks.

Regards,
JB

1 Like

hey @jamesberentsen

Thanks for the Rank() query. That was new for me. :slight_smile: Just glad your queries have been answered.

I didn’t include this earlier as I didn’t debug it then. The second query gives the 4% first because the addition of % makes the 4 loose its numeric meaning, instead, it becomes a string value as in - 0.04 = float, 4 = int but 4% = string/ char

So when we try to arrange the 3rd column, SQL takes the sequence this way - 1%, 1%, 11%, 17%, 18%, 2%, 2%, 3% 4% and so on. But if you remove the ||'%' and try with ORDER BY 3 DESC, you will get China first.

1 Like

Hi Rucha,

Thanks . Fantastic explanation, I wondered where you found the knowledge for why rank was not working? I read a lot of documentation but it did not describe DESC in detail.

That is fantastic debugging with this too, I wonder where did you find the debugging tool, or how did you know what to look for/learn what to look for is that on the data science course?

The second query gives the 4% first because the addition of % makes the 4 loose its numeric meaning, instead, it becomes a string value as in - 0.04 = float, 4 = int but 4% = string/ char