Value Tier for Delivered Orders — CASE WHEN in SQL

The problem

Brightlane's finance team is reviewing the value distribution within the fulfilled-order pipeline.

Write a query to return each delivered order's ID and a value_tier label:

'high value' for orders above $1,000.
'standard' for all other delivered orders.

Assumptions:

The orders table contains every order Brightlane has processed.
Only delivered orders (status = 'delivered') should appear in the result.
An order priced exactly at $1,000 is 'standard' (threshold is strictly greater-than).

Output:

One row per delivered order, with columns id and value_tier.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  CASE
    WHEN total_amount > 1000 THEN 'high value'
    ELSE 'standard'
  END AS value_tier
FROM
  orders
WHERE
  status = 'delivered'

The shape

WHERE status = 'delivered' narrows the row set to fulfilled orders, and CASE then labels each surviving row by value. The two clauses do separate jobs: the filter decides who's in, the CASE decides what label they get.

Clause by clause

SELECT id carries the order ID through so each labelled row is identifiable.
CASE WHEN total_amount > 1000 THEN 'high value' ELSE 'standard' END AS value_tier is the derived column. The WHEN tests each (surviving) order's total_amount against the $1,000 threshold. The threshold is strictly greater-than, so an order at exactly $1,000 lands in the ELSE branch and gets 'standard' — matching the prompt's assumption directly.
FROM orders is the source set: every order, before any filtering.
WHERE status = 'delivered' runs before the SELECT. Cancelled, pending, shipped, and refunded orders are gone before the CASE ever sees them. The labels in the result describe only delivered orders.

Why this and not put the status check inside the CASE

A CASE branch could carry the status test inside it — something like WHEN status = 'delivered' AND total_amount > 1000 THEN 'high value'. That structure changes the result: non-delivered orders would still appear in the output, just with 'standard' (or whatever the ELSE branch returns). The finance team asked for delivered orders only, so the right tool is WHERE. It removes rows; CASE only relabels them.

The division of labour is the rule: WHERE decides which rows belong in the result, CASE decides what each one's derived columns look like. Confusing the two leads to result sets with the wrong rows or the wrong labels on the right rows.

You practiced combining WHERE (to scope the row set) with CASE (to derive a per-row label). The two compose cleanly: WHERE removes the rows you don't want; CASE produces a label for the rows you keep.

- `'high value'` for orders above `$1,000`. - `'standard'` for all other delivered orders

The shape

Clause by clause

Why this and not put the status check inside the CASE

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.