Price Tier Labels for Products — CASE WHEN in SQL

The problem

Brightlane's product team is preparing a pricing report and needs every item in the catalogue labelled by price tier.

Write a query to return each product's name, its price, and a price_tier label:

'premium' if the price is above $500.
'standard' for all other prices.

Assumptions:

The products table contains every product in Brightlane's catalogue.
A product priced exactly at $500 is 'standard' (the threshold is strictly greater-than).

Output:

One row per product, with columns name, price, and price_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
  name,
  price,
  CASE
    WHEN price > 500 THEN 'premium'
    ELSE 'standard'
  END AS price_tier
FROM
  products

The shape

CASE WHEN price > 500 THEN 'premium' ELSE 'standard' END sits in the SELECT list as a value expression — for each product row, it evaluates the condition against that row's price and returns one of two literal strings. The result is a third column on every row, alongside name and price.

Clause by clause

SELECT name, price reads the two existing columns straight off each product row. No transformation.
CASE WHEN price > 500 THEN 'premium' ELSE 'standard' END AS price_tier is the derived column. The WHEN is a boolean test against the current row's price. When it's true, THEN 'premium' is the returned value; when it's false, ELSE 'standard' is. The END closes the expression, and AS price_tier labels the resulting column.
FROM products is the source set: every product in the catalogue. The CASE runs once per row, producing one label per product.

The threshold is strictly greater-than, so a product priced exactly at $500 does not satisfy price > 500 and lands in the ELSE branch as 'standard'. The prompt's assumption that $500 is 'standard' is encoded directly in the choice of > over >=.

You practiced encoding a two-branch conditional as a CASE WHEN ... ELSE ... END expression. The recurring shape: any time a single column needs to be derived from a row-level condition, CASE is the value-producing equivalent of WHERE.

- `'premium'` if the price is above `$500`. - `'standard'` for all other prices

The shape

Clause by clause

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