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.