Skip to content

LeetCode 595 - Big Countries

Database Language: PostgreSQL

Difficulty: ⭐

Problem Description

Input

Table: World

Column Name Type
name varchar
continent varchar
area int
population int
gdp bigint

name is the primary key (column with unique values) for this table. Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.

Requirement

A country is big if:

  • it has an area of at least three million (i.e., 3000000 km2), or
  • it has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

Input

World table:

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000
Output
name population area
Afghanistan 25500100 652230
Algeria 37100000 2381741
Explanation

SQL Schema

CREATE TABLE IF NOT EXISTS World (name VARCHAR(255) PRIMARY KEY, continent VARCHAR(255), area INT, population INT, gdp BIGINT);

TRUNCATE TABLE World;
INSERT INTO World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000');
INSERT INTO World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000');

Solutions

Coming up with the query for this question should be straight forward. Based on the sample output, out of the 5 columns in the World table, the required output columns are just the name, population and area columns. Since the output columns already match the column names of the World table, column aliases are not needed.

SELECT name, population, area
FROM World
name population area
Afghanistan 25500100 652230
Albania 2831741 28748
Algeria 37100000 2381741
Andorra 78115 468
Angola 20609294 1246700

To get the list of big countries, the following conditions will be used based on the description of the requirements above:

  • area >= 3000000
  • population >= 25000000

Since a country is considered big if either of this two conditions are met, so the OR logical operator will be used. The WHERE clause of the query will be as follows:

WHERE area >= 3000000 OR population >= 25000000

Putting them all together yields the following query:

# Final Solution Query
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000

Here's the query plan generated by PostgreSQL for this query:

Seq Scan on world  (cost=0.00..11.05 rows=39 width=524)
  Filter: ((area >= 3000000) OR (population >= 25000000))

And here's the fastest run time for this query:

  • Runtime: 287ms
  • Beats: 95.34% as of July 27, 2024

One may wonder, will the query be faster if the conditions were interchanged? So, instead of WHERE area >= 3000000 OR population >= 25000000, what if the conditions were interchanged into WHERE population >= 25000000 OR area >= 3000000? Will this query be faster especially if there are lot of rows in the table that will satisfy the first condition?

Using the following updated query:

SELECT name, population, area FROM World
WHERE population >= 25000000 OR area >= 3000000;

The fastest runtime is as follows, which was just a few milliseconds slower than the first version:

  • Runtime: 288ms
  • Beats: 94.47% as of July 27, 2024

The generated query plan is similar to the query plan for the first query but just with the filter interchanged to match the WHERE clause:

Seq Scan on world  (cost=0.00..11.05 rows=39 width=524)
  Filter: ((population >= 25000000) OR (area >= 3000000))

According to PostgreSQL Value Expressions - Expression Evaluation Rules:

The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order.

Furthermore, if the result of an expression can be determined by evaluating only some parts of it, then other subexpressions might not be evaluated at all. For instance, if one wrote:

SELECT true OR somefunc();

then somefunc() would (probably) not be called at all. The same would be the case if one wrote:

SELECT somefunc() OR true;

Note that this is not the same as the left-to-right “short-circuiting” of Boolean operators that is found in some programming languages.