CASE Statements

Introduction

CASE is SQL's if/else. It returns a value based on conditions. Used for derived columns, conditional aggregates, bucketing and pivoting.

Understanding the topic

Core concepts to understand:

• CASE WHEN cond THEN x WHEN ... ELSE y END.
• Works in SELECT, WHERE, ORDER BY, GROUP BY.
• Conditional aggregates: SUM(CASE WHEN status='paid' THEN amount END).
• Bucket data: ages → 'kid'/'teen'/'adult'.
• Pivot rows to columns by combining CASE + GROUP BY.

Syntax reference

Visual workflow / architecture:

SELECT user_id,
  SUM(CASE WHEN status='paid'    THEN amount END) AS paid_total,
  SUM(CASE WHEN status='refund'  THEN amount END) AS refunded
FROM orders
GROUP BY user_id;

Real-world use

Cohort reports, funnel reports and pivot tables in BI dashboards depend heavily on CASE.

Best practices

• Always include ELSE to make NULL behavior explicit.
• Avoid huge CASE chains — refactor to lookup tables.
• Use CASE in ORDER BY for custom sort orders.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. CASE syntax.
• Q2. Conditional sum example.
• Q3. Pivoting with CASE.
• Q4. CASE in ORDER BY.
• Q5. Does CASE short-circuit?