◀ Course contents Part 1 · Module 1-06

CASE Expressions

SQL's if-statement, and the last module of Part 1

So far every column has either been stored or computed by arithmetic. CASE lets a column depend on a condition — turning a price into a tier, a flag into a word, a NULL into a category. It is the tool that turns raw columns into the categories a report is actually about.

Ready?

CASE Is an Expression, Not a Statement

A CASE produces a value. That is worth saying first, because it means a CASE can go anywhere a column can go — in the SELECT list, inside WHERE, inside ORDER BY, and inside an aggregate function.

SELECT name,
       price,
       CASE
         WHEN price >= 100 THEN 'premium'
         WHEN price >= 25  THEN 'standard'
         ELSE 'entry'
       END AS tier
FROM products;

Read it as a list of questions asked in order. Is the price at least 100? Then the value is 'premium' and we stop. Otherwise, is it at least 25? And so on, with ELSE catching everything left. END closes it, and AS tier names the column it produced.

There is a shorter form for testing one expression against several values, which only does equality:

CASE is_active
  WHEN 1 THEN 'active'
  ELSE 'churned'
END

Quick check

Where can a CASE expression appear?

Branch Order Is the Specification

Evaluation stops at the first branch that is true. Everything below it is never even considered. That is what lets the second branch in the price example say simply WHEN price >= 25 — anything reaching it has already failed the 100 test, so "and under 100" is implied.

Reverse the two branches and every premium product is labelled standard, because 250 is also at least 25 and that branch now comes first. Nothing errors. The report is simply wrong.

-- Wrong: nothing is ever premium
CASE
  WHEN price >= 25  THEN 'standard'
  WHEN price >= 100 THEN 'premium'
  ELSE 'entry'
END

The rule that prevents it

Write branches from most specific to least. A catch-all sitting above a special case silently swallows it, and the only symptom is a category nobody ever lands in — which is exactly the kind of thing that survives a review.

The same rule governs priority rules that come from a stakeholder. "A churned team-plan customer is churned, not enterprise" is not extra logic — it is a statement about which branch goes first.

Quick check

A CASE lists WHEN plan = 'team' THEN 'enterprise' before WHEN NOT is_active THEN 'churned'. What happens to a churned team-plan user?

No ELSE Means NULL

ELSE is optional, and leaving it out has a specific consequence: a row matching no branch gets NULL. After the last module you know exactly how much trouble that causes downstream.

CASE
  WHEN plan = 'pro'  THEN 'growth'
  WHEN plan = 'team' THEN 'enterprise'
END
-- every free user gets NULL

The temptation is to say the branches are exhaustive so it cannot happen. Sometimes that is true today. It stops being true the moment somebody adds a fourth plan, and the bucketing column starts producing NULLs in a report that nobody re-reads.

Write the ELSE. If you genuinely have no sensible category, make that explicit — ELSE 'other' or ELSE 'unclassified' — so the unexpected row shows up as a visible bucket rather than as a blank.

Silent

No ELSE

Unmatched rows come back NULL and quietly drop out of counts and comparisons.

Visible

`ELSE 'other'`

The unexpected row appears as a bucket you can see and go and investigate.

Quick check

A CASE with two WHEN branches and no ELSE runs over 12 rows, 5 of which match nothing. What comes back for those 5?

A Number From One Branch, Zero From the Other

A CASE does not have to return text. Returning a value from one branch and 0 from the other is the most useful CASE pattern in all of analytics:

CASE WHEN status = 'paid' THEN amount ELSE 0 END

On its own that is mildly interesting. Wrapped in an aggregate in Part 2 it becomes something a WHERE clause cannot do at all — two different filters, side by side, in one query:

SELECT SUM(amount)                                            AS booked,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)  AS recognised
FROM orders;

A WHERE status = 'paid' would have filtered the whole query and lost the first number. This is called conditional aggregation, and it is how one query produces a whole row of metrics that each count something slightly different.

Where this is going

Part 2 is aggregation: counting, summing and grouping. Almost every real metric there — conversion rate, paid share, active-user percentage — is a SUM or an AVG wrapped around a CASE exactly like this one.

Get comfortable with this shape now and half of Part 2 is already familiar.

Quick check

Why compute recognised revenue with a CASE rather than with WHERE status = 'paid'?

0 of 4 completed

Loading the tables…

To do

CASE is SQL's if-statement. It walks its branches from the top and stops at the first one that is true:

CASE
  WHEN price >= 100 THEN 'premium'
  WHEN price >= 25  THEN 'standard'
  ELSE 'entry'
END AS tier

Because it stops at the first match, the second branch never has to say "and under 100" — anything reaching it has already failed the first test. Writing the branches from most specific to least is what makes that work.

Your task: return name, price, and a column named tier that reads premium at 100 or more, standard at 25 or more, and entry below that.

query.sql

SELECT name, price,
       CASE
       END AS tier
FROM products;

PostgreSQL

Hint

Two WHEN branches and an ELSE, in that order: WHEN price >= 100 THEN 'premium', WHEN price >= 25 THEN 'standard', ELSE 'entry'.

Output

To do

A CASE branch can hold any condition a WHERE can, including IS NULL. That makes it the readable way to turn a missing value into a category rather than a blank.

ELSE is optional, and leaving it out is a trap: a row that matches no branch gets NULL, which is usually the opposite of what a bucketing exercise was for. Write the ELSE.

Your task: return name and a column named account_type reading individual where the user has no company and company where they do.

query.sql

SELECT name,
FROM users;

PostgreSQL

Hint

CASE WHEN company IS NULL THEN 'individual' ELSE 'company' END AS account_type — one branch and an ELSE is enough.

Output

To do

is_active is a boolean: it holds true or false, not 1 or 0. Postgres has a real boolean type, which means a WHEN can test the column directly — there is nothing to compare it to.

CASE
  WHEN is_active THEN 'active'
  ELSE 'churned'
END

WHEN is_active = true also works and is just noise; the column is already the condition. For the other side, write WHEN NOT is_active rather than comparing against false.

Databases without a boolean type store flags as 1 and 0, and is_active = 1 is what you write there. Postgres rejects that outright — operator does not exist: boolean = integer — because a truth value and a number are genuinely different things, and it would rather say so than guess.

Your task: return name, plan and a column named state reading active when is_active is true and churned otherwise.

query.sql

SELECT name, plan,
FROM users;

PostgreSQL

Hint

CASE WHEN is_active THEN 'active' ELSE 'churned' END AS state — the boolean column needs no comparison at all.

Output

To do

A CASE does not have to produce text. Returning a number from one branch and zero from another is how you conditionally include a value in a total:

CASE WHEN status = 'paid' THEN amount ELSE 0 END

This is the single most useful CASE pattern in analytics. Wrapped in SUM() in Part 2 it becomes "revenue from paid orders only, alongside the total" — two different filters in one query, which a WHERE cannot do.

Your task: return order_id, amount, status, and a column named recognised holding the amount for paid orders and 0 for everything else.

query.sql

SELECT order_id, amount, status,
FROM orders;

PostgreSQL

Hint

CASE WHEN status = 'paid' THEN amount ELSE 0 END AS recognised — the THEN returns a column, not a literal.

Output

The segment map

To do

Marketing want every user tagged with one segment, and they have given you the rules in priority order. A user gets the first tag that applies:

churned    is_active is false, whatever their plan
enterprise on the team plan
growth     on the pro plan
starter    everyone else

The order is the specification. A churned user on the team plan is churned, not enterprise — which is exactly the kind of thing a CASE gets right for free and a pile of separate conditions gets wrong.

Your task: return name, plan, country and a fourth column named segment, sorted by user_id.

query.sql

-- Branch order matters: churned wins over any plan.
SELECT name, plan, country,
       CASE
       END AS segment
FROM users

PostgreSQL

Hint

Put WHEN NOT is_active THEN 'churned' first, then the two plan branches, then ELSE 'starter'. Finish with ORDER BY user_id.

Output

Sign in to open this module

CASE Expressions

CASE Is an Expression, Not a Statement

Quick check

Branch Order Is the Specification

The rule that prevents it

Quick check

No ELSE Means NULL

No ELSE

ELSE 'other'

Quick check

A Number From One Branch, Zero From the Other

Where this is going

Quick check

Put rows in buckets

A branch on NULL

Reading a flag

A number that depends on a condition

The segment map

Notification

`ELSE 'other'`