CASE Statement in SQL

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

Simple CASE Syntax

Think of simple CASE as the gentle introduction. You hand it one column, you list the values you care about, and you get back a label for each match. It's the form you'll reach for when you're renaming status codes or rewriting enum strings. You're not doing anything clever here, and that's fine: most production CASE expressions live at this level of complexity, and interviewers respect the candidate who knows when the simple form is enough.

-- Simple CASE: compare one expression to fixed values
SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'   THEN 'Awaiting Processing'
    WHEN 'shipped'   THEN 'In Transit'
    WHEN 'delivered' THEN 'Complete'
    WHEN 'returned'  THEN 'Returned to Warehouse'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Simple CASE only supports equality checks. You cannot write CASE status WHEN LIKE '%active%'. For anything beyond exact value matching, use the searched CASE syntax below.

When to Use Simple CASE

-- Custom sort order using simple CASE
SELECT task_name, priority
FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE 5
  END;

Searched CASE Syntax

Searched CASE evaluates independent boolean expressions. Each WHEN clause can test a different column, use a different operator, or combine multiple conditions with AND/OR. This is the form you will use 90% of the time.

-- Searched CASE: each WHEN is an independent condition
SELECT
  employee_id,
  name,
  salary,
  CASE
    WHEN salary >= 150000 THEN 'Executive'
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000  THEN 'Mid-Level'
    WHEN salary >= 40000  THEN 'Junior'
    ELSE 'Entry'
  END AS salary_band
FROM employees;

Multi-Column Conditions

Searched CASE can reference multiple columns in each WHEN clause. This is useful for classification logic that depends on more than one attribute.

-- Classify customers by both spend and tenure
SELECT
  customer_id,
  total_spend,
  months_active,
  CASE
    WHEN total_spend >= 10000 AND months_active >= 24
      THEN 'VIP'
    WHEN total_spend >= 5000 OR months_active >= 12
      THEN 'Established'
    WHEN months_active >= 3
      THEN 'Growing'
    ELSE 'New'
  END AS customer_tier
FROM customers;

NULL Handling in CASE

CASE does not treat NULL as equal to anything, including another NULL. If you need to check for NULL, use IS NULL explicitly.

-- NULL-safe CASE
SELECT
  order_id,
  shipped_date,
  CASE
    WHEN shipped_date IS NULL THEN 'Not Shipped'
    WHEN shipped_date > CURRENT_DATE - INTERVAL '7 days'
      THEN 'Recently Shipped'
    ELSE 'Shipped'
  END AS shipping_status
FROM orders;

-- WRONG: this never matches NULL
-- CASE shipped_date WHEN NULL THEN 'Not Shipped' END
-- NULL = NULL evaluates to NULL (not TRUE), so it skips

CASE with Aggregation: Conditional Counting and Summing

The most powerful use of CASE in data engineering is inside aggregate functions. This pattern lets you count or sum different subsets of rows in a single pass without needing multiple queries or subqueries. Interviewers test this pattern constantly because it shows you can think about data at the group level.

-- Conditional count: orders by status in one query
SELECT
  DATE_TRUNC('month', order_date) AS order_month,
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
  COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returned,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

This works because COUNT ignores NULLs. When the CASE condition is false and there is no ELSE, the expression returns NULL. COUNT skips it. Only rows matching the condition contribute to the count.

-- Conditional sum: revenue by region in one query
SELECT
  product_category,
  SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north_revenue,
  SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south_revenue,
  SUM(CASE WHEN region = 'East' THEN revenue ELSE 0 END) AS east_revenue,
  SUM(CASE WHEN region = 'West' THEN revenue ELSE 0 END) AS west_revenue,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_category;

CASE with AVG and Ratios

-- Delivery rate by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  ROUND(
    100.0
    * COUNT(CASE WHEN status = 'delivered' THEN 1 END)
    / NULLIF(COUNT(*), 0),
    1
  ) AS delivery_rate_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

NULLIF prevents division by zero. If a month has zero orders, NULLIF(COUNT(*), 0) returns NULL, and the division produces NULL instead of an error.

CASE with Window Functions

CASE works inside window function expressions. This combination lets you compute conditional running totals, conditional rankings, and row-level flags based on partition-level calculations.

-- Running total of delivered revenue only
SELECT
  order_id,
  order_date,
  status,
  revenue,
  SUM(CASE WHEN status = 'delivered' THEN revenue ELSE 0 END)
    OVER (ORDER BY order_date) AS running_delivered_revenue
FROM orders
ORDER BY order_date;

-- Flag rows where an employee's salary is above department average
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  CASE
    WHEN salary > AVG(salary) OVER (PARTITION BY department)
      THEN 'Above Average'
    WHEN salary = AVG(salary) OVER (PARTITION BY department)
      THEN 'At Average'
    ELSE 'Below Average'
  END AS salary_position
FROM employees;

-- Classify change direction from previous row
SELECT
  date,
  metric_value,
  LAG(metric_value) OVER (ORDER BY date) AS prev_value,
  CASE
    WHEN metric_value > LAG(metric_value) OVER (ORDER BY date)
      THEN 'Increase'
    WHEN metric_value < LAG(metric_value) OVER (ORDER BY date)
      THEN 'Decrease'
    WHEN metric_value = LAG(metric_value) OVER (ORDER BY date)
      THEN 'No Change'
    ELSE 'N/A'  -- first row has no previous value
  END AS direction
FROM daily_metrics;

CASE in Data Engineering Pipelines

In production pipelines, CASE is used for data cleaning, standardization, and transformation. Here are the patterns that show up in real dbt models and ETL jobs.

-- Standardize country names from raw data
SELECT
  user_id,
  CASE
    WHEN UPPER(TRIM(country)) IN ('US', 'USA', 'UNITED STATES', 'U.S.A.')
      THEN 'United States'
    WHEN UPPER(TRIM(country)) IN ('UK', 'GB', 'UNITED KINGDOM', 'GREAT BRITAIN')
      THEN 'United Kingdom'
    WHEN UPPER(TRIM(country)) IN ('CA', 'CAN', 'CANADA')
      THEN 'Canada'
    ELSE INITCAP(TRIM(country))
  END AS country_standardized
FROM raw_users;

-- Mark current vs historical records
SELECT
  customer_id,
  name,
  email,
  valid_from,
  valid_to,
  CASE
    WHEN valid_to IS NULL OR valid_to > CURRENT_TIMESTAMP
      THEN TRUE
    ELSE FALSE
  END AS is_current
FROM customer_history;

-- Score row completeness for a data quality dashboard
SELECT
  record_id,
  (
    CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END
    + CASE WHEN phone IS NOT NULL AND phone != '' THEN 1 ELSE 0 END
    + CASE WHEN address IS NOT NULL AND address != '' THEN 1 ELSE 0 END
    + CASE WHEN name IS NOT NULL AND name != '' THEN 1 ELSE 0 END
  ) AS completeness_score,
  CASE
    WHEN (
      CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END
      + CASE WHEN phone IS NOT NULL AND phone != '' THEN 1 ELSE 0 END
      + CASE WHEN address IS NOT NULL AND address != '' THEN 1 ELSE 0 END
      + CASE WHEN name IS NOT NULL AND name != '' THEN 1 ELSE 0 END
    ) = 4 THEN 'Complete'
    WHEN (
      CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END
      + CASE WHEN phone IS NOT NULL AND phone != '' THEN 1 ELSE 0 END
      + CASE WHEN address IS NOT NULL AND address != '' THEN 1 ELSE 0 END
      + CASE WHEN name IS NOT NULL AND name != '' THEN 1 ELSE 0 END
    ) >= 2 THEN 'Partial'
    ELSE 'Poor'
  END AS quality_tier
FROM contacts;

CASE Statement Patterns for Interviews

These are the CASE patterns that come up most in interview SQL questions. Each one solves a specific category of problem.

SELECT
  CASE
    WHEN age >= 65 THEN '65+'
    WHEN age >= 45 THEN '45-64'
    WHEN age >= 25 THEN '25-44'
    WHEN age >= 18 THEN '18-24'
    ELSE 'Under 18'
  END AS age_group,
  COUNT(*) AS user_count
FROM users
GROUP BY 1
ORDER BY MIN(age);

SELECT
  department,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  COUNT(CASE WHEN gender NOT IN ('M', 'F') THEN 1 END) AS other_count,
  COUNT(*) AS total
FROM employees
GROUP BY department;

SELECT
  user_id,
  CASE is_verified
    WHEN TRUE THEN 'Verified'
    WHEN FALSE THEN 'Unverified'
  END AS verification_status,
  CASE has_subscription
    WHEN TRUE THEN 'Subscriber'
    WHEN FALSE THEN 'Free Tier'
  END AS subscription_status
FROM users;

SELECT
  campaign_id,
  impressions,
  clicks,
  CASE
    WHEN impressions = 0 THEN 0
    ELSE ROUND(100.0 * clicks / impressions, 2)
  END AS click_through_rate
FROM ad_campaigns;

Common CASE Statement Mistakes

These mistakes appear in interviews and production code. Knowing them prevents debugging headaches.

-- BUG: salary of 120000 matches first condition
CASE
  WHEN salary > 50000 THEN 'Mid'     -- 120000 > 50000 is TRUE
  WHEN salary > 100000 THEN 'Senior' -- never reached for 120000
END

-- FIX: order from most restrictive to least
CASE
  WHEN salary > 100000 THEN 'Senior'
  WHEN salary > 50000 THEN 'Mid'
  ELSE 'Junior'
END

-- BUG: status 'canceled' returns NULL
SELECT CASE status
  WHEN 'active' THEN 'Active'
  WHEN 'inactive' THEN 'Inactive'
END AS label  -- 'canceled' -> NULL

-- FIX: always handle the default
SELECT CASE status
  WHEN 'active' THEN 'Active'
  WHEN 'inactive' THEN 'Inactive'
  ELSE 'Other'
END AS label

-- BUG in strict-type engines: mixing int and text
CASE WHEN quantity > 0 THEN quantity ELSE 'N/A' END

-- FIX: cast to consistent type
CASE WHEN quantity > 0 THEN CAST(quantity AS TEXT) ELSE 'N/A' END

CASE Statement in SQL FAQ

Related Guides