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.
Quick Answer: 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.