SQL Practice
A revenue dashboard at a payments company went red for six hours because a CASE expression was missing an ELSE branch. Silent NULLs quietly zeroed out one tier of customers. The fix was six characters. The retro was two hours. That's CASE WHEN: tiny syntax, huge blast radius when you get it wrong.
Our corpus of 1,042 verified data engineering rounds shows CASE WHEN in 28% of SQL questions and conditional aggregation in 18% of pivot prompts. It's the construct that separates candidates who've written dbt models from candidates who've read about dbt models.
This guide walks through the syntax once, then hits the six patterns that actually show up in phone screens. If you've shipped CASE logic to a warehouse, skim the pattern list for gaps. If you haven't, write every example by hand.
Questions using CASE WHEN
Pivot prompts using cond. agg
Phone-screen SQL rounds
Verified rounds analyzed
Source: DataDriven analysis of 1,042 verified data engineering interview rounds.
SQL has two CASE forms. The searched form handles any boolean expression. The simple form is shorthand for equality checks against a single value. Both return a single value per row and can be used anywhere an expression is valid.
Evaluates each WHEN condition as a standalone boolean expression. Conditions can reference different columns, use ranges, call functions, or check IS NULL. This is the form you will use 90% of the time.
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
WHEN condition_3 THEN result_3
ELSE default_result
ENDCompares one expression against multiple values. It is shorter when you are checking one column against a known list. It cannot handle ranges, IS NULL, or conditions that span multiple columns.
CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
WHEN value_3 THEN result_3
ELSE default_result
ENDThese two queries produce identical results. The searched form is more explicit. The simple form is more compact. Pick whichever your team prefers, but be ready to write both in an interview.
-- Searched CASE
SELECT
order_id,
CASE
WHEN status = 'delivered' THEN 'complete'
WHEN status = 'shipped' THEN 'in_transit'
WHEN status = 'pending' THEN 'waiting'
ELSE 'unknown'
END AS order_stage
FROM orders;
-- Simple CASE (same result)
SELECT
order_id,
CASE status
WHEN 'delivered' THEN 'complete'
WHEN 'shipped' THEN 'in_transit'
WHEN 'pending' THEN 'waiting'
ELSE 'unknown'
END AS order_stage
FROM orders;Interview note: CASE is an expression, not a statement. It returns a value. That means you can use it inside SUM(), COUNT(), in JOIN conditions, in CHECK constraints, and even inside another CASE. Interviewers test whether you understand this distinction.
Omitting ELSE is valid SQL but dangerous. When no WHEN clause matches and ELSE is absent, the result is NULL. That NULL propagates silently through arithmetic, string concatenation, and some aggregations.
-- Without ELSE: returns NULL for non-matching rows
SELECT
product_id,
CASE WHEN category = 'electronics' THEN price * 1.1 END AS adjusted_price
FROM products;
-- Row with category = 'clothing' gets adjusted_price = NULL
-- With ELSE: explicit default
SELECT
product_id,
CASE
WHEN category = 'electronics' THEN price * 1.1
ELSE price
END AS adjusted_price
FROM products;
-- Row with category = 'clothing' gets adjusted_price = priceInterview note: The one exception where omitting ELSE is intentional: inside COUNT(). COUNT(CASE WHEN ... THEN 1 END) relies on the missing ELSE producing NULL, which COUNT ignores. This is the conditional aggregation pattern described below.
These patterns account for the vast majority of CASE WHEN usage in production data pipelines and interviews. Each one solves a specific problem. Conditional aggregation alone covers about half of all CASE WHEN interview questions.
Conditional aggregation shows up in 18% of pivot prompts in our verified corpus. You wrap CASE inside COUNT or SUM to filter rows inline, swapping four scans for one. An analytics team at a rideshare company cut a daily job from 47 minutes to 90 seconds by collapsing five filtered CTEs into one conditional aggregation block. It's the move interviewers watch for.
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,
ROUND(
100.0 * COUNT(CASE WHEN tenure_years >= 5 THEN 1 END) / COUNT(*),
1
) AS pct_senior
FROM employees
GROUP BY department;Interview note: The THEN 1 inside COUNT is a convention. COUNT ignores NULLs, so when the WHEN condition is false and there is no ELSE, the result is NULL and gets skipped. SUM uses ELSE 0 because SUM(NULL) is NULL, not 0.
Before PIVOT syntax existed (and on engines that still lack it), CASE WHEN inside aggregation was the standard way to pivot. You create one CASE expression per output column, each filtering for a different value. This technique works on every SQL engine.
SELECT
product_id,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_revenue,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_revenue,
SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_revenue,
SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_revenue
FROM quarterly_sales
GROUP BY product_id;Interview note: Interviewers often frame this as 'transform rows into columns without using PIVOT.' If you reach for CASE + SUM immediately, it signals experience with engines like PostgreSQL and Redshift that have no native PIVOT.
Grouping continuous values into discrete buckets is a daily task in analytics engineering. CASE WHEN lets you define custom bucket boundaries. This is cleaner than width_bucket() because you control the labels and can use irregular intervals that match business logic.
SELECT
CASE
WHEN order_total < 25 THEN 'small'
WHEN order_total < 100 THEN 'medium'
WHEN order_total < 500 THEN 'large'
ELSE 'enterprise'
END AS order_tier,
COUNT(*) AS order_count,
ROUND(AVG(order_total), 2) AS avg_total
FROM orders
GROUP BY
CASE
WHEN order_total < 25 THEN 'small'
WHEN order_total < 100 THEN 'medium'
WHEN order_total < 500 THEN 'large'
ELSE 'enterprise'
END
ORDER BY MIN(order_total);Interview note: The CASE in GROUP BY must be identical to the CASE in SELECT (or use a CTE/subquery). ORDER BY MIN(order_total) sorts buckets by their lower bound, which is cleaner than ordering by the label string.
CASE WHEN pairs with IS NULL to handle NULLs explicitly, giving you more control than COALESCE when the logic is not a simple default replacement. You can route NULL rows to different processing paths, flag data quality issues, or apply different calculations based on whether a value is present.
SELECT
user_id,
CASE
WHEN email IS NULL AND phone IS NULL THEN 'unreachable'
WHEN email IS NULL THEN 'phone_only'
WHEN phone IS NULL THEN 'email_only'
ELSE 'fully_contactable'
END AS contact_status,
CASE
WHEN last_login IS NULL THEN 'never_logged_in'
WHEN last_login < CURRENT_DATE - INTERVAL '90 days' THEN 'dormant'
WHEN last_login < CURRENT_DATE - INTERVAL '30 days' THEN 'at_risk'
ELSE 'active'
END AS engagement_tier
FROM users;Interview note: CASE evaluates WHEN clauses top to bottom and stops at the first match. Put the most restrictive condition first. If you swap the order here, 'unreachable' users would match 'phone_only' or 'email_only' instead.
Custom sort orders that don't follow alphabetical or numeric sequence require CASE in ORDER BY. This is common when business stakeholders want priority-based ordering: critical items first, then high, then medium, then low. You assign a numeric rank to each category inside CASE.
SELECT
ticket_id,
subject,
priority,
created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY
CASE priority
WHEN 'critical' THEN 1
WHEN 'high' THEN 2
WHEN 'medium' THEN 3
WHEN 'low' THEN 4
ELSE 5
END,
created_at ASC;Interview note: This uses the simple CASE form (CASE priority WHEN ...) rather than the searched form (CASE WHEN priority = ...). Both work. The simple form is shorter when comparing one expression against multiple values.
CASE in WHERE lets you apply different filter conditions based on a parameter or another column's value. This pattern appears in stored procedures and parameterized reports where the filter logic changes based on user input. It replaces dynamic SQL in many cases.
-- Filter by different date columns based on report_type
SELECT
order_id,
customer_id,
order_date,
ship_date,
status
FROM orders
WHERE
CASE
WHEN 'shipped' = 'shipped' THEN ship_date
WHEN 'shipped' = 'placed' THEN order_date
ELSE order_date
END >= CURRENT_DATE - INTERVAL '30 days';Interview note: Be careful with CASE in WHERE. It can prevent index usage because the optimizer cannot know which column to scan until runtime. For high-volume tables, consider rewriting as OR conditions or using UNION ALL for each branch.
Nesting one CASE inside another is syntactically valid and occasionally necessary. But most nested CASE expressions can be flattened into a single CASE with combined conditions. Flat is easier to read, easier to test, and easier to maintain.
SELECT
user_id,
CASE
WHEN country = 'US' THEN
CASE
WHEN state IN ('CA', 'NY', 'TX') THEN 'tier_1'
ELSE 'tier_2'
END
WHEN country = 'UK' THEN 'tier_1'
ELSE 'tier_3'
END AS market_tier
FROM users;SELECT
user_id,
CASE
WHEN country = 'US' AND state IN ('CA', 'NY', 'TX') THEN 'tier_1'
WHEN country = 'US' THEN 'tier_2'
WHEN country = 'UK' THEN 'tier_1'
ELSE 'tier_3'
END AS market_tier
FROM users;Interview note: Interviewers will sometimes hand you a nested CASE and ask you to simplify it. Flatten by combining the outer and inner conditions with AND. If you have three or more levels, push the logic into a lookup table joined at query time.
Every SQL engine has its own shorthand for conditional logic. CASE WHEN is the only form that works everywhere. In an interview, always write CASE unless the interviewer specifies a particular engine and you know its shorthand cold.
| Construct | Standard | Engines | Notes |
|---|---|---|---|
| CASE WHEN ... THEN ... END | ANSI SQL | All engines | Universal. Use this by default. |
| IF(condition, true_val, false_val) | Non-standard | MySQL, BigQuery | Two-branch only. No multi-condition chaining. |
| IFF(condition, true_val, false_val) | Non-standard | Snowflake, SQL Server (as IIF) | Snowflake uses IFF, SQL Server uses IIF. Same limitation. |
| DECODE(expr, search, result, ...) | Oracle-specific | Oracle, Snowflake | Equality checks only. Cannot do range comparisons. |
| CHOOSE(index, val1, val2, ...) | SQL Server-specific | SQL Server | Picks value by integer position. Rarely used. |
These questions come from verified interview reports at companies ranging from Series B startups to FAANG. They test CASE WHEN in combination with aggregation, pivoting, and query design.
What they test:
Conditional aggregation with CASE WHEN inside COUNT, combined with date truncation and percentage math. This is the bread and butter of analytics queries.
Approach:
Use DATE_TRUNC('month', order_date) for the time axis. For each status, use COUNT(CASE WHEN status = 'x' THEN 1 END). Divide by COUNT(*) and multiply by 100.0 (not 100, to avoid integer division). GROUP BY the truncated date.
What they test:
CASE WHEN inside MIN for pivoting event data. This is a real-world pattern used in every user analytics pipeline.
Approach:
SELECT user_id, MIN(CASE WHEN event_type = 'login' THEN event_timestamp END) AS first_login, MIN(CASE WHEN event_type = 'purchase' THEN event_timestamp END) AS first_purchase, MIN(CASE WHEN event_type = 'support_ticket' THEN event_timestamp END) AS first_support_ticket FROM events GROUP BY user_id.
What they test:
Syntax knowledge and practical judgment. Most candidates only know one form.
Approach:
Simple CASE (CASE col WHEN 'a' THEN ...) compares one expression to multiple values. Searched CASE (CASE WHEN col > 10 THEN ...) evaluates arbitrary boolean expressions. Use simple CASE for equality checks against a single column. Use searched CASE for ranges, multiple columns, or IS NULL checks.
What they test:
NULL awareness. Without ELSE, CASE returns NULL when nothing matches. This can silently break downstream aggregations.
Approach:
CASE returns NULL. This matters inside SUM (which ignores NULLs) and in string concatenation (which propagates NULLs in standard SQL). Always include ELSE unless you intentionally want NULL for the unmatched case, and document why.
What they test:
CASE WHEN with computed thresholds. Requires a CTE or subquery to calculate the mean and stddev, then a CASE expression referencing those values.
Approach:
WITH stats AS (SELECT AVG(quantity) AS avg_qty, STDDEV(quantity) AS std_qty FROM orders) SELECT o.*, CASE WHEN o.discount > 0.5 THEN 'suspicious' WHEN o.quantity > s.avg_qty + 3 * s.std_qty THEN 'suspicious' ELSE 'normal' END AS flag FROM orders o CROSS JOIN stats s.
What they test:
Whether you know that CASE is an expression, not a statement, so it works anywhere an expression is valid: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN ON.
Approach:
Yes. GROUP BY CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END. The identical CASE must appear in SELECT. Alternatively, use a CTE that computes the bucket first, then GROUP BY the alias in databases that allow it (like BigQuery and MySQL).
What they test:
Code quality instincts. Nested CASE is legal but hard to maintain. Interviewers want you to flatten it.
Approach:
Flatten to: CASE WHEN a AND b THEN 'x' WHEN a AND NOT b THEN 'y' ELSE 'z' END. This is logically identical and easier to read. Mention that deeply nested CASE is a code smell; if you have more than two levels, consider a lookup table or a CTE with separate flag columns.
These four mistakes appear in interview code reviews and in production pull requests. Each one produces valid SQL that runs without error but returns wrong results.
Without an ELSE clause, CASE returns NULL when no WHEN condition matches. This NULL then propagates through arithmetic, string operations, and some aggregations. In a SUM, the NULL row is silently skipped. In a concatenation, the entire result can become NULL.
Fix: Always include an explicit ELSE. If you want NULL, write ELSE NULL to show it is intentional. This makes your intent clear to code reviewers and your future self.
SQL requires that a CASE expression in SELECT either be inside an aggregate function or repeated identically in GROUP BY. Writing the same 10-line CASE twice is error-prone. If you edit one and forget the other, the query breaks or silently produces wrong groups.
Fix: Use a CTE or subquery to compute the CASE result first, then reference the alias. Some engines (BigQuery, MySQL) allow GROUP BY alias directly. PostgreSQL requires the full expression or a CTE.
This is the same trap as WHERE col = NULL. The equality operator returns NULL (not TRUE) when compared to NULL. Your WHEN clause never matches NULL rows, and they fall through to ELSE. The query runs without error, which makes this bug hard to catch.
Fix: Always use CASE WHEN column IS NULL, never CASE WHEN column = NULL. If you are checking for both NULL and empty string, write: CASE WHEN column IS NULL OR column = '' THEN ...
CASE evaluates WHEN clauses sequentially and returns the first match. If you put a broader condition before a narrower one, the narrow condition is never reached. Example: CASE WHEN salary > 50000 THEN 'above_avg' WHEN salary > 100000 THEN 'high' END. The second branch is dead code because every salary above 100k also matches the first branch.
Fix: Order from most restrictive to least restrictive. Check salary > 100000 first, then salary > 50000. Think of it like if/else if: the first match wins.
Reading syntax doesn't build reflex. Twenty reps on real SQL with live grading does. Miss an ELSE branch now, on a warm-up problem, instead of at 2am on a pager ticket.