Stock Status Labels for Products — CASE WHEN in SQL

The problem

Brightlane's warehouse team needs each product classified by stock level. The classification is:

'out of stock' if stock_qty is exactly 0.
'low' if stock_qty is greater than 0 but less than 50.
'adequate' if stock_qty is between 50 and 199.
'well stocked' if stock_qty is 200 or higher.

Write a query to return each product's name, stock quantity, and stock_status label.

Assumptions:

The products table contains every product in Brightlane's catalogue.
The four bullets are exhaustive and non-overlapping when evaluated in order.

Output:

One row per product, with columns name, stock_qty, and stock_status.

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
  name,
  stock_qty,
  CASE
    WHEN stock_qty = 0 THEN 'out of stock'
    WHEN stock_qty < 50 THEN 'low'
    WHEN stock_qty < 200 THEN 'adequate'
    ELSE 'well stocked'
  END AS stock_status
FROM
  products

The shape

Four branches encode the four stock bands, ordered from lowest threshold to highest. Each WHEN only needs an upper bound because the earlier branches have already caught everything below. PostgreSQL returns on the first match, so the order of branches is what makes the open-ended conditions safe.

Clause by clause

SELECT name, stock_qty carries the product's name and its raw stock count through to the output, so the label sits next to the number it was derived from.
WHEN stock_qty = 0 THEN 'out of stock' is the most restrictive branch. It catches the exact-zero case first because no later branch would distinguish it from low stock.
WHEN stock_qty < 50 THEN 'low' runs only on rows that didn't match the first branch — so stock_qty is already known to be non-zero. The condition is therefore equivalent to "between 1 and 49," even though only the upper bound is written.
WHEN stock_qty < 200 THEN 'adequate' is the same pattern one level up. Rows reaching this branch have stock_qty >= 50 (otherwise the previous branch would have caught them), so this matches the 50 to 199 band.
ELSE 'well stocked' catches everything at 200 or higher.
END AS stock_status closes the expression and labels the derived column.
FROM products is the source set.

Why this and not explicit range bounds

The branches could be written as WHEN stock_qty BETWEEN 1 AND 49 THEN 'low' and so on — explicit upper and lower bounds on each band. That works, but it's verbose, and changing a threshold means editing two branches instead of one. Letting the earlier branches handle the lower bound implicitly keeps each WHEN to a single comparison. The cost is that the branches now depend on their order: shuffle them and the result changes.

You practiced encoding ordered range bands as CASE branches. The recurring shape: tiered classifications (stock levels, score grades, age brackets) translate directly to top-to-bottom WHEN chains where each branch tightens the range.

Return each product's name, stock quantity, and `stock_status` label

The shape

Clause by clause

Why this and not explicit range bounds

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