Using CASE WHEN Statements in Snowflake

What is a CASE WHEN Statement in Snowflake?

The CASE WHEN statement in Snowflake allows you to perform conditional logic directly in SQL queries. It works similarly to IF-ELSE statements found in other programming languages, enabling complex conditional expressions and aggregations.

Basic Syntax for CASE WHEN in Snowflake

CASE
  WHEN condition THEN result
  [WHEN ... THEN ...]
  [ELSE default_result]
END

• condition: Logical condition to evaluate.
• result: Value to return if the condition is true.
• ELSE: Optional value returned if no conditions are true.

CASE WHEN Example

Here’s a simple example to categorize sales:

SELECT
  product,
  sales,
  CASE
    WHEN sales >= 1000 THEN 'High'
    WHEN sales >= 500 THEN 'Medium'
    ELSE 'Low'
  END AS sales_category
FROM products;

CASE WHEN with Multiple Conditions

You can combine multiple conditions with logical operators (AND, OR):

SELECT
  customer_id,
  CASE
    WHEN status = 'Active' AND balance > 0 THEN 'In Good Standing'
    WHEN status = 'Inactive' OR balance <= 0 THEN 'Review Needed'
    ELSE 'Unknown'
  END AS account_status
FROM customers;

Handling NULL Values with CASE WHEN

Use the CASE statement to handle NULL values explicitly:

SELECT
  employee_id,
  CASE
    WHEN department IS NULL THEN 'Unassigned'
    ELSE department
  END AS department_status
FROM employees;

Nested CASE WHEN Statements

For more complex logic, use nested CASE statements:

SELECT
  student_id,
  CASE
    WHEN score IS NULL THEN 'No Score'
    ELSE CASE
      WHEN score >= 90 THEN 'A'
      WHEN score >= 80 THEN 'B'
      WHEN score >= 70 THEN 'C'
      ELSE 'F'
    END
  END AS grade
FROM exams;

Using CASE WHEN with Aggregation (SUM)

Aggregate conditionally using SUM with CASE WHEN:

SELECT
  region,
  SUM(CASE WHEN status = 'Closed' THEN 1 ELSE 0 END) AS closed_count,
  SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END) AS open_count
FROM cases
GROUP BY region;

This provides a count of cases by status per region.

CASE WHEN ELSE

Using ELSE provides a default result:

SELECT
  order_id,
  CASE
    WHEN delivery_date IS NULL THEN 'Pending'
    ELSE 'Delivered'
  END AS delivery_status
FROM orders;

Conditional Aggregation: SUM CASE WHEN

Summing values conditionally:

SELECT
  salesperson,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_revenue,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_revenue
FROM sales
GROUP BY salesperson;

This pivot-like functionality helps create insightful summaries.

Conclusion

• CASE WHEN statements are powerful for conditional logic.
• Support complex conditions, handling of NULLs, nested statements, and conditional aggregations.
• Essential for data transformations, categorization, and reporting.

For more details, refer to Snowflake’s official documentation.