Find top countries by population per continent

Table

world_population

• continent VARCHAR
• country VARCHAR
• population BIGINT

Assume each row is a country’s latest population and (continent, country) is unique.

Tasks

1. Return the most populous country in each continent.
   • Output columns: continent, country, population.
2. Follow-up: Return the top 2 most populous countries in each continent.
   • Output columns: continent, country, population, plus a rank_in_continent (1 = largest).
3. Follow-up: For each continent’s top 2 countries, compute what percentage of the continent’s total population each country represents.
   • Output columns: continent, country, population, continent_population, population_share_pct.
   • population_share_pct = 100 * population / continent_population (round to 2 decimals).

Notes

• If there are ties in population, break ties by country ascending to make results deterministic.

Top countries by population per continent

You are given a table world_population where each row is a country's latest population and (continent, country) is unique.

The interviewer asks three progressive questions:

1. Return the most populous country in each continent (continent, country, population).
2. Follow-up: Return the top 2 most populous countries in each continent, adding a rank_in_continent column (1 = largest).
3. Follow-up: For each continent's top 2 countries, compute what percentage of the continent's total population each country represents.

Write ONE query that answers the final follow-up and subsumes the earlier parts. For the top 2 most populous countries in each continent, return:

• continent
• country
• population
• rank_in_continent — 1 = most populous in that continent
• continent_population — the total population of all countries in that continent (not just the top 2)
• population_share_pct — 100 * population / continent_population, rounded to 2 decimals

Notes:

• If countries tie on population, break ties by country ascending so results are deterministic.
• A continent with fewer than 2 countries returns only the rows it has.
• Rows may be returned in any order.

Table world_population

column	type	constraints
continent	VARCHAR(50)	NOT NULL
country	VARCHAR(100)	NOT NULL
population	BIGINT	NOT NULL

Sample data:

continent	country	population
Asia	China	1382323332
Asia	India	1326801576
Asia	Indonesia	260581100
Asia	Vietnam	94444200
North America	United States of America	324118787
North America	Mexico	128632004
North America	Canada	36286378
Europe	Germany	82000000
Europe	France	67000000
Europe	United Kingdom	67000000
Europe	Italy	60000000
Africa	Nigeria	98000000
Africa	Egypt	98000000
Africa	Ethiopia	97000000
South America	Brazil	210000000
South America	Argentina	44000000
Oceania	Australia	25690000

Expected output

continent	country	population	rank_in_continent	continent_population	population_share_pct
Africa	Egypt	98000000	1	293000000	33.45
Africa	Nigeria	98000000	2	293000000	33.45
Asia	China	1382323332	1	3064150208	45.11
Asia	India	1326801576	2	3064150208	43.3
Europe	Germany	82000000	1	276000000	29.71
Europe	France	67000000	2	276000000	24.28
North America	United States of America	324118787	1	489037169	66.28
North America	Mexico	128632004	2	489037169	26.3
Oceania	Australia	25690000	1	25690000	100
South America	Brazil	210000000	1	254000000	82.68
South America	Argentina	44000000	2	254000000	17.32

Hints

1. ROW_NUMBER() OVER (PARTITION BY continent ORDER BY population DESC, country ASC) gives a deterministic per-continent rank.
2. A windowed SUM(population) OVER (PARTITION BY continent) computed BEFORE filtering to the top 2 gives the whole continent's total.
3. Watch out for integer division: use 100.0 (or a numeric cast) when computing the percentage, then ROUND(..., 2).