Combining Filters with AND/OR

Hello! Welcome to your next lesson in learning SQL for product design.

Introduction

In our last lesson, we focused on using the IN operator to filter for a list of values within a single column, like selecting users from a specific list of countries. This is a great way to handle multiple OR conditions efficiently.

However, many product questions are more complex and require you to filter across multiple columns with different criteria. For instance, you might want to find:

• Users who are on a 'premium' plan AND signed up from an iOS device.
• Events that represent a 'purchase_completed' OR a 'subscription_started'.

Today, we'll learn how to build these more layered queries.

Lesson Goal: By the end of this 60-minute lesson, you will be able to combine multiple filter conditions using the AND and OR operators to ask more specific and powerful questions of your data.

1. The AND Operator: Narrowing Your Search

The AND operator is used to filter data where all specified conditions must be true. Think of it as adding layers of criteria that make your search more and more specific. Each AND you add narrows down the result set.

Product Design Scenario:
Imagine you want to analyze the initial experience of new users on your premium tier. You need to find all users who signed up after a specific date AND are on the 'premium' plan.

A query for this would look like:

SELECT user_id, email, signup_date, plan_type
FROM users
WHERE signup_date > '2023-10-01'
  AND plan_type = 'premium';

This query will only return users who satisfy both conditions. A user who signed up after October 1st but is on the 'free' plan will be excluded. Similarly, a 'premium' user who signed up before that date will also be excluded.

Let's watch a quick video that demonstrates the AND operator in action.

2. The OR Operator: Broadening Your Search

The OR operator is used when you want to retrieve rows where at least one of the conditions is true. Unlike AND, which narrows your results, OR typically broadens them because a row only needs to match one of several criteria to be included.

Product Design Scenario:
You're gathering feedback on the checkout process. You want to identify any user who has either encountered a 'payment_error' OR abandoned their cart.

A query for this might be:

SELECT user_id, event_name, event_timestamp
FROM user_events
WHERE event_name = 'payment_error'
   OR event_name = 'cart_abandoned';

This query will return all events that are either a 'payment_error' or a 'cart_abandoned'.

Let's see a demonstration of the OR operator.

3. Combining AND and OR: The Importance of Parentheses

This is where SQL's power truly shines, but it's also where you need to be careful. When you combine AND and OR in the same WHERE clause, SQL has a default order of operations: it evaluates AND conditions before OR conditions. This can lead to unexpected results if you're not explicit about your logic.

The solution is to use parentheses () to group your conditions. Just like in mathematics, conditions inside parentheses are evaluated first.

Product Design Scenario:
You want to analyze the adoption of a new feature among your core mobile user base. Specifically, you need to find all 'new_feature_click' events that came from users on either the 'iOS' or 'Android' platform.

Let's look at the wrong way to write this first:

-- This query is logically incorrect!
SELECT *
FROM user_events
WHERE event_name = 'new_feature_click'
  AND platform = 'iOS'
  OR platform = 'Android';

Because AND is evaluated first, SQL interprets this as:

1. Find all events that are 'new_feature_click' AND from 'iOS'.
2. Then, find all events that are from 'Android' (regardless of the event name).

This is not what you want. You'll get all Android events, not just the feature clicks.

Here is the correct way, using parentheses:

-- This is the correct query
SELECT *
FROM user_events
WHERE event_name = 'new_feature_click'
  AND (platform = 'iOS' OR platform = 'Android');

By grouping (platform = 'iOS' OR platform = 'Android'), you tell SQL to first find all users on either mobile platform, and then filter that group down to only those who performed the 'new_feature_click' event.

This concept is critical, so let's reinforce it with a detailed reading and another video.

To see this in action again, the following video explains the concept of operator precedence very clearly.

4. Practice: Answering Product Questions

Let's use your new skills to answer some typical product design questions. Imagine you have a table called user_activity with the following columns: user_id, event_name, platform ('iOS', 'Android', 'Web'), and event_date.

user_activity table sample:

Write the queries to answer the following questions.

Question 1: Find all activities from user_id 101 that occurred on the 'iOS' platform.

Question 2: You're researching user engagement. Find all activities that are either a 'login' or a 'purchase_completed'.

Question 3: You're analyzing purchase behavior on mobile. Find all 'purchase_completed' events that happened on either the 'iOS' or 'Android' platform.

SELECT *
FROM user_activity
WHERE user_id = 101 AND platform = 'iOS';

SELECT *
FROM user_activity
WHERE event_name = 'login' OR event_name = 'purchase_completed';

SELECT *
FROM user_activity
WHERE event_name = 'purchase_completed' 
  AND (platform = 'iOS' OR platform = 'Android');

Conclusion

Excellent work! You've now learned how to construct much more nuanced and powerful queries. By combining filters with AND and OR, you can move from asking simple questions to performing sophisticated user segmentation and behavioral analysis.

Key Takeaways:

• AND narrows your results: all conditions must be true.
• OR broadens your results: at least one condition must be true.
• When mixing AND and OR, SQL evaluates AND first by default.
• Always use parentheses () to group your conditions and control the order of operations. This makes your queries predictable, readable, and correct.

Next Lesson Preview:

So far, we've been filtering based on exact text matches (e.g., platform = 'iOS'). But what if you want to find all events related to clicking a button, like 'green_button_click', 'red_button_click', and 'submit_button_click'? In our next lesson, we'll explore the LIKE operator, which allows you to filter text using patterns and wildcards, unlocking a new level of flexibility for analyzing event data.