Label Orders by Urgency — CASE WHEN in SQL

The problem

Brightlane's shipping team needs each order flagged by urgency. The mapping is:

pending → 'action needed'
shipped → 'in transit'
delivered → 'complete'
Any other status → 'unknown'

Write a query to return each order's ID, status, and urgency label.

Assumptions:

The orders table contains every order Brightlane has processed.
The four bullets above are exhaustive — every order maps to one of the four labels.

Output:

One row per order, with columns id, status, and urgency.

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,
  status,
  CASE
    WHEN status = 'pending' THEN 'action needed'
    WHEN status = 'shipped' THEN 'in transit'
    WHEN status = 'delivered' THEN 'complete'
    ELSE 'unknown'
  END AS urgency
FROM
  orders

The shape

Three WHEN branches handle the three known statuses, and ELSE 'unknown' catches anything outside that set. The mapping in the prompt translates directly into the CASE, one branch per status.

Clause by clause

SELECT id, status returns the order ID and its raw status, so the input (the raw status) sits next to the output (the urgency label).
WHEN status = 'pending' THEN 'action needed' is the first branch. PostgreSQL tests the condition against the current row's status; if it matches the literal 'pending', the expression returns 'action needed' and the rest of the branches are skipped.
WHEN status = 'shipped' THEN 'in transit' and WHEN status = 'delivered' THEN 'complete' are the same shape for the other two known statuses. PostgreSQL walks the branches top to bottom and stops at the first match.
ELSE 'unknown' is the catch-all. Any order whose status doesn't match one of the three literals — cancelled, refunded, anything outside the enumerated set — lands here.
END AS urgency closes the expression and labels the derived column.
FROM orders is the source set.

Because the three known conditions are mutually exclusive (a status equals exactly one string), branch order doesn't affect the answer here. That changes the moment branches can overlap, which is what range-based CASE expressions force you to think about.

You practiced chaining multiple WHEN branches to encode a many-to-one mapping. PostgreSQL evaluates the branches top-to-bottom and returns the first match — ELSE catches everything that didn't match any earlier branch.

Return each order's ID, status, and urgency label

The shape

Clause by clause

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