Most candidates think CASE WHEN evaluates all conditions and picks the best match. It doesn't. It walks the WHEN list top to bottom and stops at the first TRUE. That one detail is why half the CASE WHEN answers we see in mock interviews bucket rows into the wrong tier. The interviewer isn't checking whether you know the keyword. They're checking whether you know the order of operations well enough to notice when yours is broken.
The second thing candidates get wrong: NULL in a WHEN clause doesn't return FALSE, it returns UNKNOWN, which also doesn't match. You write WHEN score > 80 thinking you've covered every row and silently lose every NULL to the ELSE. Interviewers love this trap because it looks right, runs without error, and produces wrong numbers on the rows they're actually checking.
What NULL returns
Time to spot wrong order
SQL rounds with CASE
TRUE wins, rest skipped
Source: DataDriven analysis of 1,042 verified data engineering interview rounds.
Forget what you think you know. CASE isn't a pattern match, it isn't a scoring function, and it doesn't care about specificity. It reads the WHEN clauses in the order you wrote them, takes the first one that evaluates TRUE, and walks away. Omit ELSE and a row with no match returns NULL, not zero. Most bugs in production dashboards trace back to one of those two facts.
-- General syntax with multiple conditions
CASE
WHEN condition1 AND condition2 THEN result_a
WHEN condition3 OR condition4 THEN result_b
WHEN condition5 THEN result_c
ELSE default_result
END
-- Searched CASE (most flexible)
SELECT
CASE
WHEN age >= 65 AND income < 30000 THEN 'Senior Discount'
WHEN age < 18 OR student_flag = true THEN 'Student Rate'
ELSE 'Standard Rate'
END AS pricing_tier
FROM customers;
-- Simple CASE (equality only, no AND/OR)
SELECT
CASE status
WHEN 'A' THEN 'Active'
WHEN 'I' THEN 'Inactive'
WHEN 'P' THEN 'Pending'
ELSE 'Unknown'
END AS status_label
FROM accounts;Simple vs Searched: Simple CASE (CASE column WHEN value) only supports equality checks. Searched CASE (CASE WHEN condition) supports AND, OR, inequalities, IS NULL, and any boolean expression. Always use searched CASE for multiple conditions.
From sequential WHEN clauses to AND/OR conditions, nested CASE expressions, and conditional aggregation inside GROUP BY queries.
CASE evaluates WHEN clauses from top to bottom and returns the result for the first condition that is TRUE. Once a match is found, remaining clauses are skipped. This means the order of your WHEN clauses matters. Put the most specific or most restrictive conditions first. The ELSE clause handles any rows that do not match any WHEN condition.
-- Categorize orders by size
SELECT
order_id,
amount,
CASE
WHEN amount >= 10000 THEN 'Enterprise'
WHEN amount >= 1000 THEN 'Mid-Market'
WHEN amount >= 100 THEN 'Small Business'
ELSE 'Micro'
END AS order_tier
FROM orders;
-- Order matters: a $15,000 order matches the first clause
-- and is labeled 'Enterprise'. The second clause is never
-- checked for that row, even though $15,000 >= 1000 is true.AND combines multiple conditions within a single WHEN clause. All conditions must be TRUE for the clause to match. This pattern handles business rules that depend on two or more factors simultaneously: an order is 'Priority' only if the amount is high AND the customer is VIP. Both conditions must hold.
-- Flag high-value VIP orders for expedited shipping
SELECT
order_id,
customer_type,
amount,
CASE
WHEN customer_type = 'VIP' AND amount >= 5000
THEN 'Priority Ship'
WHEN customer_type = 'VIP' AND amount < 5000
THEN 'Standard Ship'
WHEN amount >= 5000
THEN 'Expedited Ship'
ELSE 'Standard Ship'
END AS shipping_tier
FROM orders;
-- Row with VIP + $8,000: matches first clause
-- Row with VIP + $200: matches second clause
-- Row with Regular + $6,000: matches third clause
-- Row with Regular + $50: falls to ELSEOR allows a single WHEN clause to match any of several conditions. The clause fires if at least one condition is TRUE. Use OR when multiple distinct scenarios should produce the same result. This reduces duplication compared to writing separate WHEN clauses that all return the same value.
-- Identify accounts needing review
SELECT
account_id,
balance,
status,
last_activity_date,
CASE
WHEN balance < 0 OR status = 'suspended'
THEN 'Immediate Review'
WHEN balance < 100 OR last_activity_date < CURRENT_DATE - 90
THEN 'Routine Review'
ELSE 'No Action'
END AS review_action
FROM accounts;
-- Negative balance OR suspended status: same action needed
-- Low balance OR inactive for 90 days: same review tierA CASE expression can appear inside another CASE expression. The outer CASE handles the first decision. The inner CASE refines the result based on additional criteria. This pattern is useful when the logic has a natural hierarchy: first determine the category, then within that category determine the subcategory. Keep nesting to two levels maximum; deeper nesting becomes unreadable.
-- Tiered pricing based on customer type and volume
SELECT
customer_id,
customer_type,
units_ordered,
unit_price,
CASE
WHEN customer_type = 'Enterprise' THEN
CASE
WHEN units_ordered >= 1000 THEN unit_price * 0.70
WHEN units_ordered >= 100 THEN unit_price * 0.85
ELSE unit_price * 0.90
END
WHEN customer_type = 'SMB' THEN
CASE
WHEN units_ordered >= 500 THEN unit_price * 0.90
ELSE unit_price * 0.95
END
ELSE unit_price
END AS final_price
FROM order_lines;Placing CASE WHEN inside COUNT, SUM, or AVG creates conditional aggregation. This computes different metrics from the same rows in a single pass. It replaces multiple filtered subqueries and is one of the most powerful reporting patterns in SQL. Combined with GROUP BY, it produces cross-tabulation reports.
-- Single query: multiple conditional metrics
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS high_earners,
SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_payroll,
AVG(CASE
WHEN tenure_years >= 5 AND performance_rating >= 4
THEN salary
END) AS avg_salary_senior_performers
FROM employees
GROUP BY department;
-- COUNT(CASE...) counts only rows where the condition is true
-- NULL results from unmatched CASE are ignored by COUNT
-- SUM uses ELSE 0 to avoid NULL in the sumThese mistakes are silent. The query runs, the results look plausible, and the bug only surfaces when someone checks the numbers.
CASE short-circuits on the first match. If a broad condition appears before a specific one, the specific clause never fires. WHEN amount > 0 THEN 'Positive' before WHEN amount > 10000 THEN 'Enterprise' means no row ever reaches the Enterprise clause.
Fix: Put the most specific (restrictive) conditions first. WHEN amount > 10000 before WHEN amount > 0.
WHEN column = 'value' does not match rows where column is NULL. NULL = anything is UNKNOWN, not FALSE. These rows fall through all WHEN clauses and hit ELSE (or become NULL if there is no ELSE). This silently misclassifies rows.
Fix: Add WHEN column IS NULL THEN 'default' as an explicit clause, or use COALESCE(column, 'fallback') before the comparison.
Without ELSE, CASE returns NULL when no WHEN matches. If the result feeds into arithmetic (NULL + 5 = NULL) or a WHERE filter (NULL is not TRUE), downstream logic breaks silently.
Fix: Always include ELSE unless you deliberately want NULL for unmatched rows. For conditional aggregation (COUNT(CASE...)), omitting ELSE is intentional because COUNT ignores NULLs.
These questions test multi-factor conditional logic, conditional aggregation as a performance pattern, and NULL-aware CASE debugging.
What they test:
Ability to combine multiple conditions with AND/OR inside WHEN clauses. The interviewer wants to see you handle a multi-factor decision tree cleanly. They also check whether you order your WHEN clauses correctly (most restrictive first) and include an ELSE for the default case.
Approach:
Define the tiers clearly before writing SQL. High risk: balance < 0 OR (account_age < 30 AND transaction_count = 0). Medium risk: balance < 500 AND transaction_count < 5. Low risk: everything else. Write the CASE with the most critical conditions first. Explain that order matters because CASE short-circuits on the first match. Add ELSE 'Low Risk' as the catch-all.
What they test:
Whether you understand conditional aggregation as an alternative to multiple joins or subqueries. This tests both CASE WHEN proficiency and query optimization awareness. Conditional aggregation scans the table once instead of joining it multiple times.
Approach:
Show the before (three LEFT JOINs to subqueries counting pending, shipped, and cancelled orders) and the after (single GROUP BY with COUNT(CASE WHEN status = 'pending' THEN 1 END) for each status). Explain that the CASE returns NULL for non-matching rows and COUNT ignores NULLs, so each COUNT only tallies the matching status. Mention the performance benefit: one table scan vs three.
What they test:
Understanding of CASE default behavior and NULL comparison pitfalls. When no WHEN clause matches and there is no ELSE, CASE returns NULL. Also, WHEN column = value does not match when column is NULL because NULL = anything is UNKNOWN, not TRUE.
Approach:
Two common causes. First: missing ELSE clause. If no WHEN matches, CASE returns NULL. Always add an ELSE unless you intentionally want NULL. Second: NULL input values. WHEN status = 'active' does not match rows where status IS NULL. To handle NULLs, add a specific clause: WHEN status IS NULL THEN 'Unknown'. Or use COALESCE on the input: WHEN COALESCE(status, 'unknown') = 'active'.
They're checking whether you catch the NULL fall-through and whether you order WHEN clauses from narrowest to broadest without being told. Twenty timed problems is usually enough to stop missing both.