Step-by-step lessons

Deciding, per Row

Four short lessons: the shape of a CASE, why the order of its branches is the specification, what happens when nothing matches, and the pattern that makes CASE indispensable in World 2.

1

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?

2

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 is_active = 0 THEN 'churned'. What happens to a churned team-plan user?

3

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 stage 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?

4

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 World 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

World 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 World 2 is already familiar.

Quick check

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

Write it yourself

Query Lab

Four queries that put rows into buckets. CASE is the if-statement of SQL, and the order of its branches is the whole game.

0 of 4 cleared

Real SQLite runs right here in your browser — nothing to install, nothing sent to a server. The database is rebuilt from scratch before every single run, so nothing you write can break it and nothing carries over between exercises.

The Northwind Analytics database A small SaaS product's database: who signed up, what they subscribed to, what they bought, and what they did in the app.

Loading the tables…

01

Put rows in buckets

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;

SQLite

Hint

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

Output

02

A branch on NULL

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;

SQLite

Hint

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

Output

03

Reading a flag

To do

is_active holds 1 or 0. Databases without a real boolean type store flags this way constantly, and a column of ones and zeroes is unreadable in a report.

There is a shorter form of CASE for testing one expression against several values:

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

It only does equality, so the moment you need a range or an IS NULL you are back to the long form. Both are correct here.

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

query.sql

SELECT name, plan,
FROM users;

SQLite

Hint

Either CASE is_active WHEN 1 THEN 'active' ELSE 'churned' END AS state, or the long form with WHEN is_active = 1.

Output

04

A number that depends on a condition

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 World 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;

SQLite

Hint

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

Output

Boss round

Assignment: The Segment Map

One graded query, and the last stage of World 1. Get the branch order right and the segments fall out correctly.

This one is graded. You can see 2 of the checks up front, and 2 stay hidden until you run. The hidden ones test the same job from angles you have not been shown, so code that genuinely solves the problem clears this and code shaped around the visible examples does not. Pass them all and the stage is yours.

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 0, 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

SQLite

Hint

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

Output

Lock it in

Guess the Term

Read the clues and name the concept. The fewer clues you need, the more points you score.

Round 1 Score 0

Keep it handy

CASE Quick Reference

The whole stage on one screen.

The shape

1

`CASE WHEN … THEN …`

Any condition a WHERE could take.

2

`ELSE …`

The catch-all. Always write it.

3

`END AS name`

Closes it, and names the column it produced.

Branch order

1

First match wins

Later branches never run.

2

Most specific first

A catch-all above a special case swallows it silently.

3

Ranges are implied

>= 25 after >= 100 already means under 100.

Two forms

1

Searched

CASE WHEN cond THEN …. Any condition.

2

Simple

CASE col WHEN val THEN …. Equality only.

3

Which to use

Simple for mapping codes to labels; searched for everything else.

The key pattern

Σ

Conditional aggregation

SUM(CASE WHEN c THEN x ELSE 0 END)

%

Rates

AVG(CASE WHEN c THEN 1.0 ELSE 0 END) is a proportion.

Why it matters

Several differently-filtered numbers in one row, which WHERE cannot do.

Sign in to train

Not unlocked yet

CASE Expressions

Deciding, per Row

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

Query Lab

Put rows in buckets

A branch on NULL

Reading a flag

A number that depends on a condition

Assignment: The Segment Map

The segment map

Guess the Term

CASE Quick Reference

The shape

CASE WHEN … THEN …

ELSE …

END AS name

Branch order

First match wins

Most specific first

Ranges are implied

Two forms

Searched

Simple

Which to use

The key pattern

Conditional aggregation

Rates

Why it matters

Notification

`ELSE 'other'`

`CASE WHEN … THEN …`

`ELSE …`

`END AS name`