Introduction  >>>

Scenario world

See details on the model

In this scenario, we have a single table world in which each row describes a country. The table contains the following attributes:

  • name of type VARCHAR(50) NOT NULL, the name of the country (e.g. France)
  • continent of type VARCHAR(60), in which continent is the country in (e.g. Europe)
  • area of time DECIMAL(10) the area of the country in square kilometer
  • population of type DECIMAL(11) the number of inhabitants
  • gdp of type DECIMAL(14) the Gross Domestic Product
  • capital of type VARCHAR(60) the name of the capital city
  • tld of type VARCHAR(5) the Top Level Domain of the country (the last part of the domain names of the country, e.g. .fr for France)
  • flag of type VARCHAR(255) an url of the flag of the country

Question

Compute for each country its size rank and its area rank (i.e. the i and j such that it is the i-th most populated and the j-th largest).

OVER() aggregation without GROUP BY

There is a way in SQL to compute some aggregates without aggregation. A typical example is to compute the rank according to some order. To compute such "aggregate" the syntax is:

SELECT agg_fun(col) OVER(PARTITION BY criterion ORDER BY criterion) FROM ...

Here, agg_fun(col) is an aggregation over the column "col" (so either MAX, SUM, etc. or RANK to give the RANK), ORDER BY defines the order in which we add elements in the aggregate and partition gives "boundaries" where we reset the aggregation function.

For instance to compute the order of countries by area within each continent we can use the following:

SELECT name, continent, RANK() OVER(PARTITION BY continent ORDER BY area DESC ) as r 
  FROM world 
  WHERE area is NOT NULL 
  ORDER BY continent, r;

Query


...
Run query first