Context

Teams often need to categorize rows, clean outputs, or build conditional metrics directly in SQL. CASE WHEN is one of the most common tools for adding business logic to a query without changing the underlying data.

Core question

Explain how you would use CASE WHEN in a SQL query. In your answer, cover:

1. What CASE WHEN does
2. The difference between using it in SELECT, WHERE, and ORDER BY
3. How it can be used for conditional aggregation
4. How NULL values interact with CASE WHEN

Scope guidance

The interviewer expects a practical explanation with simple PostgreSQL examples. You should describe the syntax, common use cases, and a few mistakes to avoid, rather than giving a highly theoretical answer.

Row-level conditional logic

CASE WHEN lets you return different values based on conditions, similar to if/else logic in programming. It is commonly used to create derived columns such as status labels, buckets, or flags.

SELECT order_id,
       amount,
       CASE
         WHEN amount >= 100 THEN 'high_value'
         ELSE 'standard'
       END AS order_type
FROM orders;

Conditional aggregation

CASE WHEN is often placed inside aggregate functions like SUM or COUNT to calculate metrics for specific subsets of data in a single query. This is a standard pattern for reporting and dashboard queries.

SELECT
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

Evaluation order and placement

CASE WHEN can appear in different parts of a query, but its role changes depending on where it is used. In SELECT, it transforms output values; in ORDER BY, it customizes sorting; in WHERE, it is usually less readable than direct boolean conditions.

SELECT customer_name, status
FROM orders
ORDER BY CASE
           WHEN status = 'priority' THEN 1
           WHEN status = 'standard' THEN 2
           ELSE 3
         END;

NULL handling

CASE WHEN does not treat NULL as equal to anything, including another NULL. You must explicitly check IS NULL or IS NOT NULL when you want to branch on missing values.

SELECT order_id,
       CASE
         WHEN shipped_at IS NULL THEN 'not_shipped'
         ELSE 'shipped'
       END AS shipping_status
FROM orders;