CASE WHEN for Business Logic: The Pattern Analysts Underrate

Ask a dozen analysts what the most-written SQL construct on their job is, and a dozen will tell you the same thing: CASE WHEN. It is how vague stakeholder asks — "group the big accounts separately," "flag anything unusual," "count the ones that converted quickly" — become queryable rules. Window functions get the press. CASE WHEN does the work.

This post is the practical shape of it: the basic form, the NULL trap that burns every junior, the three idiomatic patterns (labeling, bucketing, pivoting), and the senior-level decision of when to write it inline vs push it into a dbt model so every downstream query gets the same definition.

Why CASE WHEN beats IF in every warehouse

Every major warehouse — PostgreSQL, Snowflake, BigQuery, Redshift — supports CASE WHEN. Only some support IF(...) or IIF(...). Writing CASE WHEN from muscle memory means your SQL ports across engines without edits, and PR reviewers don’t have to squint to understand which dialect you were thinking in.

There’s a second reason. CASE WHEN chains naturally: three, four, five mutually exclusive conditions read top-to-bottom with WHEN ... THEN ... and a final ELSE. An IF tower of nested IF(cond1, val1, IF(cond2, val2, ...)) is a maintenance hazard. Senior analysts don’t write trees when they can write lists.

The basic form and the NULL trap

Here’s the canonical shape:

SELECT
  customer_id,
  CASE
    WHEN lifetime_value > 10000 THEN 'VIP'
    WHEN lifetime_value > 1000  THEN 'High'
    WHEN lifetime_value > 100   THEN 'Mid'
    ELSE 'Low'
  END AS segment
FROM dim_customers;

Three things to internalize. First, order matters — the first WHEN that matches wins. If you swap "High" and "VIP" above, every VIP gets labeled "High." Second, ELSE is optional but almost always worth writing; omitting it means unmatched rows get NULL, which is rarely what a stakeholder wants in a segment column. Third, the CASE expression is a single value; you can use it anywhere a column would go — in SELECT, in GROUP BY, even inside SUM().

While we’re on it: CASE WHEN col IN (1, 2, NULL) also doesn’t match NULL rows. IN and = are both NULL-unsafe in the same way. If you need to treat NULL as a real value, say so explicitly: CASE WHEN col IS NULL OR col IN (1, 2) THEN ....

Bucketing continuous values

Half of what CASE WHEN gets used for in analyst work is bucketing a continuous number — revenue, session length, days-since-signup — into a small set of human-readable labels. The trick is ordering from most-restrictive to least-restrictive so the first match wins correctly, and being explicit about boundary conditions.

SELECT
  order_id,
  purchase_amount,
  CASE
    WHEN purchase_amount >= 500 THEN 'Enterprise'
    WHEN purchase_amount >= 100 THEN 'Standard'
    WHEN purchase_amount >= 10  THEN 'Small'
    WHEN purchase_amount >  0   THEN 'Micro'
    ELSE 'Invalid'
  END AS order_tier
FROM fact_purchases;

Notice the > 0 branch at the bottom — that catches zero-dollar rows (refunds, free trials, corrupted inserts) and flags them as Invalid rather than letting them silently become Micro. Stakeholders never ask for this, but they always appreciate it when they see it. Your manager will read the segment column and immediately trust the rest of the query.

SELECT
  CASE
    WHEN purchase_amount > 10000 THEN 'VIP'
    WHEN purchase_amount > 1000  THEN 'High'
    WHEN purchase_amount > 100   THEN 'Mid'
    ELSE 'Low'
  END AS tier,
  COUNT(*) AS n_purchases
FROM fact_purchases
WHERE purchased_at >= DATE('now', '-30 days')
GROUP BY tier
ORDER BY n_purchases DESC;

Pivoting rows into columns with CASE

The single most powerful CASE WHEN pattern, and the one that separates junior from senior: pivoting rows into columns with CASE inside an aggregate. Most warehouses don’t have a clean PIVOT operator (or the syntax varies) — so everyone writes pivots this way.

SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'email' THEN revenue ELSE 0 END) AS email_revenue,
  SUM(CASE WHEN channel = 'paid'  THEN revenue ELSE 0 END) AS paid_revenue,
  SUM(CASE WHEN channel = 'organic' THEN revenue ELSE 0 END) AS organic_revenue,
  SUM(revenue) AS total_revenue
FROM fact_purchases
WHERE purchased_at >= DATE('now', '-30 days')
GROUP BY campaign_id;

Read that carefully. The CASE returns revenue when the channel matches, and 0 otherwise; SUM() adds up only the matching rows per campaign. The result: one row per campaign, one column per channel, plus a total. That’s the structure stakeholders actually want in a dashboard. Junior analysts produce a long table with a channel column; senior analysts pivot it for them.

SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'email' THEN 1 ELSE 0 END) AS email_sends,
  SUM(CASE WHEN channel = 'sms'   THEN 1 ELSE 0 END) AS sms_sends,
  SUM(CASE WHEN channel = 'push'  THEN 1 ELSE 0 END) AS push_sends,
  COUNT(*) AS total_sends
FROM fact_sends
WHERE sent_at >= DATE('now', '-30 days')
GROUP BY campaign_id
ORDER BY total_sends DESC;

CASE WHEN condition THEN 1 ELSE 0 END inside SUM() is the idiomatic "count where" pattern. COUNT(CASE WHEN condition THEN 1 END) works too because COUNT ignores NULLs — pick whichever reads more cleanly to your team.

When to push the logic upstream instead

The moment multiple queries start repeating the same CASE WHEN block, you have a source-of-truth problem. Imagine three dashboards all defining "VIP customer" slightly differently — one uses > 10000, one uses >= 10000, one uses lifetime revenue net of refunds. Stakeholders will pull those three dashboards in the same meeting, see three different VIP counts, and lose trust in the whole data team.

The right fix is to push the CASE into a dbt model (or equivalent warehouse view), so customer_segment is a column on dim_customers and every downstream query uses the same definition. CASE WHEN is cheap to write inline; it’s expensive to write inline *twice*. The second time is your signal to lift it upstream.

CASE WHEN is a tiny piece of SQL syntax, but it’s the construct analyst work rests on. Master its bucketing and pivoting patterns, respect the NULL rules, and know when to lift it upstream — and you’ll produce queries that get trusted on first read.