Products Priced Above Average — Scalar Subqueries in SQL

The problem

Brightlane's premium-product team needs to know which products are priced above the catalogue average.

Write a query to return the name and price of every product whose price exceeds the overall average.

Assumptions:

The products table contains every product in the catalogue.
The threshold (the average price) is itself a query result, not a literal — it must be computed from the same table being narrowed.
A product priced exactly at the average does not qualify (strictly greater than).

Output:

One row per qualifying product, with columns name and 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
FROM
  products
WHERE
  price > (
    SELECT
      AVG(price)
    FROM
      products
  )

The shape

The filter threshold is itself a query result. (SELECT AVG(price) FROM products) resolves to 326.58..., and the outer WHERE compares each product's price against that single value. The result is every product priced strictly above the catalogue average.

Clause by clause

FROM products is the source: every row in the catalogue.
WHERE price > (SELECT AVG(price) FROM products) does the filtering. The subquery runs first and returns one number. The outer comparison then reads, for each row, price > 326.58.... Products at or below the average drop out; products above it pass.
SELECT name, price returns the two columns the premium-product team asked for. The subquery itself doesn't appear in the output — it's used as a threshold, not displayed.

Why this and not a literal threshold

Writing WHERE price > 326.58 works against today's catalogue and breaks the moment a price changes. The subquery recomputes the average every run, so "above average" stays correct as the catalogue evolves. The threshold and the rows being compared come from the same table, in the same snapshot, with no risk of drift between the two.

You practiced a scalar subquery in a WHERE comparison. The recurring shape: any time the filter threshold is itself a query result (an average, a max, a count), the subquery produces the threshold value once and the outer query uses it like a literal.

Return the name and price of every product whose price exceeds the overall average

The shape

Clause by clause

Why this and not a literal threshold

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