Advanced SQL: CASE & CTEs for Business Logic

Implementing Business Logic with CASE Statements

Think of a CASE statement as a way to apply business rules to your data. It evaluates a list of conditions and returns a value when the first condition is met. Its structure is CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END.

Let's start with a video that clearly explains the syntax and a few practical use cases.

As you saw, CASE statements are incredibly versatile. You can use them to:

1. Create Categories: Group continuous data, like age or sales value, into discrete buckets (e.g., 'Young', 'Old'; 'Small Order', 'Large Order').
2. Apply Conditional Logic: Implement rules like the salary raise example. You might encounter rules like "if a customer is from the USA and has spent over $500, flag them for a premium offer."
3. Perform Conditional Aggregation: This is a very common and powerful pattern in BI. Instead of filtering rows with WHERE, you can use CASE inside an aggregate function like SUM() or COUNT() to "pivot" data from rows into columns.

For a great example of this, let's look at a real-world SQL challenge.

This technique lets you calculate percentages for different segments (in this case, 'Retail' and 'Shopify') across a grouped category (in this case, month_number) all within a single query.

SELECT
  order_id,
  CASE
    WHEN order_value > 1000 THEN 'High'
    WHEN order_value >= 500 AND order_value <= 1000 THEN 'Medium'
    ELSE 'Low'
  END AS order_priority
FROM
  orders;

Structuring Queries with Common Table Expressions (CTEs)

As you saw in the last lesson, queries that join multiple tables can get long and difficult to follow. Common Table Expressions (CTEs) are the solution. A CTE, defined using a WITH clause, creates a temporary, named result set that you can then reference in your main query.

Think of it as creating a temporary view that exists only for the duration of your query. This makes your code more readable, modular, and easier to debug.

This image shows a query rewritten with CTEs. Notice how the logic is broken into clean, understandable blocks.

Now, let's watch a video that explains the concept and demonstrates how to refactor a messy query with subqueries into a clean one using CTEs.

The key takeaway is that CTEs allow you to build your analysis step-by-step. You can have one CTE that joins tables, a second CTE that aggregates data from the first one, and a final SELECT statement that performs a final calculation or filtering.

Combining CTEs and CASE for Advanced Analysis

The true power of these tools becomes apparent when you combine them. A common workflow for a data analyst is:

1. Use one or more CTEs to join, filter, and pre-process your raw data into a clean, intermediate table.
2. In the final SELECT statement, use CASE statements on the results from your CTE to apply final business logic, create categories, or perform conditional calculations.

Let's look at two practical case studies that use this exact pattern.

Example 1: Danny's Diner Points Calculation

This case study analyzes customer behavior at a small restaurant. In one of the questions, the analyst needs to calculate loyalty points based on a set of rules.

Example 2: Before-and-After Analysis

A very common business request is to analyze the impact of a change—for instance, the launch of a new product feature or a marketing campaign. This requires comparing performance metrics before the change to the metrics after the change. CTEs and CASE statements are perfect for this.

The "Data Mart" case study we looked at earlier has a fantastic section on this.

This pattern is extremely powerful and one you will use frequently as a data analyst.

WITH customer_order_counts AS (
  -- This CTE calculates the total number of orders for each customer
  SELECT
    customer_id,
    COUNT(order_id) AS total_orders
  FROM
    orders
  GROUP BY
    customer_id
)
-- The main query joins the CTE with the customers table to get the names
-- and then filters for customers who meet the 'High-Frequency' criteria.
SELECT
  c.customer_id,
  c.name
FROM
  customers c
JOIN
  customer_order_counts coc ON c.customer_id = coc.customer_id
WHERE
  coc.total_orders > 5;

Conclusion

In this lesson, you've added two of the most important SQL tools for data analysis to your toolkit. They enable you to embed business logic directly into your queries and to manage the complexity of real-world analytical tasks.

Key Takeaways:

• CASE Statements: Use CASE WHEN ... THEN ... END to apply conditional logic, create categorical variables, and perform conditional aggregations. They are the SQL equivalent of if-then-else logic.
• Common Table Expressions (CTEs): Use WITH ... AS (...) to break down complex queries into logical, readable steps. They dramatically improve the maintainability and clarity of your code, especially when multiple JOINs and aggregations are involved.
• Combined Power: The most effective analytical queries often use CTEs to prepare and structure the data, followed by CASE statements in the final query to apply business rules and formatting.

Next Lesson Preview:

We've seen how to structure queries and apply conditional logic. However, what if you need to perform calculations that depend on other rows? For example, how would you rank your customers by sales within each country, or calculate a running total of sales over time? These tasks are difficult with standard aggregations.

In our next lesson, we will introduce window functions. These are incredibly powerful functions that allow you to perform calculations across a set of related rows, unlocking a new level of analytical capability right within SQL.