Products Between 100 and 500 with Member Price — BETWEEN, IN, and LIKE in SQL

The problem

Brightlane's promotions team is running a member-only sale that applies a 20% discount to all products priced between $100 and $500, inclusive.

Write a query to return each qualifying product's name, its regular price, and its discounted member price (the regular price scaled by 0.8).

Assumptions:

The products table contains every product in Brightlane's catalogue.
A product priced exactly at $100 or exactly at $500 qualifies for the sale.

Output:

One row per qualifying product, with columns name, price, and member_price.

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,
  price * 0.8 AS member_price
FROM
  products
WHERE
  price BETWEEN 100 AND 500

The shape

BETWEEN 100 AND 500 selects the eligible products, and a derived price * 0.8 column in the SELECT list produces the member price alongside the regular price in a single row per product.

Clause by clause

SELECT name, price, price * 0.8 AS member_price returns three columns. The first two come straight off the row — the product label and its regular price. The third is computed: each row's price multiplied by 0.8, labelled member_price. SQL evaluates the multiplication once per row, against that row's price, so Coffee Table at 249 produces 199.2 and Patio Set at 499 produces 399.2.
FROM products reads the catalogue.
WHERE price BETWEEN 100 AND 500 keeps only the rows whose price falls in the inclusive range. A product priced at exactly 100 qualifies — Gift Card $100 is in the result for that reason — and the upper bound at 500 is included the same way.

Why this and not filtering on `member_price`

The filter has to be expressed in terms of price, not member_price. SQL evaluates WHERE before SELECT — at the time WHERE runs, the member_price alias doesn't exist yet because the SELECT list hasn't been computed. Writing WHERE member_price BETWEEN 100 AND 500 would raise an error.

It's also the wrong question. The prompt says the sale applies to products priced between $100 and $500 — the regular price is the qualifying number. The 20% discount changes what the customer pays, not what makes the product eligible. So the filter goes on the stored value, the derived column shows the discounted price, and the row carries both for the team to see at once.

You practiced combining a BETWEEN filter with a derived column in the SELECT list. The recurring shape: filtering on a stored value while presenting both the stored value and a value computed from it side by side.

Return each qualifying product's name, its regular price, and its discounted member price (the regular price scaled by 0.8)

The shape

Clause by clause

Why this and not filtering on `member_price`

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

The shape

Clause by clause

Why this and not filtering on member_price

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

Why this and not filtering on `member_price`