Classify Orders with CASE WHEN

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.