Find top countries per continent

Q: Find top countries per continent

This question evaluates competency in SQL data manipulation, including grouped aggregation, deterministic partitioned ranking with tie-breaking, and computing percentage shares of group totals.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

You are given a table world_population that contains one row per country for a single population snapshot.

Schema:

continent STRING
country STRING
population BIGINT

Assumptions:

Each row represents one country.
All populations are measured for the same snapshot date.
population is non-null.
If two countries in the same continent have the same population, break ties by country ascending so the ranking is deterministic.
Timezone is not relevant because this is a single static snapshot.

Write SQL for the following:

Return the single most populous country in each continent.
- Output columns: continent, country, population
Return the top 2 countries by population in each continent.
- Output columns: continent, country, population, population_rank
For only those top 2 countries in each continent, compute each country's share of its continent's total population.
- Define population_share_pct = 100 * population / total_continent_population
- Output columns: continent, country, population, population_rank, total_continent_population, population_share_pct

Find top countries per continent

Quick Overview

Comments (0)