SQL CASE WHEN: The Complete Guide With Real Interview Examples

CASE WHEN is the closest thing SQL has to an if-else statement — and it shows up in more interview questions than almost any other concept.

Most candidates know the basic syntax. Fewer know how to use it inside aggregations, inside ORDER BY, or to pivot rows into columns. This guide covers all of it.

1. The two forms of CASE

Simple CASE — compares one column against fixed values:

sql
SELECT
  order_id,
  CASE status
    WHEN 'pending'  THEN 'Not started'
    WHEN 'shipped'  THEN 'In transit'
    WHEN 'delivered' THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Searched CASE — evaluates arbitrary conditions (more powerful, use this by default):

sql
SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 1000 THEN 'High value'
    WHEN amount >= 500  THEN 'Mid value'
    ELSE 'Low value'
  END AS order_tier
FROM orders;

Rule: CASE evaluates conditions top to bottom and returns the first match. Order matters — put the most specific conditions first.

2. CASE inside SELECT — the most common use

"Classify customers by their signup year."

sql
SELECT
  customer_id,
  name,
  CASE
    WHEN EXTRACT(YEAR FROM signup_date) < 2022 THEN 'Early adopter'
    WHEN EXTRACT(YEAR FROM signup_date) = 2022 THEN '2022 cohort'
    WHEN EXTRACT(YEAR FROM signup_date) = 2023 THEN '2023 cohort'
    ELSE 'Recent'
  END AS customer_segment
FROM customers;

3. CASE inside aggregate functions — conditional aggregation

This is the pattern that separates intermediate from advanced SQL writers.

"For each country, count how many customers are active vs inactive."

Without CASE, you would need two separate queries. With CASE:

sql
SELECT
  country,
  COUNT(CASE WHEN status = 'active'   THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
  COUNT(*) AS total
FROM customers
GROUP BY country
ORDER BY total DESC;

Why COUNT(CASE WHEN ... THEN 1 END) works:

• When the condition is true → returns 1 → COUNT counts it
• When the condition is false → returns NULL → COUNT ignores it

You can also use SUM:

sql
SELECT
  category,
  SUM(CASE WHEN returned = TRUE THEN amount ELSE 0 END) AS returned_revenue,
  SUM(amount) AS total_revenue
FROM orders
JOIN products USING (product_id)
GROUP BY category;

Interview tip: whenever you see "how many X vs Y per group", reach for conditional aggregation. It is cleaner than multiple subqueries or self joins.

4. CASE to pivot rows into columns

"Show monthly revenue for Q1 2024 as three separate columns."

sql
SELECT
  product_id,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN amount ELSE 0 END) AS mar_revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY product_id;

This is SQL pivoting — turning row values into column headers using CASE inside SUM.

5. CASE inside WHERE — be careful here

You cannot use CASE directly in WHERE the way most people try. This does not work:

sql
-- This FAILS:
WHERE CASE WHEN type = 'premium' THEN amount > 100 END

Instead, restructure the logic:

sql
WHERE (type = 'premium' AND amount > 100)
   OR (type != 'premium' AND amount > 0)

Or use CASE to return a value that you then compare:

sql
WHERE CASE WHEN type = 'premium' THEN amount ELSE 0 END > 100

6. CASE inside ORDER BY — dynamic sorting

"Sort VIP customers first, then by amount descending."

sql
SELECT customer_id, name, tier, amount
FROM customers
ORDER BY
  CASE tier
    WHEN 'VIP'      THEN 1
    WHEN 'Premium'  THEN 2
    WHEN 'Standard' THEN 3
    ELSE 4
  END ASC,
  amount DESC;

This lets you define custom sort priorities that a plain ORDER BY tier would not give you.

7. CASE vs COALESCE vs NULLIF

These three are often confused:

COALESCE is a shortcut for a common CASE pattern:

sql
-- These are equivalent:
COALESCE(phone, 'No phone')

CASE WHEN phone IS NOT NULL THEN phone ELSE 'No phone' END

Use COALESCE when you can — it is cleaner. Use CASE when you need true conditional logic.

8. Common interview patterns with CASE

Pattern 1: Assign bonus based on multiple conditions

sql
SELECT
  employee_id,
  salary,
  CASE
    WHEN performance_score >= 90 AND years_tenure >= 5 THEN salary * 0.20
    WHEN performance_score >= 90 THEN salary * 0.15
    WHEN performance_score >= 75 THEN salary * 0.10
    ELSE salary * 0.05
  END AS bonus_amount
FROM employees;

Pattern 2: Flag outliers or anomalies

sql
SELECT
  transaction_id,
  amount,
  CASE
    WHEN amount > AVG(amount) OVER () * 3 THEN 'Suspicious'
    WHEN amount > AVG(amount) OVER () * 2 THEN 'High'
    ELSE 'Normal'
  END AS flag
FROM transactions;

Pattern 3: Recode values for reporting

sql
SELECT
  CASE
    WHEN age < 25 THEN '18-24'
    WHEN age < 35 THEN '25-34'
    WHEN age < 45 THEN '35-44'
    ELSE '45+'
  END AS age_bucket,
  COUNT(*) AS users
FROM users
GROUP BY 1
ORDER BY 1;

9. Mistakes to avoid

Mistake 1: Forgetting ELSE

Without an ELSE clause, CASE returns NULL when no condition matches. This silently produces NULLs in your output. Always add ELSE unless you explicitly want NULLs.

Mistake 2: Wrong condition order

sql
-- Bug: the first condition matches everything >= 500
-- so the second condition never runs
CASE
  WHEN amount >= 500  THEN 'Mid or High'
  WHEN amount >= 1000 THEN 'High'  -- never reached
  ELSE 'Low'
END

Fix: put the most specific (highest threshold) first.

Mistake 3: Using CASE when COALESCE is simpler

sql
-- Verbose:
CASE WHEN email IS NOT NULL THEN email ELSE '[email protected]' END

-- Clean:
COALESCE(email, '[email protected]')

10. Practice CASE WHEN problems on TheQueryLab

The best way to get comfortable with CASE is to use it in problems where the business logic requires conditional branching — not just simple filters.

On TheQueryLab, problems like special bonus calculations, cohort assignments, and revenue classification all require CASE in their solutions. Start with the SQL Interview Preparation Kit and look for problems tagged with conditional logic.

Once CASE feels natural, you will find yourself reaching for it instinctively whenever an interview question involves "classify", "categorise", "flag", or "assign".