CASE WHEN in SQL: a working analyst's guide

Why CASE WHEN matters

CASE WHEN is the conditional expression in SQL, the closest cousin of an if/else block in a regular language. It lets you produce a new column from arbitrary conditions: bucket users into cohorts, tag rows as fraud-suspect or clean, push a custom ordering through ORDER BY, or fold three queries into one with conditional aggregates. If you spend any time in Snowflake, BigQuery, or Postgres, you will write CASE every day.

On a data analyst loop at Stripe, Airbnb, or DoorDash, CASE shows up in almost every SQL question. The phrasing is rarely "write a CASE expression" — it is "count active users", "compute conversion from signup to first order", "rank customers into VIP and standard tiers". All of those resolve to a CASE nested inside COUNT, SUM, ORDER BY, or GROUP BY. Candidates who fumble it end up writing three subqueries and joining them when a single SELECT would have done the job. This guide walks the syntax, the places CASE earns its keep, and the traps that show up in screens.

Syntax: simple CASE and searched CASE

SQL has two CASE forms. Both return a single value and can be used anywhere an expression is legal: SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and inside aggregates and window functions.

Simple CASE compares one expression against a list of constants:

SELECT
    user_id,
    CASE status
        WHEN 'active'  THEN 'Active'
        WHEN 'churned' THEN 'Churned'
        WHEN 'paused'  THEN 'Paused'
        ELSE 'Unknown'
    END AS status_label
FROM users;

Searched CASE evaluates arbitrary boolean conditions. This is the form you will use in roughly nine out of ten queries:

SELECT
    user_id,
    CASE
        WHEN lifetime_revenue >= 50000 THEN 'VIP'
        WHEN lifetime_revenue >= 10000 THEN 'Mid'
        ELSE 'New'
    END AS segment
FROM users;

Conditions are checked top to bottom; the first one that matches wins and the rest are skipped. Ordering is therefore not cosmetic. If you put >= 10000 first, every VIP user falls into the "Mid" bucket because their revenue also satisfies the looser condition. Always order from most restrictive to least restrictive.

CASE in SELECT: segmentation and buckets

The most common job is turning a continuous metric into a categorical column for a dashboard or a cohort cut.

Activity segmentation by last seen:

SELECT
    user_id,
    CASE
        WHEN last_active_at >= CURRENT_DATE - INTERVAL '7 days'  THEN 'active'
        WHEN last_active_at >= CURRENT_DATE - INTERVAL '30 days' THEN 'dormant'
        ELSE 'churned'
    END AS activity_segment
FROM users;

Order-value buckets for a histogram:

SELECT
    order_id,
    amount,
    CASE
        WHEN amount <  50  THEN 'under 50'
        WHEN amount <  200 THEN '50-200'
        WHEN amount < 1000 THEN '200-1000'
        ELSE '1000+'
    END AS amount_bucket
FROM orders;

Bucket boundaries are a product call: pick cutoffs from the actual distribution — quartiles or round numbers customers think in. CASE just turns the axis into something a human can read.

CASE with aggregate functions

This is the pattern that separates fluent SQL from beginner SQL. CASE inside an aggregate computes a conditional metric in the same pass as everything else, with no subqueries and no joins.

Conditional COUNT for a funnel:

SELECT
    DATE_TRUNC('week', created_at) AS week,
    COUNT(*)                                                AS registrations,
    COUNT(CASE WHEN completed_onboarding   THEN 1 END)      AS onboarded,
    COUNT(CASE WHEN first_order_at IS NOT NULL THEN 1 END)  AS buyers
FROM users
WHERE created_at >= '2026-01-01'
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week;

COUNT ignores NULL, so a CASE with no ELSE returns NULL for the rows that did not match, and they simply do not contribute. No ELSE is needed here, and adding ELSE 0 would be wrong because then COUNT would count zeros too.

Conditional SUM for revenue by channel in one row per month:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                                                AS total_revenue,
    SUM(CASE WHEN channel = 'organic' THEN amount ELSE 0 END)  AS organic_revenue,
    SUM(CASE WHEN channel = 'paid'    THEN amount ELSE 0 END)  AS paid_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

ELSE 0 inside SUM is a readability choice. SUM ignores NULL, so the two forms produce the same number, but ELSE 0 makes intent explicit when a teammate audits the query at 11pm. The same trick works inside AVG, MIN, MAX, and window functions — AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) is the classic way to compute conversion rate as a float without dividing.

CASE in GROUP BY and ORDER BY

GROUP BY with CASE lets you group by a computed bucket:

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_group,
    COUNT(*)                          AS user_count,
    ROUND(AVG(lifetime_revenue), 2)   AS avg_ltv
FROM users
GROUP BY
    CASE
        WHEN age < 25 THEN '18-24'
        WHEN age < 35 THEN '25-34'
        WHEN age < 45 THEN '35-44'
        ELSE '45+'
    END
ORDER BY user_count DESC;

You have to repeat the CASE in GROUP BY. The standard does not let you group by a SELECT alias; PostgreSQL and MySQL allow it quietly, but BigQuery and Snowflake disagree on edge cases, so writing it twice is the portable habit.

ORDER BY with CASE for a custom sort that does not match alphabetical order:

SELECT topic, question_count
FROM topics
ORDER BY
    CASE topic
        WHEN 'SQL'                 THEN 1
        WHEN 'Python'              THEN 2
        WHEN 'A/B testing'         THEN 3
        WHEN 'Product analytics'   THEN 4
        ELSE 99
    END;

Anything not in the explicit list lands in the catch-all bucket. The pattern shows up for subscription tiers, severity levels, and pipeline stages — domains where alphabetical order would put "Enterprise" before "Starter".

Nested CASE

You can nest CASE inside CASE, but past one level of nesting the query becomes painful to read.

SELECT
    user_id,
    CASE
        WHEN subscription_type = 'premium' THEN
            CASE
                WHEN lifetime_revenue > 50000 THEN 'Premium VIP'
                ELSE 'Premium'
            END
        WHEN subscription_type = 'free' THEN 'Free'
        ELSE 'Unknown'
    END AS user_tier
FROM users;

If you find yourself at three levels, split the logic into a CTE: compute the inner classification first, then the outer one. Reviewers will thank you and the optimizer will not care either way.

CASE vs COALESCE vs IF

COALESCE is a special case: COALESCE(x, 0) is exactly CASE WHEN x IS NOT NULL THEN x ELSE 0 END, just shorter. When all you need is a NULL fallback, reach for COALESCE — see the NULL in SQL guide for how NULL propagates through expressions and aggregates. IF() and IIF() are dialect-specific shortcuts; searched CASE is the portable choice for anything beyond a NULL fallback.

Common pitfalls

Forgetting ELSE is the bug that ships most often. Without ELSE, rows that fail every WHEN get NULL. Inside a COUNT that is fine because COUNT ignores NULL, but in a SELECT that drives a dashboard, NULL shows up as a blank cell and the PM asks why some users have no segment. Every CASE in a SELECT clause needs an ELSE, even if it is just ELSE 'unknown'. Treat the absence of ELSE as a signal that you have not thought about the edge case yet.

Ordering conditions from loose to strict swallows the specific buckets. The CASE engine takes the first match, so writing WHEN revenue >= 10000 THEN 'Mid' before WHEN revenue >= 50000 THEN 'VIP' puts every VIP into Mid because their revenue also passes the looser threshold. Always order from most restrictive to least restrictive, the same way you would write a series of else if blocks.

NULL comparisons silently drop rows. CASE WHEN status = 'active' THEN ... does not match a row where status is NULL, because NULL = 'active' is NULL, not TRUE. The CASE moves to the next branch, and if no branch handles NULL explicitly, the row falls to ELSE. If NULL is a meaningful category — users who never set a status — add WHEN status IS NULL THEN 'unset' before the equality branches.

Repeating a long CASE in GROUP BY is verbose but unavoidable in portable SQL. If the expression is more than a couple of lines, lift it into a CTE: compute the bucketed column once in a subquery, then SELECT and GROUP BY the alias in the outer query. The optimizer inlines it and there is no runtime cost.

Using CASE inside WHERE rarely earns its price. WHERE CASE WHEN x > 0 THEN 1 ELSE 0 END = 1 works, but it prevents the optimizer from using an index on x and is harder to read than WHERE x > 0. CASE in WHERE is genuinely useful only when the filter depends on a session variable or query parameter — for everything else, plain AND/OR is the right move.

Interview questions

What is the difference between simple CASE and searched CASE? Simple CASE compares one expression against a list of constants — CASE status WHEN 'active' THEN ... END. Searched CASE evaluates arbitrary conditions — CASE WHEN revenue > 10000 AND region = 'EU' THEN ... END. Searched is more flexible for ranges and compound predicates; simple is shorter when you only need equality against a fixed list.

How do you compute signup-to-purchase conversion in a single query? COUNT(CASE WHEN first_order_at IS NOT NULL THEN 1 END) * 100.0 / COUNT(*) gives the percentage. CASE without ELSE returns NULL for users who never bought, COUNT ignores those NULLs, and the 100.0 forces float division. The same shape works for any funnel step — see the SQL window functions interview questions post for the windowed cousin.

What does CASE return when no branch matches and ELSE is missing? NULL. Inside an aggregate that is usually what you want because aggregates ignore NULL. In a SELECT clause that powers a dashboard it is usually a bug because NULL renders as blank. Write an explicit ELSE in any SELECT-clause CASE.

How do you compute the share of mobile users per day? SELECT day, ROUND(100.0 * COUNT(CASE WHEN platform = 'mobile' THEN 1 END) / COUNT(*), 1) AS mobile_pct FROM events GROUP BY day. The CASE-inside-COUNT pattern shows up in roughly half of the SQL questions on a product analyst loop — make it muscle memory.

Related reading

• NULL in SQL guide
• SQL window functions interview questions
• CASE WHEN SQL cheat sheet
• How to build customer segments in SQL

FAQ

Can you put CASE inside CASE?

Yes. Nested CASE is legal SQL at arbitrary depth. The problem is human: past one level the indentation eats a screen and reviewers stop reading. If you need conditional logic that depends on another conditional, lift the inner classification into a CTE and reference its column from the outer CASE. The optimizer usually inlines the CTE, so runtime is identical and the code is half the size.

Does CASE affect query performance?

Almost never. CASE evaluates row by row and the cost is negligible next to scans and joins. The exception is CASE in WHERE on an indexed column: the optimizer cannot use the index when the predicate hides the column behind an expression, and the query falls back to a full scan. If you filter on a CASE result frequently, materialize the bucketed value as a generated column or compute it once in a CTE.

How do I replace CASE for NULL handling?

Use COALESCE. COALESCE(city, 'unknown') is shorter than CASE WHEN city IS NULL THEN 'unknown' ELSE city END and reviewers parse it faster. CASE is the right tool when NULL is one of several substitutions, or NULL plus a range check. For the plain "fallback to a default" case, COALESCE wins on readability every time.

Is CASE WHEN portable across databases?

Yes. CASE WHEN is ANSI SQL and behaves identically across PostgreSQL, MySQL, ClickHouse, BigQuery, Snowflake, Redshift, Databricks, SQL Server, and Oracle. The dialect-specific shortcuts — IF(), IIF(), IFNULL(), DECODE() — do not port, so if your SQL might run on more than one engine, default to CASE.

When should I use CASE vs a JOIN to a lookup table?

If the mapping has more than a handful of cases or changes frequently, put it in a lookup table and JOIN. A static three-tier segment can live in CASE forever; a 50-row country-to-region mapping should be a table with a foreign key. CASE is for logic, JOIN is for data — when the mapping starts to look like data owned by the business, move it out of the query.

If you want to drill CASE WHEN and the rest of the SQL interview pattern catalog every day, NAILDD is launching with 500+ SQL problems including conditional aggregates, bucket queries, and the full set of cohort and funnel patterns that hiring managers actually ask.