Intermediate 12 min read SQL for Analytics

CASE WHEN and Categorical Logic

Dividing rows into buckets the business names

CASE WHEN is SQL's if-then-else: it segments, flags and buckets rows so analysis can split the world into categorical groups. Understanding evaluation order prevents the classic 'branch never hit' bug.

CASE WHENclassificationIFbucketingflagssegments

Prerequisites: SQL for Analysts

Before we start

What you will learn

Write CASE expressions with correct evaluation order
Bucket continuous values and create segment flags
Use CASE inside aggregates for conditional counts

Introduction

What is this?

CASE WHEN cond1 THEN a WHEN cond2 THEN b ELSE c END evaluates conditions in order, returns the first match — or ELSE (NULL if absent). It builds segments, flags and bucketed values in ONE column. Placing CASE inside aggregates (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) creates pivoted conditional counts in a single query.

Motivation

Why do you need it?

Business questions name categories: high-value vs low-value, new vs returning, mobile vs desktop.
Conditional counts in one SELECT produce clean pivot-style tables.
Bucketing continuous revenue into spend tiers powers RFM and segmentation.
Well-named buckets make dashboards readable to non-technical stakeholders.

Think about it

Mental model

Imagine

An airport directing passengers to gates

Think of

CASE WHEN

Check each condition in order at the podiums: 'Built?' → gate A; 'Pilot?' → gate B; else → gate C. First match wins; everyone ends up at exactly one gate.

Mechanics

How it works

Conditions evaluate top to bottom; the first true branch wins and the rest is skipped.
ELSE supplies the fallback; without it, unmatched rows become NULL.
CASE can emit values, labels or booleans for later use.
Inside aggregates, CASE acts as a filter: SUM(CASE WHEN x THEN 1 ELSE 0 END).
For long chains, prefer a lookup/reference table over nested CASE spaghetti.

Routing a row through CASE WHEN.

Rowamount = 450

WHEN amount>=1000High-value bucket

WHEN amount>=100Mid-value bucket

ELSELow-value bucket

Hands-on

Examples

Segments + conditional aggregate

SELECT
  country,
  SUM(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) AS big_orders,
  SUM(CASE WHEN amount >= 100 THEN amount END)   AS big_order_rev,
  CASE
    WHEN amount >= 1000 THEN 'high'
    WHEN amount >= 100  THEN 'mid'
    ELSE 'low'
  END AS segment
FROM orders
GROUP BY country, segment;

Case builds a segment column, and conditional ones in the aggregate count/pivot orders within each bucket.

Real world

In production

RFM scoring where recency/frequency/monetary each becomes a CASE-bucketed tier.
Channel attribution flags: direct, social, paid-organic.
A support dashboard marking tickets SLA-met / at-risk / breached with CASE over aging.

Guidelines

Best practices

Order conditions from most specific to most general.
Always include ELSE explicitly when NULL would confuse consumers.
Prefer reference tables (spend tier by threshold) over 15 nested WHENs.
Name the flag column what it means ('big_order'), not what it does (CASE).

Watch out

Common mistakes

Putting a broad catch-all first (amount >= 0) that eats every later branch.
Forgetting ELSE and getting NULL buckets that silently vanish in counts.
Aggregating over CASE when the CASE should be applied post-group for correct scope.
Comparing numeric strings in CASE (amount as text) so '100' > '99' is lexicographic.

When things break

Debugging tips

Am empty bucket? Check branch order — an earlier catch-all matches first.
NULL rows in segment? Add ELSE or review conditions for gaps.
Conditional SUM lower than expected? CASE-based count misses NULL-valued else branches.
Testing one row: trace the row through the conditions by hand.

Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2

What happens if no condition matches and no ELSE is present?

CASE WHEN evaluates conditions from top to bottom, first match wins.

Go deeper

Official documentation

PostgreSQL CASE expression

docs