Find Top Countries per Continent

Q: Find Top Countries per Continent

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.

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 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:

Return the most populous country in each continent.
Follow-up: return the top 2 countries by population in each continent.
For the top 2 countries in each continent, calculate each country's share of that continent's total population.

Definitions:

population_share_pct = 100 * country_population / total_population_of_continent
If two countries tie on population , break ties by country in ascending order so the result is deterministic.

Required output:

Task 1: continent, country, population
Task 2: continent, country, population, population_rank_within_continent
Task 3: continent, country, population, total_continent_population, population_share_pct

Find Top Countries per Continent

Quick Overview

Comments (0)