Max Unit Price per Order — Correlated Subqueries in SQL

The problem

Brightlane's inventory team is analyzing the highest-priced item within each order to understand premium purchase behavior.

Write a query to return every order's ID, customer ID, total amount, and the highest unit price among the items in that order.

Assumptions:

For each order, the highest unit price is the largest unit_price across line items linked to that order_id.
Every order must appear. An order with no line items should show a missing value in the highest-unit-price column.

Output:

One row per order, with columns order_id, customer_id, total_amount, and max_unit_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
  o.id AS order_id,
  o.customer_id,
  o.total_amount,
  (
    SELECT
      MAX(oi.unit_price)
    FROM
      order_items oi
    WHERE
      oi.order_id = o.id
  ) AS max_unit_price
FROM
  orders o

The shape

Each order needs a fourth column showing the highest unit price among that order's own line items, so \MAX(unit_price)\ goes inline in the \SELECT\ list as a correlated subquery filtered to the outer order's \id\. The first three columns are read straight from the order row. The fourth is computed once per order against \order_items\. An order with no line items gets NULL from \MAX\ over an empty set, which matches the spec's missing-value rule.

Clause by clause

\SELECT o.id AS order_id, o.customer_id, o.total_amount, (SELECT MAX(oi.unit_price) FROM order_items oi WHERE oi.order_id = o.id) AS max_unit_price\ returns the three order columns and the per-order maximum unit price. The alias \AS order_id\ renames \o.id\ to the column name the spec asks for. The inner subquery's \WHERE oi.order_id = o.id\ references the outer order's \id\, which is what creates the correlation. An aggregate over zero rows returns NULL, so an order with no line items shows a missing value in \max_unit_price\ rather than dropping out of the result.
\FROM orders o\ reads every order. No outer \WHERE\ because the spec requires every order in the output.

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`

A \LEFT JOIN order_items oi ON oi.order_id = o.id\ followed by \GROUP BY o.id, o.customer_id, o.total_amount\ and \MAX(oi.unit_price)\ returns the same result. The correlated form avoids the \GROUP BY\ on three order columns and reads as "for each order, attach this scalar," which is what the report wants. When the related-table value is one scalar per outer row and the outer columns are already at the right grain, the inline subquery is the cleaner shape.

You practiced a correlated MAX(...) against a child table — the per-parent maximum attaches to every parent record through a single inner expression.

Return every order's ID, customer ID, total amount, and the highest unit price among the items in that order

The shape

Clause by clause

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`

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 \LEFT JOIN\ plus \GROUP BY\

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

Why this and not \`LEFT JOIN\` plus \`GROUP BY\`