This question evaluates a candidate's competency in data manipulation with SQL or Python, focusing on aggregation and grouping, ranking within partitions, deterministic tie-breaking, and computation of percentage shares.
You are given a country population snapshot table:
world_population(continent VARCHAR, country VARCHAR, population BIGINT)
Each row represents the latest known population for one country. Assume all rows refer to the same point in time, so there are no timezone considerations.
Tasks:
Definitions:
population_share_pct = 100 * country_population / total_population_of_continent
population
, break ties by
country
in ascending order so the result is deterministic.
Required output:
continent, country, population
continent, country, population, population_rank_within_continent
continent, country, population, total_continent_population, population_share_pct