Filtering Problems

Problem 1

Filter Records Excluding a Specific Pattern

Problem Statement

A company stores customer email addresses and wants to identify users who are not using Gmail.

This helps the analytics team understand how many users are coming from external email providers such as Yahoo, Outlook, ProtonMail, company domains, and others.

You are given a table named Users.

Table: Users

• user_id is the primary key.
• Each row represents one registered user.

Your task is to return:

• user_id
• name
• email

…for all users whose email does not end with @gmail.com.

The result can be returned in any order.

Example

Input

Output

CREATE TABLE Users (user_id INTEGER, name TEXT, email TEXT);
INSERT INTO Users VALUES
  (1, 'Aarav', 'aarav@gmail.com'),
  (2, 'Neha', 'neha@yahoo.com'),
  (3, 'Rohan', 'rohan@gmail.com'),
  (4, 'Meera', 'meera@outlook.com');

Explanation

We need to exclude users whose email ends with @gmail.com.

Let us check each row:

• Aarav → aarav@gmail.com → ends with @gmail.com → exclude
• Neha → neha@yahoo.com → not Gmail → include
• Rohan → rohan@gmail.com → ends with @gmail.com → exclude
• Meera → meera@outlook.com → not Gmail → include

So only Neha and Meera remain.

Intuition

This problem is about excluding a text pattern.

Usually in SQL, we use LIKE when we want to match a text pattern.

But here we want the opposite:

do not show emails matching Gmail

So instead of saying:

find Gmail users

we say:

remove Gmail users and keep everyone else

That is exactly what NOT LIKE is built for.

Mental Model

Think of this like filtering a customer list for marketing.

You already know Gmail users are common.

But now the business wants to study:

• Yahoo users
• Outlook users
• Work email users
• Other external domains

So instead of selecting what you want directly, you remove what you do not want.

That is the key idea:

sometimes filtering means excluding noise, not selecting matches

Approach

We solve this in 3 simple steps:

1. Read required columns

We only need:

• user_id
• name
• email

So select only these columns.

2. Identify the unwanted pattern

We want to exclude emails ending in:

@gmail.com

Since the username before it can be anything, we use:

'%@gmail.com'

Here:

• % = any number of characters before @gmail.com

This matches:

• aarav@gmail.com
• rohan@gmail.com
• abc123@gmail.com

3. Exclude that pattern

We use:

NOT LIKE

This tells SQL:

keep only rows where email does not match this pattern

Query

SELECT
    user_id,
    name,
    email
FROM Users
WHERE email NOT LIKE '%@gmail.com';

Step-by-Step Breakdown

Step 1

SELECT user_id, name, email

Return only the columns required in the output.

Step 2

FROM Users

Read data from the Users table.

Step 3

WHERE email NOT LIKE '%@gmail.com'

Filter rows where email does not end with @gmail.com.

This removes all Gmail users.

Why LIKE Works Here

We are not checking exact equality.

This would be wrong:

WHERE email != '@gmail.com'

Why?

Because emails are full strings like:

• aarav@gmail.com
• rohan@gmail.com

They are not exactly '@gmail.com'.

We only care about the ending pattern.

That is why LIKE is needed.

Why NOT LIKE is the Correct Choice

• LIKE → keeps matching pattern
• NOT LIKE → removes matching pattern

Since we want non-Gmail users, NOT LIKE is the correct operator.

Output Logic

This query keeps:

• Yahoo
• Outlook
• Hotmail
• Company emails
• any non-Gmail provider

And removes:

• all Gmail users

Common Mistakes to Avoid

1. Using != '@gmail.com'

Wrong:

WHERE email != '@gmail.com'

This checks full equality, not ending pattern.

It will fail logically.

2. Forgetting %

Wrong:

WHERE email NOT LIKE '@gmail.com'

This checks exact text only.

It will not match:

• aarav@gmail.com
• rohan@gmail.com

Because those contain extra characters before @gmail.com.

3. Using LIKE instead of NOT LIKE

Wrong:

WHERE email LIKE '%@gmail.com'

This returns only Gmail users, which is the opposite of what we want.

Key Concept Learned

This problem teaches one of the most important SQL filtering ideas:

Sometimes SQL is not about selecting what matches.

Sometimes SQL is about removing what you do not want.

That is the real use of NOT LIKE.

Use it when you want to:

• exclude test users
• exclude internal emails
• exclude fake accounts
• exclude specific domains
• remove noisy patterns from reports

Final Answer

SELECT
    user_id,
    name,
    email
FROM Users
WHERE email NOT LIKE '%@gmail.com';

Problem 2

Filter Orders by Excluding Unwanted Status

Problem Statement

An e-commerce platform tracks customer orders.

Some orders are marked as:

• Cancelled
• Returned

These orders are not useful for business analysis because they do not represent successful or active customer purchases.

The business team wants to analyze only valid orders.

You are given a table named Orders.

Table: Orders

• order_id is the primary key.
• Each row represents one order.
• status can contain values such as:
  • Completed
  • Cancelled
  • Returned
  • Pending
  • Shipped
  • Processing

Your task is to return:

• order_id
• customer_id
• status

…for all orders whose status is not:

• Cancelled
• Returned

The result can be returned in any order.

Example

Input

Output

CREATE TABLE Orders (order_id INTEGER, customer_id INTEGER, status TEXT);
INSERT INTO Orders VALUES
  (1, 101, 'Completed'),
  (2, 102, 'Cancelled'),
  (3, 103, 'Returned'),
  (4, 104, 'Pending');

Explanation

We must exclude orders whose status is:

• Cancelled
• Returned

Let us evaluate each row:

• Order 1 → Completed → valid → include
• Order 2 → Cancelled → exclude
• Order 3 → Returned → exclude
• Order 4 → Pending → valid → include

So only orders 1 and 4 remain.

Intuition

This problem is about removing unwanted categories.

In business reports, we often do not ask:

What do I want exactly?

Instead, we ask:

What should be removed first?

Here the business already knows which orders are useless for analysis:

• cancelled orders never completed
• returned orders were reversed

So instead of listing every valid status (Completed, Pending, Shipped, Processing…), it is much simpler to say:

remove the bad statuses, keep everything else

That is exactly what NOT IN is built for.

Mental Model

Imagine the business team wants to calculate:

• valid revenue
• successful orders
• active order volume

Would cancelled and returned orders help?

No.

Those orders are noise for this report.

So the smartest way is:

• identify the bad statuses
• remove them
• keep the rest

That is the core idea behind NOT IN.

Approach

We solve this in 3 simple steps.

1. Select required columns

We only need:

• order_id
• customer_id
• status

So return only those columns.

2. Identify unwanted values

The problem clearly tells us to exclude:

• Cancelled
• Returned

These are unwanted statuses.

3. Remove them using NOT IN

We use:

NOT IN

This tells SQL:

keep rows where status is not part of this list

Query

SELECT
    order_id,
    customer_id,
    status
FROM Orders
WHERE status NOT IN ('Cancelled', 'Returned');

Step-by-Step Breakdown

Step 1

SELECT order_id, customer_id, status

Return only the columns required in the final output.

Step 2

FROM Orders

Read data from the Orders table.

Step 3

WHERE status NOT IN ('Cancelled', 'Returned')

Remove rows where status is either:

• Cancelled
• Returned

Keep everything else.

Why NOT IN is Perfect Here

Without NOT IN, you would write:

WHERE status != 'Cancelled'
  AND status != 'Returned'

This works.

But it becomes harder to read and harder to maintain.

Now imagine excluding 6 statuses.

That becomes messy very quickly.

NOT IN is cleaner, shorter, and easier to scale.

Compare

Less readable

WHERE status != 'Cancelled'
  AND status != 'Returned'

Better

WHERE status NOT IN ('Cancelled', 'Returned')

Same logic, cleaner query.

Why This Matches Real Business Logic

In real reporting, we often exclude:

• failed transactions
• refunded payments
• cancelled subscriptions
• test records
• invalid statuses

This is a very common SQL filtering pattern.

The logic is:

remove invalid states, analyze only useful ones

That is why NOT IN is one of the most practical SQL operators in real systems.

Common Mistakes to Avoid

1. Using IN instead of NOT IN

Wrong:

WHERE status IN ('Cancelled', 'Returned')

This returns only invalid orders, the exact opposite of what we want.

2. Forgetting quotes around text

Wrong:

WHERE status NOT IN (Cancelled, Returned)

Cancelled and Returned are text values, so they must be in quotes.

Correct:

WHERE status NOT IN ('Cancelled', 'Returned')

3. Using multiple != checks when list grows

This works for small cases, but becomes ugly fast.

Prefer NOT IN for readability.

4. NULL inside NOT IN

Be careful in real systems.

If your list becomes:

NOT IN ('Cancelled', 'Returned', NULL)

many databases may return unexpected results.

Avoid NULL inside NOT IN.

Key Concept Learned

This problem teaches one of the most practical SQL ideas:

When you already know the bad values, remove them first.

Instead of listing every valid option, just exclude invalid ones.

That is the real power of NOT IN.

Use it when you want to remove:

• cancelled orders
• failed payments
• blocked users
• invalid records
• noisy categories

Final Answer

SELECT
    order_id,
    customer_id,
    status
FROM Orders
WHERE status NOT IN ('Cancelled', 'Returned');

Problem 3

Find Salaries Outside the Expected Range

Problem Statement

A company stores employee salary information to monitor compensation fairness.

The HR team wants to identify employees whose salary falls outside the acceptable salary range.

This helps detect:

• underpaid employees
• overpaid employees
• compensation outliers
• payroll anomalies

You are given a table named Employees.

Table: Employees

• emp_id is the primary key.
• Each row represents one employee and their salary.

Your task is to return:

• emp_id
• name
• salary

…for all employees whose salary is not between 30,000 and 80,000 (inclusive).

The result can be returned in any order.

Example

Input

Output

CREATE TABLE Employees (emp_id INTEGER, name TEXT, salary INTEGER);
INSERT INTO Employees VALUES
  (1, 'Aarav', 25000),
  (2, 'Neha', 30000),
  (3, 'Rohan', 50000),
  (4, 'Meera', 85000);

Explanation

The valid salary range is:

30000 to 80000

This range is inclusive, meaning:

• 30000 is valid
• 80000 is valid

Now evaluate each employee:

• Aarav → 25000 → below range → include
• Neha → 30000 → inside range → exclude
• Rohan → 50000 → inside range → exclude
• Meera → 85000 → above range → include

So only Aarav and Meera are returned.

Intuition

This problem is about finding salary outliers.

The HR team already knows what the acceptable salary band is:

• minimum acceptable = 30000
• maximum acceptable = 80000

So instead of asking:

who is underpaid?

who is overpaid?

We can combine both into one simple idea:

show anyone outside the acceptable range

That is exactly what NOT BETWEEN is built for.

Mental Model

Think of salary review like a safe zone.

Employees whose salary falls inside the safe zone are normal.

Employees outside the safe zone need attention.

So the salary band looks like this:

< 30000   → too low
30000–80000 → acceptable
> 80000   → too high

The HR team wants only the risky edges:

• too low
• too high

That is why NOT BETWEEN is the perfect fit.

Approach

We solve this in 3 simple steps.

1. Select required columns

We only need:

• emp_id
• name
• salary

2. Define the valid salary range

The acceptable range is:

30000 to 80000

This includes both boundaries.

That means:

• 30000 is valid
• 80000 is valid

3. Return employees outside the range

Use:

NOT BETWEEN

This tells SQL:

return rows where salary is below 30000 or above 80000

Query

SELECT
    emp_id,
    name,
    salary
FROM Employees
WHERE salary NOT BETWEEN 30000 AND 80000;

Step-by-Step Breakdown

Step 1

SELECT emp_id, name, salary

Return only the columns required in the output.

Step 2

FROM Employees

Read data from the Employees table.

Step 3

WHERE salary NOT BETWEEN 30000 AND 80000

Keep only employees whose salary is:

• less than 30000
• greater than 80000

Exclude salaries inside the valid range.

Why NOT BETWEEN is Perfect Here

Without NOT BETWEEN, we would write:

WHERE salary < 30000 OR salary > 80000

This is correct.

But NOT BETWEEN is cleaner and easier to read.

Compare

Manual version

WHERE salary < 30000 OR salary > 80000

Cleaner version

WHERE salary NOT BETWEEN 30000 AND 80000

Both mean the same thing.

But NOT BETWEEN is shorter and much easier to understand.

Why This Matches Real Business Logic

In real companies, salary bands are very common.

HR teams often define:

• junior salary band
• mid-level salary band
• senior salary band

Then they look for employees outside expected ranges to detect:

• compensation inequality
• payroll mistakes
• extreme salaries
• policy violations

This is a very common real-world SQL use case.

Common Mistakes to Avoid

1. Forgetting NOT

Wrong:

WHERE salary BETWEEN 30000 AND 80000

This returns employees inside the valid range, the opposite of what we want.

2. Forgetting BETWEEN is inclusive

This is very important.

BETWEEN 30000 AND 80000

includes:

• 30000
• 80000

So Neha (30000) is valid and must be excluded.

3. Reversing the range

Wrong:

WHERE salary NOT BETWEEN 80000 AND 30000

The lower number must always come first.

Correct:

WHERE salary NOT BETWEEN 30000 AND 80000

4. Using AND instead of OR manually

Wrong:

WHERE salary < 30000 AND salary > 80000

This is impossible.

A salary cannot be both lower than 30000 and higher than 80000 at the same time.

Key Concept Learned

This problem teaches one of the most practical SQL ideas:

Use NOT BETWEEN when you want to find outliers outside a valid range.

It is the cleanest way to detect:

• salary outliers
• price outliers
• abnormal scores
• suspicious values
• business exceptions

Final Answer

SELECT
    emp_id,
    name,
    salary
FROM Employees
WHERE salary NOT BETWEEN 30000 AND 80000;

Problem 4

Non-Referred Customers

Problem Statement

A company runs a customer referral program where existing customers can invite new customers to join the platform.

Each customer record stores a referee_id, which tells us who referred that customer.

The company maintains a table named Customer.

Table: Customer

• id is the primary key.
• name is the customer name.
• referee_id stores the ID of the customer who referred them.
• referee_id can be NULL, which means the customer joined without any referral.

Your task is to return the names of customers who were NOT referred by customer with id = 2.

The result can be returned in any order.

Example

Input

CREATE TABLE Customer (id INTEGER, name TEXT, referee_id INTEGER);
INSERT INTO Customer VALUES
  (1, 'Alice', NULL),
  (2, 'Daniel', NULL),
  (3, 'James', 2),
  (4, 'Sarah', NULL),
  (5, 'Kevin', 1),
  (6, 'Robert', 2);

Output

name

Alice

Daniel

Sarah

Kevin

Explanation

We need customers who were not referred by customer 2.

Let us check row by row:

• Alice → referee_id = NULL → not referred by 2 → include
• Daniel → referee_id = NULL → not referred by 2 → include
• James → referee_id = 2 → referred by 2 → exclude
• Sarah → referee_id = NULL → not referred by 2 → include
• Kevin → referee_id = 1 → referred by 1, not 2 → include
• Robert → referee_id = 2 → referred by 2 → exclude

So the final answer is:

• Alice
• Daniel
• Sarah
• Kevin

Intuition

This problem looks simple at first.

We only need:

customers whose referee_id is not 2

So many beginners write:

SELECT name
FROM Customer
WHERE referee_id <> 2;

This looks correct.

But it gives the wrong answer.

Why?

Because SQL has a special concept called NULL, and NULL behaves differently from normal values.

That is the real trick in this problem.

Why the Basic Query Fails

At first glance, this looks correct:

SELECT name
FROM Customer
WHERE referee_id <> 2;

You may expect this to return:

• Alice
• Daniel
• Sarah
• Kevin

But it actually returns only:

• Kevin

Why?

Because of NULL.

The Real Problem: NULL Logic

In SQL, NULL does not mean zero.

It does not mean empty.

It means:

unknown / missing / not recorded

That changes everything.

Important Rule

Any comparison with NULL does not return TRUE or FALSE.

It returns:

UNKNOWN

That means:

NULL = 2      → UNKNOWN
NULL != 2     → UNKNOWN
NULL <> 2     → UNKNOWN

And SQL only keeps rows where the condition is TRUE.

It discards:

• FALSE
• UNKNOWN

That is why rows with NULL disappear.

SQL’s Hidden Rule (Very Important)

In a WHERE clause, SQL keeps only rows where the condition evaluates to:

TRUE

It removes rows where the condition becomes:

• FALSE
• UNKNOWN

This is why NULL rows are silently skipped.

That is the entire trick in this problem.

Mental Model

Think of SQL filtering like a security gate.

Each row is checked before entering the final result.

• TRUE → allowed in
• FALSE → blocked
• UNKNOWN → also blocked

Now test each customer:

That is why only Kevin appears in the wrong query.

Approach

To solve this correctly, we need two valid groups:

Group 1

Customers whose referee_id is not 2

Example:

• Kevin → referee_id = 1

Group 2

Customers whose referee_id is NULL

Example:

• Alice
• Daniel
• Sarah

These customers were not referred by anyone, so they were definitely not referred by customer 2.

So we must include them too.

Correct Approach

We combine both conditions:

1. referee_id != 2
2. referee_id IS NULL

That gives us the full correct answer.

Query

SELECT name
FROM Customer
WHERE referee_id != 2
   OR referee_id IS NULL;

Step-by-Step Breakdown

Step 1

SELECT name

Return only the name column.

That is the required output.

Step 2

FROM Customer

Read data from the Customer table.

Step 3

WHERE referee_id != 2

Keep customers whose referee is not customer 2.

This includes:

• Kevin

But this still misses NULL rows.

Step 4

OR referee_id IS NULL

Also include customers who were never referred.

This adds:

• Alice
• Daniel
• Sarah

Now the answer is complete.

Why IS NULL Is Mandatory

This is the most important lesson in this problem.

Never compare NULL using:

• =
• !=
• <>

These do not work for NULL.

Wrong:

referee_id = NULL

Wrong:

referee_id != NULL

Both return UNKNOWN.

Correct Way

Always use:

IS NULL

or

IS NOT NULL

Common Mistake

Many beginners write this:

SELECT name
FROM Customer
WHERE referee_id = NULL OR referee_id <> 2;

This is wrong.

Why?

Because:

referee_id = NULL

is never TRUE.

It becomes UNKNOWN.

So SQL ignores those rows.

Correct vs Wrong

Wrong

SELECT name
FROM Customer
WHERE referee_id <> 2;

Misses NULL rows.

Wrong

SELECT name
FROM Customer
WHERE referee_id = NULL OR referee_id <> 2;

Still wrong because = NULL does not work.

Correct

SELECT name
FROM Customer
WHERE referee_id != 2
   OR referee_id IS NULL;

This handles both normal values and NULL safely.

Key Concept Learned

This problem teaches one of the most important SQL rules:

NULL is not a normal value.

Because of that:

• NULL = something does not work
• NULL != something does not work
• always use IS NULL / IS NOT NULL

This is one of the most common SQL interview traps.

Final Answer

SELECT name
FROM Customer
WHERE referee_id != 2
   OR referee_id IS NULL;