SQL CASE WHEN: Complete Guide with Examples

Why CASE WHEN Matters in Interviews

CASE WHEN is SQL's version of if-else logic. It lets you create conditional columns, categorize data, and handle complex business rules — all within a single query. Interviewers love CASE WHEN questions because they test your ability to think logically and write clean, readable SQL.

You will encounter CASE WHEN in almost every data science SQL interview, whether at startups or companies like Google and Meta. Master it, and you unlock a huge range of problems.

Simple CASE vs Searched CASE

SQL provides two forms of the CASE expression. Both are useful, but searched CASE is far more common in interviews.

Simple CASE

Simple CASE compares one expression against multiple values:

SELECT
    order_id,
    CASE status
        WHEN 'shipped' THEN 'Complete'
        WHEN 'pending' THEN 'In Progress'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS order_status
FROM orders;

This is clean and readable when you are matching exact values against a single column.

Searched CASE

Searched CASE evaluates arbitrary boolean expressions:

SELECT
    employee_id,
    salary,
    CASE
        WHEN salary >= 150000 THEN 'Senior'
        WHEN salary >= 100000 THEN 'Mid-Level'
        WHEN salary >= 60000 THEN 'Junior'
        ELSE 'Entry'
    END AS salary_band
FROM employees;

Searched CASE is more flexible because each WHEN clause can use different columns, comparisons, and operators. This is the form you will use 90% of the time.

Using CASE WHEN in SELECT

The most common use is creating derived columns. Here is a practical example for categorizing users:

SELECT
    user_id,
    total_purchases,
    CASE
        WHEN total_purchases >= 100 THEN 'Power User'
        WHEN total_purchases >= 20 THEN 'Regular'
        WHEN total_purchases >= 1 THEN 'Occasional'
        ELSE 'Inactive'
    END AS user_segment
FROM user_stats;

Conditional Aggregation

One of the most powerful interview patterns combines CASE with aggregate functions:

SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
    ROUND(
        100.0 * COUNT(CASE WHEN gender = 'F' THEN 1 END) / COUNT(*),
        1
    ) AS female_pct
FROM employees
GROUP BY department;

This pivot-style query is extremely common in interviews. It avoids multiple self-joins and keeps everything in a single pass over the data.

Using CASE WHEN in WHERE and HAVING

While less common, you can use CASE in WHERE clauses for conditional filtering:

SELECT *
FROM orders
WHERE
    CASE
        WHEN order_type = 'subscription' THEN amount > 50
        WHEN order_type = 'one-time' THEN amount > 100
        ELSE TRUE
    END;

More practically, you will use CASE inside HAVING to filter groups conditionally:

SELECT
    product_category,
    AVG(price) AS avg_price
FROM products
GROUP BY product_category
HAVING
    CASE
        WHEN product_category = 'Electronics' THEN AVG(price) > 500
        ELSE AVG(price) > 50
    END;

Using CASE WHEN in ORDER BY

CASE in ORDER BY lets you implement custom sort orders:

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;

This is useful when the natural alphabetical or numeric order does not match the business logic.

Common Interview Patterns

Pattern 1: Flagging Records

SELECT
    transaction_id,
    amount,
    CASE
        WHEN amount > 10000 THEN 'Flagged'
        ELSE 'Normal'
    END AS review_status
FROM transactions;

Pattern 2: Pivoting Rows to Columns

SELECT
    date,
    SUM(CASE WHEN platform = 'iOS' THEN revenue ELSE 0 END) AS ios_revenue,
    SUM(CASE WHEN platform = 'Android' THEN revenue ELSE 0 END) AS android_revenue,
    SUM(CASE WHEN platform = 'Web' THEN revenue ELSE 0 END) AS web_revenue
FROM daily_revenue
GROUP BY date;

Pattern 3: NULL Handling

SELECT
    customer_id,
    CASE
        WHEN email IS NOT NULL AND phone IS NOT NULL THEN 'Full Contact'
        WHEN email IS NOT NULL THEN 'Email Only'
        WHEN phone IS NOT NULL THEN 'Phone Only'
        ELSE 'No Contact Info'
    END AS contact_status
FROM customers;

Important Details

Evaluation Order

CASE WHEN evaluates conditions top to bottom and returns the first match. Order your conditions from most specific to least specific:

-- Correct: specific conditions first
CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
END

If you reversed the order, every score >= 70 would match the first condition and you would never reach the higher grades.

ELSE Is Optional but Recommended

If no WHEN condition matches and there is no ELSE, the result is NULL. Always include ELSE to make your intent explicit and avoid unexpected NULLs.

CASE Returns a Value

CASE is an expression, not a statement. It returns a single value. This means you can use it anywhere a value is valid: SELECT, WHERE, ORDER BY, JOIN conditions, and even inside function arguments.

Practice Problems

To master CASE WHEN, practice problems that require conditional aggregation and data categorization. You can find relevant problems on the CASE WHEN practice page.

Key Takeaways

CASE WHEN is one of the most versatile tools in SQL. Learn the searched CASE syntax, master conditional aggregation, and practice pivoting data. These patterns appear in nearly every SQL interview and will serve you well in your day-to-day work as a data scientist.