country with the highest number of tests(oracle plsql)

Question

Data Model:

Hi, I am trying to get "country with the highest number of tests".

Query:

I tried using one table.. ok... but how I get it with "countryname"? How should I make this with inner join?

Answer 1

Join, as you said.

select s.countryname,
       s.date_,
       s.total_tests
from (select 
         row_number() over (order by a.total_tests desc) rn,
         a.date_,
         a.total_tests,
         c.countryname
      from cases_by_countries a join country c 
        on c.countryid = a.country_id       
     ) s
where s.rn = 1;