Basic Filtering Problems

Problem Statement - 1 (Big Countries)

A global research organisation is analysing countries based on their physical size and population.

You are given a table named World that stores information about countries.

Your task is to identify all big countries.

A country is considered big if:

• its area is at least 3,000,000 km²OR
• its population is at least 25,000,000

You need to return:

• name
• population
• area

The result can be returned in any order.

Table Schema

Column Meaning

Goal

Return the name, population, and area of all countries that are considered big.

A country is big if:

• area >= 3000000 OR
• population >= 25000000

Example

Input — World

Output

CREATE TABLE World (name TEXT, continent TEXT, area INTEGER, population INTEGER, gdp INTEGER);
INSERT INTO World VALUES
  ('Greenland', 'North America', 652230, 25500100, 20343000000),
  ('Iceland', 'Europe', 28748, 2831741, 12960000000),
  ('Brazil', 'South America', 2381741, 37100000, 188681000000),
  ('Fiji', 'Oceania', 468, 78115, 3712000000),
  ('Nepal', 'Asia', 1246700, 20609294, 100990000000);

Explanation

Greenland

• Area = 652,230 → not large enough
• Population = 25,500,100 → qualifies

Included because population is at least 25M.

Brazil

• Area = 2,381,741 → not large enough
• Population = 37,100,000 → qualifies

Included because population is at least 25M.

Iceland

• Area is too small
• Population is too small

Excluded.

Fiji

• Area is too small
• Population is too small

Excluded.

Nepal

• Area is below 3M
• Population is below 25M

Excluded.

Intuition

This problem is about filtering rows based on conditions.

We do not need:

• sorting
• grouping
• joins
• aggregation

We only need to:

1. read the required columns
2. filter rows based on given rules

This is a direct use of:

• SELECT → choose required columns
• WHERE → filter rows
• OR → match either condition

Mental Model

Think of this problem as:

“Show me only those countries that are big enough by land size or by population.”

A country can qualify in two different ways:

• either it has huge land
• or it has huge population

It does not need both.

That is the most important observation.

So the keyword here is:

OR

Not AND.

Why OR and Not AND?

This is the most important part of the problem.

The condition says:

• area is largeOR
• population is large

This means a country is valid even if only one condition is true.

If we used AND

WHERE area >= 3000000 AND population >= 25000000

This would mean:

• area must be large
• and population must also be large

That is stricter than the problem asks.

You would incorrectly remove valid countries like:

• Greenland
• Brazil

because their area is smaller than 3M.

So AND would give wrong results.

Approach

Step 1: Select required columns

We only need:

• name
• population
• area

So select only these columns.

Step 2: Apply filtering

Keep only countries where:

• area >= 3000000 OR
• population >= 25000000

This is done using the WHERE clause.

Step 3: Return result

Return all rows that satisfy at least one condition.

SQL Query

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

Query Breakdown

SELECT name, population, area

Return only the required columns.

FROM World

Read data from the World table.

WHERE area >= 3000000 OR population >= 25000000

Keep only countries where:

• area is at least 3M OR
• population is at least 25M

Why This Works

The query checks every country row one by one.

For each country, SQL asks:

• Is area at least 3,000,000?
• OR is population at least 25,000,000?

If yes → keep the row

If no → discard it

That exactly matches the problem statement.

Time Complexity

O(n)

SQL scans each row once and checks the condition.

Where n is the number of rows in World.

Space Complexity

O(1)

No extra memory is used apart from the output.

Key Learning

This problem teaches one of the most important SQL basics:

• how to filter rows
• how to apply multiple conditions
• when to use OR

Problem Insight

This is a classic beginner SQL filtering problem.

The interviewer is mainly testing whether you understand:

• SELECT
• WHERE
• OR

The trick is not SQL syntax.

The trick is correctly understanding the condition.

Most common mistake:

using AND instead of OR.

Common Mistakes to Avoid

1. Using AND instead of OR

Wrong:

WHERE area >= 3000000 AND population >= 25000000

This removes valid rows.

2. Selecting unnecessary columns

Do not return extra columns like:

• continent
• gdp

The problem only asks for:

• name
• population
• area

3. Misreading the condition

The country qualifies if either one condition is true.

Not both.

Final Takeaway

Whenever a problem says:

• this condition or
• that condition

your brain should immediately think:

WHERE ... OR ...

This is a pure filtering problem.

The core skill being tested is:

Can you correctly translate business rules into SQL conditions?

Problem Statement - 2 Customers With Positive Revenue in 2021

A company keeps yearly revenue records for customers.

You need to identify customers who generated positive revenue in the year 2021.

These customers are valuable because they can be targeted for:

• loyalty programs
• upsell opportunities
• retention campaigns
• customer value analysis

You are given a table named Customers.

Your task is to return all customer_id values where:

• year = 2021
• and revenue > 0

The result can be returned in any order.

Table Schema

Column Meaning

Key Constraint

(customer_id, year) is the primary key.

This means:

• one customer can appear multiple times
• but only once per year

So each customer has at most one revenue record for 2021.

This is important because it means we do not need aggregation.

Goal

Return all customer_id values where:

• revenue was recorded in 2021
• and revenue was greater than 0

Example

Input — Customers

CREATE TABLE Customers (customer_id INTEGER, year INTEGER, revenue INTEGER);
INSERT INTO Customers VALUES
  (1, 2018, 50),
  (1, 2021, 30),
  (1, 2020, 70),
  (2, 2021, -50),
  (3, 2018, 10),
  (3, 2016, 50),
  (4, 2021, 20);

Output

customer_id

1

4

Explanation

Customer 1

Has a record for 2021 and revenue = 30

30 > 0 → included

Customer 2

Has a record for 2021 but revenue = -50

• 50 is not positive → excluded

Customer 3

Has no row for 2021

No 2021 record → excluded

Customer 4

Has a record for 2021 and revenue = 20

20 > 0 → included

Intuition

This is a pure filtering problem.

We do not need:

• joins
• grouping
• sorting
• aggregation
• subqueries

We only need to identify rows that satisfy both business rules.

This is a direct use of:

• SELECT
• WHERE
• AND

Mental Model

Think of this problem as:

“Show me customers who earned money in 2021.”

A customer qualifies only if both conditions are true:

• the record belongs to 2021
• the revenue is positive

This means both rules must pass together.

So the keyword here is:

AND

Not OR.

Why AND and Not OR?

This is the most important part of the problem.

The customer must satisfy both conditions:

• year must be 2021
• revenue must be greater than 0

That means both conditions are mandatory.

If we used OR

WHERE year = 2021 OR revenue > 0

This would return wrong rows.

It would include:

• customers from other years with positive revenue
• customers in 2021 even if revenue is negative

That is incorrect.

So OR would break the logic.

Approach

Step 1: Select required column

We only need:

• customer_id

So return only that column.

Step 2: Filter for 2021

Keep only rows where:

year = 2021

This removes all other years.

Step 3: Keep only positive revenue

From 2021 rows, keep only those where:

revenue > 0

This removes zero and negative revenue customers.

Step 4: Return result

Return customer IDs that satisfy both conditions.

SQL Query

SELECT customer_id
FROM Customers
WHERE year = 2021
  AND revenue > 0;

Query Breakdown

SELECT customer_id

Return only the customer ID.

FROM Customers

Read from the Customers table.

WHERE year = 2021

Keep only rows from the year 2021.

AND revenue > 0

From those rows, keep only customers with positive revenue.

Why This Works

SQL checks every row in the Customers table.

For each row, it asks:

1. Is the year 2021?
2. Is revenue greater than 0?

If both are true → keep the row

If either fails → discard it

That exactly matches the problem.

Time Complexity

O(n)

SQL scans each row once.

Where n is the number of rows in Customers.

Space Complexity

O(1)

No extra memory is used apart from the output.

Key Learning

This problem teaches:

• how to filter by multiple conditions
• when to use AND
• how to translate business rules into SQL

Problem Insight

This is a basic SQL filtering problem.

The interviewer is testing whether you understand:

• WHERE
• AND
• filtering by multiple conditions

The trick is not syntax.

The trick is understanding that both conditions must be true.

Common Mistakes to Avoid

1. Using OR instead of AND

Wrong:

WHERE year = 2021 OR revenue > 0

This includes incorrect rows.

2. Forgetting the year filter

Wrong:

WHERE revenue > 0

This returns customers from all years.

3. Forgetting revenue condition

Wrong:

WHERE year = 2021

This includes negative revenue customers.

4. Overthinking with GROUP BY

Not needed.

Each customer has only one row per year because:

(customer_id, year)

is already unique.

So no aggregation is required.

Final Takeaway

Whenever a problem says:

• specific year
• specific condition
• both must be true

your brain should immediately think:

WHERE condition_1 AND condition_2

This is a simple filtering problem.

The core skill being tested is:

Can you correctly apply multiple conditions using AND?

Problem Statement - 3 Not Boring Movies

A movie streaming platform wants to feature movies that are more engaging and unique.

You are given a table named Cinema that stores movie information.

Your task is to return movies that satisfy all of the following:

• the movie id is an odd number
• the description is not exactly 'boring'
• sort the final result by rating in descending order

Return the result in the required sorted format.

Table Schema

Column Meaning

Goal

Return all movies where:

• id is odd
• description is not 'boring'

Then sort by:

• rating descending (highest first)

Example

Input — Cinema

Output

CREATE TABLE Cinema (id INTEGER, movie TEXT, description TEXT, rating REAL);
INSERT INTO Cinema VALUES
  (1, 'Galaxy Raid', 'great 3D', 8.9),
  (2, 'Future Code', 'fiction', 8.5),
  (3, 'Old Times', 'boring', 6.2),
  (4, 'Winter Dream', 'Fantacy', 8.6),
  (5, 'Silent Echo', 'Interesting', 9.1);

Explanation

Movie 1 — Galaxy Raid

• id = 1 → odd
• description = great 3D → not boring

Included.

Movie 2 — Future Code

• id = 2 → even

Excluded.

Movie 3 — Old Times

• id = 3 → odd
• description = boring

Excluded because description is boring.

Movie 4 — Winter Dream

• id = 4 → even

Excluded.

Movie 5 — Silent Echo

• id = 5 → odd
• description = Interesting → not boring

Included.

After filtering, sort remaining movies by rating (highest first).

So:

• Silent Echo (9.1)
• Galaxy Raid (8.9)

Intuition

This problem is a combination of:

• filtering
• logical conditions
• sorting

We need to:

1. keep only odd IDs
2. remove boring movies
3. sort by highest rating

This is a direct use of:

• WHERE
• AND
• arithmetic operator %
• ORDER BY DESC

Mental Model

Think of this problem as:

“Show only interesting featured movies.”

A movie must pass all screening rules:

• odd ID only
• not boring
• highest rated first

That means:

• filtering first
• sorting after

Key Observation

This problem has 3 parts:

1. Odd check
2. Text filter
3. Sorting

Solve them in that order.

Step 1 — Keep Only Odd IDs

We only want odd-numbered movie IDs.

To check odd numbers in SQL, use modulus:

id % 2 = 1

Why this works

% returns the remainder.

• 1 % 2 = 1 → odd
• 2 % 2 = 0 → even
• 3 % 2 = 1 → odd
• 4 % 2 = 0 → even

So odd numbers are identified using:

id % 2 = 1

Step 2 — Exclude Boring Movies

We do not want movies whose description is exactly:

'boring'

So exclude them using:

description != 'boring'

This keeps all non-boring movies.

Step 3 — Sort by Rating

After filtering, sort by rating from highest to lowest.

Use:

ORDER BY rating DESC

This ensures top-rated movies appear first.

Approach

Step 1: Read from Cinema

We need movie records from the Cinema table.

Step 2: Filter odd IDs

Keep only rows where movie ID is odd.

Step 3: Remove boring movies

Exclude rows where description is exactly 'boring'.

Step 4: Sort by rating

Sort remaining rows by rating in descending order.

SQL Query

SELECT *
FROM Cinema
WHERE id % 2 = 1
  AND description != 'boring'
ORDER BY rating DESC;

Query Breakdown

SELECT *

Return all columns.

The problem asks for full movie details.

FROM Cinema

Read data from the Cinema table.

WHERE id % 2 = 1

Keep only odd movie IDs.

AND description != 'boring'

From those, remove boring movies.

ORDER BY rating DESC

Sort remaining movies from highest rated to lowest.

Why This Works

SQL checks each movie row one by one.

For every row:

1. Is the ID odd?
2. Is description not boring?

If both are true → keep it

If not → discard it

Then SQL sorts the remaining rows by rating in descending order.

That exactly matches the problem statement.

Time Complexity

O(n log n)

• O(n) to scan/filter rows
• O(n log n) to sort by rating

Sorting dominates.

Space Complexity

O(1) extra space

(excluding output)

Key Learning

This problem teaches how to combine:

• arithmetic filtering
• text filtering
• multiple conditions
• sorting

It is one of the best beginner problems for combining SQL basics.

Problem Insight

This problem checks whether you understand:

• modulus %
• AND
• string filtering
• ORDER BY DESC

The SQL is simple.

The real test is whether you can combine multiple small concepts correctly.

Common Mistakes to Avoid

1. Forgetting odd check

Wrong:

WHERE description != 'boring'

This includes even IDs.

2. Forgetting boring filter

Wrong:

WHERE id % 2 = 1

This includes boring movies.

3. Sorting in wrong direction

Wrong:

ORDER BY rating ASC

This puts worst movies first.

We need highest-rated first → DESC

4. Using OR instead of AND

Wrong:

WHERE id % 2 = 1 OR description != 'boring'

This includes many invalid rows.

Both conditions must be true.

Final Takeaway

This problem is a perfect example of SQL query flow:

1. filter rows
2. apply multiple conditions
3. sort results

Whenever a problem says:

• keep rows matching rule 1
• keep rows matching rule 2
• then sort

your brain should think:

WHERE condition_1
  AND condition_2
ORDER BY column DESC