<<< Rank in continent
Scenario world
f35f7fece0cf0019765619c477c900ddd8131dbc27b32e9261f4ab7cdc49773c
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) both at the world level and the continental level. Output only the rankings for European countries and remove from consideration countries with NULL area or NULL population.
{"rows":[["Germany",61,16,6,1],["France",40,19,2,2],["United Kingdom",76,21,11,3],["Italy",70,23,10,4],["Spain",50,27,4,5],["Ukraine",44,30,3,6],["Poland",68,33,9,7],["Romania",79,56,12,8],["Kazakhstan",9,60,1,9],["Netherlands",129,62,30,10],["Belgium",135,73,33,11],["Greece",93,75,14,12],["Czech Republic",113,80,21,13],["Portugal",107,81,18,14],["Hungary",106,85,17,15],["Sweden",54,86,5,16],["Belarus",82,88,13,17],["Austria",111,93,20,18],["Switzerland",130,95,31,19],["Bulgaria",101,98,15,20],["Serbia",109,99,19,21],["Denmark",128,110,29,22],["Finland",64,111,7,23],["Slovakia",125,112,27,24],["Norway",67,115,8,25],["Ireland",116,119,22,26],["Croatia",122,124,25,27],["Bosnia and Herzegovina",123,126,26,28],["Moldova",134,127,32,29],["Lithuania",119,133,23,30],["Albania",139,135,34,31],["Slovenia",149,140,36,32],["Macedonia",144,141,35,33],["Latvia",120,143,24,34],["Estonia",127,149,28,35],["Montenegro",154,160,37,36],["Luxembourg",165,162,38,37],["Malta",181,165,40,38],["Iceland",104,168,16,39],["Andorra",174,180,39,40],["Liechtenstein",185,184,41,41],["Monaco",189,185,43,42],["San Marino",186,186,42,43],["Vatican City",190,190,44,44]],"fields":[{"name":"name","dataTypeID":1043},{"name":"area_order_world","dataTypeID":20},{"name":"pop_order_world","dataTypeID":20},{"name":"area_order_continent","dataTypeID":20},{"name":"pop_order_continent","dataTypeID":20}],"affectedRows":0}
False
False