Conditional Logic with CASE

The Anatomy of a CASE Statement

At its core, a CASE statement evaluates a list of conditions and returns a specific value when the first condition is met. It's structured like this:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

Let's break this down with a practical video demonstration.

To complement the video, the following reading breaks down the syntax in detail. It's a great reference for cementing the concepts.

The Importance of Order

One of the most critical rules of a CASE statement is that it is evaluated sequentially and stops at the first true condition. This means the order in which you write your WHEN clauses matters immensely. If a row meets the criteria for multiple WHEN clauses, only the result of the first one it encounters will be returned.

Let's see a practical demonstration of why this is so important.

SELECT
    campaign_name,
    ctr,
    CASE
        WHEN ctr > 0.01 THEN 'Average'
        WHEN ctr > 0.02 THEN 'High'
        ELSE 'Low'
    END AS performance_category
FROM
    campaign_metrics;

CASE
    WHEN ctr > 0.02 THEN 'High'
    WHEN ctr > 0.01 THEN 'Average'
    ELSE 'Low'
END

Real-World Use Cases for Business Analysis

Now let's move beyond basic syntax to see how CASE statements solve real business problems. This is where you'll see its true power for analysis and reporting.

1. Conditional Calculations

You can perform mathematical operations within your THEN clauses. This is perfect for scenarios like applying different commission rates, calculating tiered pricing, or, as you'll see in this example, giving different salary raises based on job roles.

2. Segmenting and Aggregating Data

As a BA, you'll frequently need to segment data into meaningful groups for analysis. CASE statements are the perfect tool for this, especially when combined with aggregate functions like COUNT(), SUM(), and AVG(). You can create categories and then aggregate your data based on those newly created categories.

Extending the CASE: WHERE, ORDER BY, and GROUP BY

While CASE is most commonly used in the SELECT clause to create new columns, it can also be used in other parts of a query to control filtering, sorting, and grouping. This is a more advanced technique, but it's good to be aware of its potential.

• ORDER BY: Allows for custom sorting logic. For example, you could display all 'High Priority' tasks first, regardless of their creation date.
• GROUP BY: Lets you group data by the custom categories you define in your CASE statement. You saw this in Example 8 above.
• WHERE: Enables conditional filtering, where the filter criteria can change based on the data in the row itself.

Conclusion

In this lesson, you've added a crucial tool to your SQL arsenal. The CASE statement bridges the gap between raw data and business-level insights, allowing you to embed conditional logic directly into your queries.

Key Takeaways:

• The CASE statement implements if-then-else logic to create new, derived columns.
• The basic structure is CASE WHEN condition THEN result ... ELSE default_result END.
• The order of WHEN clauses is critical, as the statement returns the result for the first true condition it finds.
• CASE is powerful for segmenting data (e.g., customer tiers, performance categories) and performing conditional calculations (e.g., tiered commissions).
• It can be combined with aggregate functions like COUNT() and SUM() to build insightful summary reports.

As a Business Analyst, you will constantly be asked to categorize, label, and segment data to make it understandable for non-technical stakeholders. The CASE statement is your primary tool for accomplishing this directly within the database.

Preview of the Next Lesson:
We've now covered subqueries for complex filtering and CASE statements for conditional logic. Our queries are becoming more powerful, but they can also become long and difficult to read. In the next lesson, we will learn about Common Table Expressions (CTEs). CTEs allow you to name and organize the logical building blocks of your query (like subqueries), making your code dramatically more readable, maintainable, and easier to debug—all essential skills for a professional analyst.