CODEWITHSUNDEEP
sqlsql-rank
Aditi Inamdar
Aditi Inamdar

Reputation: 1

Window lag sqlzoo.net #7

The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).

Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.

Ans.

SELECT world.name,
       ROUND(100000*confirmed/population,00) as rd,
       rank() over (order by rd)
FROM covid 
   JOIN world ON covid.name=world.name
WHERE whn = '2020-04-20' 
  AND population > 10000000
ORDER BY population DESC

The above code is my solution, but still, I am getting the wrong answer. Does anyone know the correct solution?

Upvotes: 0

Views: 1197

Answers (2)

sobiwa
sobiwa

Reputation: 1

  1. The answer on the site rounds to 2 decimal points, so that needs to be specified in the ROUND function.

  2. If using ORDER BY rf (the variable created on line 2) in conjunction with the RANK function, the RANK function uses that now rounded number which renders values equal, creating ties in the ranking (e.g., Nepal and Ethiopia are both 6). The answer uses the raw data, therefore eliminating ties in the ranks. So the query that will give you that satisfying little smily is:

    SELECT world.name,
   ROUND(100000*confirmed/population,2),
   RANK() OVER(ORDER BY 100000*confirmed/population) AS rank
FROM covid JOIN world ON covid.name=world.name
WHERE whn = '2020-04-20' AND population > 10000000
ORDER BY population DESC

Upvotes: 0

Ajeetesh Mishra
Ajeetesh Mishra

Reputation: 69

Here infection rate is confirmed/population; so the query goes as-

SELECT world.name,
       ROUND(100000*confirmed/population,0),
       RANK() OVER (ORDER BY confirmed/population) AS rank
FROM covid JOIN world ON covid.name=world.name
WHERE whn = '2020-04-20' AND population > 10000000
ORDER BY population DESC

Upvotes: 1

Related Questions