CASE WHEN — Teaching SQL to Say "It Depends" 🔀

Scene: 6:45 a.m. on a Wall Street trading desk. Before the market opens, the risk manager needs one number on her screen: how many of yesterday's 200 trades were "large tickets"? Regulators (and her boss) treat a 5,000-share block very differently from a 150-share dabble. She doesn't want the raw notional of every trade — she wants each trade sorted into a bucket: LARGE, MEDIUM, or SMALL. That sorting-into-labels is exactly what `CASE WHEN` does, and it runs in almost every risk, P&L and compliance report on the Street.

🧠 The mental model: a bouncer with a clipboard

Picture a bouncer at a club checking IDs. For each person he runs down his list top to bottom and stops at the first rule that matches:

• ›Over 50k in the bank? → VIP room.
• ›Otherwise, over 20k? → main floor.
• ›Otherwise → the queue outside.

`CASE WHEN` is that bouncer. It reads your conditions in order, takes the `THEN` of the first one that is true, and ignores the rest. If nothing matches, the `ELSE` is the queue outside — the catch-all. Order matters: put the strictest rule first, or everyone ends up in the wrong room.

The syntax

```sql CASE WHEN condition_1 THEN label_1 WHEN condition_2 THEN label_2 ELSE default_label END AS column_alias ```

One `CASE` expression turns a raw number (a trade's notional) into a human-readable category (a text label). Because it produces a single value per row, you can `SELECT` it, `GROUP BY` it, even `ORDER BY` it — just like any real column.

Why order is everything

Watch what these two orderings do to a $60,000 trade:

Because 60,000 satisfies both `> 50000` and `> 20000`, the branch you write first wins. Always order your thresholds from strictest to loosest.

The finance move: bucket, then count

A single labelled trade is nice, but the risk manager wants the tally per bucket. That is the classic combo: build the category with `CASE WHEN`, then `GROUP BY` that category and `COUNT(*)`. You can even group by the alias you just named:

```sql GROUP BY size_category -- group by the CASE result itself ```

Career tip: "Bucketing" is desk vocabulary. Trades get bucketed by size, bonds by credit rating, clients by AUM tier, returns by decile. Master `CASE WHEN` and you can build any of these reports — it is the single most-used expression in a bank's data warehouse.

Your Task

You'll query the `trade_ledger` table (200 trades) and bucket each one by notional = quantity × price:

• ›LARGE: notional > 50,000
• ›MEDIUM: notional > 20,000
• ›SMALL: everything else

The starter already has the scaffolding — the `GROUP BY`, the `COUNT(*)`, the ordering. But its `CASE` has a single lazy branch that dumps every trade into one bucket labelled `UNCLASSIFIED`. Your job is to write the real three-branch `CASE WHEN` so the desk sees LARGE, MEDIUM and SMALL tiers.

Predict before you run: the starter, as written, collapses all 200 trades into a single row. Once you write the three branches correctly, how many rows will come back — and which bucket do you expect to be the biggest? (Hint: trade notionals here run from roughly 10,000 to 500,000.) Run it and check your instinct against the desk's real distribution.