Find top countries by population per continent
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
### 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.
Overview: This question evaluates data manipulation competency in SQL/Python, specifically testing aggregation, ranking, deterministic tie-breaking, and percentage calculations across grouped data to identify 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.
Tables
world_population(continent VARCHAR(50), country VARCHAR(100), population BIGINT)
Hints
- ROW_NUMBER() OVER (PARTITION BY continent ORDER BY population DESC, country ASC) gives a deterministic per-continent rank.
- A windowed SUM(population) OVER (PARTITION BY continent) computed BEFORE filtering to the top 2 gives the whole continent's total.
Community answers
Answer by blanchey218
Q3: WITH cte AS (
SELECT
continent, country, population,
SUM(population) OVER (PARTITION BY continent) AS total,
ROW_NUMBER() OVER (PARTITION BY continent ORDER BY population DESC) AS rn
FROM world
)
SELECT
continent,
country,
1.0 * population / total AS ratio
FROM cte
WHERE rn <= 2
ORDER BY continent, rn;
Answer by michelleyuu
I think you can reduce the number of CTEs, but I split into 2 groups, the total_pop sum and the ranking:
-- Write your SQL query here
WITH total_pop AS
(SELECT
continent,
SUM(population) AS continent_population
FROM world_population
GROUP BY continent),
pop_count AS
(SELECT
w.continent,
country,
population,
continent_population,
ROUND(population / continent_population * 100, 2) AS population_share_pct,
DENSE_RANK() OVER(PARTITION BY w.continent ORDER BY population DESC, country ASC) AS rank_in_continent
FROM world_population w
JOIN total_pop t
ON w.continent = t.continent)
SELECT
continent,
country,
population,
rank_in_continent,
continent_population,
population_share_pct
FROM pop_count
WHERE rank_in_continent <= 2