Wednesday, January 9, 2019

SQL question challenge (Continents)


Link to github

Table with the columns: continents: continent, country, population
CREATE TABLE continents (
   continent          varchar(80),
   country            varchar(80),
   population         int
  );

  INSERT INTO continents VALUES ('Asia','China', 100);
  INSERT INTO continents VALUES ('Asia','India', 100);
  INSERT INTO continents VALUES ('Africa','South Africa', 50);
  INSERT INTO continents VALUES ('Africa','Egypt', 20);
  INSERT INTO continents VALUES ('North America','USA', 50);
  INSERT INTO continents VALUES ('North America','Canada', 50);



QUESTIONS

1)Find the country with largest population in each continent, with strictly output: continent, country, population. Consider corner case that two country have same largest population in the same continent
select a.continent,a.country,a.population 
   from (select continent,country,population,
     dense_rank() 
                           over (partition by continent order by population desc) 
     as rnk from continents
     ) a 
    where rnk=1;
   +---------------+--------------+------------+
   | continent     | country      | population |
   +---------------+--------------+------------+
   | Africa        | South Africa |         50 |
   | Asia          | China        |        100 |
   | Asia          | India        |        100 |
   | North America | USA          |         50 |
   | North America | Canada       |         50 |
   +---------------+--------------+------------+
  select a.continent,b.country,a.max_pop 
   from (select continent,max(population) as max_pop 
     from continents group by continent
      ) a 
   join continents b 
     on a.continent=b.continent 
     and a.max_pop=b.population;
   +---------------+--------------+---------+
   | continent     | country      | max_pop |
   +---------------+--------------+---------+
   | Asia          | China        |     100 |
   | Asia          | India        |     100 |
   | Africa        | South Africa |      50 |
   | North America | USA          |      50 |
   | North America | Canada       |      50 |
   +---------------+--------------+---------+

No comments:

Post a Comment