Abstract / Summary

Find names of countries with a population of at least 3 million in a given table.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 69% (3+ million submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL statement retrieves a subset of rows from the World table, focusing on countries that satisfy either a large land-area threshold or a substantial population count. The query selects three columns-name, population, and area-and applies a filter that combines two comparison predicates with a logical OR. This design enables the extraction of records that meet at least one of the defined criteria, ensuring that both geographically extensive nations and densely populated ones are represented in the result set.

Key Steps

Selection of Columns

The query explicitly lists the columns to be returned: name, population, and area. By specifying these fields, the statement avoids returning unnecessary data, which reduces the amount of information transferred from the database engine to the client.

SELECT name, population, area

Source Table Identification

The FROM clause designates the World table as the data source. This table is presumed to contain one row per country, with attributes that include the country name, its population, and its total land area.

FROM World

Filtering Logic

The WHERE clause implements a dual-condition filter. The first predicate checks whether a country's area is greater than or equal to three million square units. The second predicate evaluates whether the population meets or exceeds twenty-five million individuals. The logical OR ensures that a row satisfying either condition passes the filter.

WHERE area >= 3000000 OR population >= 25000000

Edge Cases Handled

The logical OR construct inherently accommodates scenarios where a country meets only one of the thresholds. For example, a nation with a modest land area but a very high population will still be included because the population predicate evaluates to true. Conversely, a country with a vast territory but a relatively small population will also be captured due to the area predicate.

When either area or population contains a NULL value, the comparison with a numeric constant yields UNKNOWN, which the SQL three-valued logic treats as false for the purpose of the OR expression. Consequently, rows with NULL in both columns are excluded, while rows with a valid value in at least one column can still satisfy the filter if that value meets its respective threshold. This behavior prevents the accidental inclusion of incomplete records while still allowing partial data to qualify.

Performance Optimizations Observed

The query’s simplicity lends itself to efficient execution, especially when appropriate indexes exist on the filtered columns. An index on area enables rapid location of rows whose land area meets the lower bound, while an index on population serves the same purpose for the population criterion. Because the filter uses an OR, the optimizer can perform an index union, merging the result sets from both indexes without scanning the entire table.

Additionally, the explicit column list reduces the amount of data that must be read from disk and transmitted over the network, as the engine can perform a covering index scan if the selected columns are included in the index definition. This eliminates the need for a separate lookup to retrieve non-indexed columns, further streamlining the operation.

The use of constant literals for the thresholds allows the optimizer to treat the predicates as static, enabling pre-computed statistics to guide plan selection. This contributes to consistent performance across repeated executions of the statement.

Imagine that a giant book called World keeps track of every country. Inside this book are big pages, and each page has a list of names, how many people live in each place, and how much land there is. The computer is acting like a librarian here. It looks through every single page in the book to find specific countries. It only keeps the ones that are either very big because they have a massive amount of area or very busy because they have a huge number of people living there. Once the computer finds these special countries, it writes down their names, their population size, and their land area on a piece of paper for everyone to see.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

Time: linear
As a rule of thumb, the maximum input size for one second execution time is

• factorial: up to 10 elements
• exponential: up to 20 elements
• polynomial: a few thousand elements
• loglinear: up to 100,000 elements
• linear: a few million elements
• logarithmic: a billion elements
• constant: practically unlimited

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	fsq	SQL
	keineahnung2345	SQL
	Krazune	SQL
	lzl124631x	SQL
	muhammadfarhankt	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
fsq	SQL
keineahnung2345	SQL
Krazune	SQL
lzl124631x	SQL
muhammadfarhankt	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality