Count Products Below Max Price — Scalar Subqueries in SQL

The problem

Brightlane's catalogue team wants to know how many products are priced below the highest price in the catalogue.

Write a query to return the count as a single number named cheaper_than_max.

Assumptions:

The products table contains every product in the catalogue.
The threshold (the maximum price) is computed from the same table being counted.
Products priced exactly at the maximum are excluded from the count.

Output:

A single row with one column, cheaper_than_max, containing the count.

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
  COUNT(*) AS cheaper_than_max
FROM
  products
WHERE
  price < (
    SELECT
      MAX(price)
    FROM
      products
  )

The shape

Three pieces compose cleanly. The subquery resolves the boundary (the catalogue's MAX(price)), WHERE filters out everything at or above it, and COUNT(*) collapses the remaining rows to a single number — 62 products priced strictly below the maximum.

Clause by clause

FROM products is the source set: every row in the catalogue.
WHERE price < (SELECT MAX(price) FROM products) runs first. The subquery computes one number — the highest price in the table — and the outer comparison reads price < that_number for every row. Products at the maximum drop out. The strict < is what the prompt requires.
SELECT COUNT(*) AS cheaper_than_max then counts the rows the filter let through. COUNT(*) collapses the filtered set to a single number; the result row is { cheaper_than_max: 62 }.

Why this and not subtracting from the row count

A learner might reach for (SELECT COUNT(*) FROM products) - (SELECT COUNT(*) FROM products WHERE price = (SELECT MAX(price) FROM products)) — total rows minus rows at the maximum. The arithmetic is correct, but it reads as a workaround. The direct shape says exactly what's being asked: count the rows below the max. The subquery resolves the boundary as a value, the comparison applies that boundary, and the aggregate counts the survivors.

The trap

Replacing < with <= changes the answer. The prompt asks for products priced below the maximum, which excludes the rows at the maximum itself. With <= the count climbs to include the maximum-priced row (or rows, when tied). Always check whether the boundary value belongs in or out of the filtered set — a one-character change flips the count.

You practiced wrapping a scalar-subquery threshold inside an aggregate (COUNT(*) ... WHERE ... < (SELECT MAX...)). Each part does one thing — the subquery resolves the boundary, WHERE filters, COUNT(*) aggregates — and they compose without ceremony.

Return the count as a single number named `cheaper_than_max`

The shape

Clause by clause

Why this and not subtracting from the row count

The trap

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