How to Write a Case Statement in Snowflake

A CASE statement in SQL is used to return a value based on conditional logic. It's particularly useful in scenarios where you need to perform transformations or aggregations based on specific criteria. Snowflake, like most SQL-based databases, supports the use of the CASE statement to simplify queries and improve readability.

Understanding the Syntax

The basic syntax for a CASE statement in Snowflake looks like this:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

This allows you to evaluate different conditions and return corresponding results. If none of the conditions are met, the ELSE clause provides a default result.

Examples of CASE Statement Usage

Here’s an example where we categorize sales amounts into different ranges:

SELECT
  sale_amount,
  CASE
    WHEN sale_amount >= 1000 THEN 'High'
    WHEN sale_amount >= 500 THEN 'Medium'
    ELSE 'Low'
  END AS sale_category
FROM sales;

In this example, we’re evaluating the `sale_amount` and categorizing it as 'High', 'Medium', or 'Low'.

CASE with Aggregations

You can also use CASE statements within aggregations. For example, you might want to count the number of high, medium, and low sales:

SELECT
  COUNT(CASE WHEN sale_amount >= 1000 THEN 1 END) AS high_sales,
  COUNT(CASE WHEN sale_amount >= 500 AND sale_amount < 1000 THEN 1 END) AS medium_sales,
  COUNT(CASE WHEN sale_amount < 500 THEN 1 END) AS low_sales
FROM sales;

Using CASE with Joins

Another powerful use of the CASE statement is within joins. Suppose you want to join tables but apply conditional logic based on certain criteria:

SELECT
  orders.order_id,
  customers.customer_name,
  CASE
    WHEN orders.order_total > 500 THEN 'VIP Customer'
    ELSE 'Regular Customer'
  END AS customer_type
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

This query classifies customers based on their order total and joins them with customer data to provide a more personalized result.

Conclusion

The CASE statement is a versatile tool in Snowflake that allows you to introduce conditional logic into your queries, making them more flexible and dynamic. Whether you're categorizing data, using it in aggregations, or applying it in joins, the CASE statement is an essential part of writing efficient and readable SQL queries.